Sorting (Part 2)

The SQL below shows how many sorts have been done in memory and how many have had to go out to disk since instance startup:

SQL> col name format a20

SQL> l

  1  select name, value

  2  from v$sysstat

  3* where name in ('sorts (memory)', 'sorts (disk)')

SQL> /

NAME                      VALUE

-------------------- ----------

sorts (memory)          1758574

sorts (disk)                  0

SQL>

You Cannot Analyze a Table Through a Synonym

This was tested on an Oracle 11 database. First I created a table and analyzed it: 

SQL> create table andrew1 (one_col number)

  2  /

Table created.

SQL> analyze table andrew1 compute statistics

  2  /

Table analyzed.

SQL>

Then I created a synonym for the table:

SQL> create synonym andrew2 for andrew1

  2  /

Synonym created.

SQL>

I found that I was able to describe the table via the synonym:

SQL> desc andrew2

 Name                    Null?    Type

 ----------------------- -------- ----------------

 ONE_COL                          NUMBER

SQL>

... but I could not analyze it:

SQL> analyze table andrew2 compute statistics

  2  /

analyze table andrew2 compute statistics

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

Restricted Session

(Tested on an Oracle 9 database.)

You can use the RESTRICTED SESSION facility to control logins to the database. It can be used to prevent normal users from logging into a database while a DBA is carrying out maintenance. There are two ways to do this:

You can do it when you open the database. In the example below, an instance is started twice, once in restricted mode. You can tell if the instance is in restricted mode by querying V$INSTANCE:

SQL> startup open

ORACLE instance started.

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL> select logins from v$instance;

LOGINS

----------

ALLOWED

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup restrict

ORACLE instance started.

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

SQL>

Or you can do it while the database is running:

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

----------

ALLOWED

SQL>

Enabling restricted session stops new logins except by users with the RESTRICTED SESSION privilege. Existing connections are not terminated (although you can kill them yourself if necessary). In the next example, RESTRICTED SESSION is enabled. Andrew keeps the session he had beforehand but cannot start a new one:

SQL> alter system enable restricted session;

System altered.

SQL> select username from v$session

  2  where username is not null;

USERNAME

------------------------------

SYS

ANDREW

SQL>conn andrew/reid

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Warning: You are no longer connected to ORACLE.

SQL>

The DBA role has the RESTRICTED SESSION privilege:

SQL> select grantee from dba_sys_privs

  2  where privilege = 'RESTRICTED SESSION';

GRANTEE

------------------------------

DBA

SQL>

I’m not sure why you would want to do this but you can grant the privilege directly to a user. This allows him to login even when the database is in restricted mode:

SQL> conn andrew/reid

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba

Connected.

SQL> grant restricted session to andrew;

Grant succeeded.

SQL> conn andrew/reid

Connected.

SQL>

RESTRICTED SESSION is an instance level setting so it disappears when you bounce the database (unless you reopen it with the RESTRICT clause as shown above):

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

----------

RESTRICTED

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area   68423632 bytes

Fixed Size                   733136 bytes

Variable Size              46137344 bytes

Database Buffers           20971520 bytes

Redo Buffers                 581632 bytes

Database mounted.

Database opened.

SQL> select logins from v$instance;

LOGINS

----------

ALLOWED

SQL>

Truncate (Part 1)

This post shows the effect of the TRUNCATE statement. First create a table:

SQL> create table truncate_example
  2  as select * from dba_tables
  3  /

Table created.

SQL>

Count the lines in the table:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
      3023

SQL>

And the number of extents:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Truncate the table with the REUSE STORAGE option:

SQL> truncate table truncate_example reuse storage
  2  /

Table truncated.

SQL>

Now count the number of rows in the table. There will be none:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Truncate does not produce any undo information so if you do a rollback, the deleted lines will not reappear:

SQL> rollback;

Rollback complete.

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Now count the number of extents. It will be the same as before because you included the REUSE STORAGE clause:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Now reinsert the deleted rows from the source table:

SQL> insert into truncate_example
  2  select * from dba_tables
  3  /

3024 rows created.

SQL>

Truncate the table again but this time, add the DROP STORAGE clause, which is the default:

SQL> truncate table truncate_example
  2  drop storage
  3  /

Table truncated.

SQL>

Count the number of rows in the table again. There will be none, as before:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

And count the number of extents. By adding the DROP STORAGE clause, the table’s extents have been deallocated. The number of extents remaining is determined by the table’s MINEXTENTS value:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
         1

SQL>

Compressed Tables (Part 2)

This is an example to show the effect of block sizes on Oracle compression. The SQL was run on an Oracle 9 database with a 4k block size:

SQL> col value format a10
SQL> select value from v$parameter
  2  where name = 'db_block_size'
  3  /

VALUE
----------
4096

SQL>

Before starting, I allocated space in the buffer cache for all the non-standard block sizes I planned to use:

SQL> col name format a20
SQL> select name, value from v$parameter
  2  where name like 'db_%k_cache_size'
  3  /

NAME                      VALUE
-------------------- ----------
db_2k_cache_size        8388608
db_4k_cache_size              0
db_8k_cache_size        8388608
db_16k_cache_size       8388608
db_32k_cache_size       8388608

SQL>

Then I created tablespaces with various block sizes:

SQL> create tablespace two_k
  2  datafile size 20m blocksize 2k
  3  /

Tablespace created.

SQL> create tablespace four_k
  2  datafile size 20m
  3  /

Tablespace created.

SQL> create tablespace eight_k
  2  datafile size 20m blocksize 8k
  3  /

Tablespace created.

SQL> create tablespace sixteen_k
  2  datafile size 20m blocksize 16k
  3  /

Tablespace created.

SQL> create tablespace thirty_two_k
  2  datafile size 20m blocksize 32k
  3  /

Tablespace created.

SQL>

Then I checked that the tablespaces had been created with the block sizes specified. I did this because the example did not work as expected at first:

SQL> col tablespace_name format a15
SQL> select tablespace_name, block_size
  2  from dba_tablespaces
  3  where tablespace_name like '%_K'
  4  order by block_size
  5  /

TABLESPACE_NAME BLOCK_SIZE
--------------- ----------
TWO_K                 2048
FOUR_K                4096
EIGHT_K               8192
SIXTEEN_K            16384
THIRTY_TWO_K         32768

SQL>

Then I created a table with some repeated data:

SQL> create table repeated_data
  2  as select * from dba_tables
  3  /

Table created.

SQL> begin
  2  for a in 1..5 loop
  3  insert into repeated_data
  4  select * from repeated_data;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

I copied this table into each tablespace. At first I tried it without the order by statement. But when I compressed the tables, the space saved was the same, irrespective of block size. That’s because the compression is done at block level. If Oracle sees data repeated within a block, it sets up a symbol for it in a symbol table in the same block. Then it replaces every occurrence of that repeated data in the block by the appropriate symbol. I guess the effect of the order by statement is to place more repeating data within the same block. This gives the compression algorithm something to work on. The larger block sizes then result in better compression because they need to store fewer copies of the symbol tables:

SQL> create table repeated_data_2k
  2  tablespace two_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

SQL> create table repeated_data_4k
  2  tablespace four_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

SQL> create table repeated_data_8k
  2  tablespace eight_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

SQL> create table repeated_data_16k
  2  tablespace sixteen_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

SQL> create table repeated_data_32k
  2  tablespace thirty_two_k
  3  as select * from repeated_data
  4  order by owner
  5  /

Table created.

Next, I checked the starting sizes of the tables and compressed them:

SQL> col segment_name format a20
SQL> select segment_name, bytes
  2  from dba_segments
  3  where segment_name like 'REPEATED_DATA_%K'
  4  /

SEGMENT_NAME              BYTES
-------------------- ----------
REPEATED_DATA_8K        6291456
REPEATED_DATA_16K       6291456
REPEATED_DATA_32K       6291456
REPEATED_DATA_2K        7340032
REPEATED_DATA_4K        7340032

SQL> alter table repeated_data_2k
  2  move compress
  3  /

Table altered.

SQL> alter table repeated_data_4k
  2  move compress
  3  /

Table altered.

SQL> alter table repeated_data_8k
  2  move compress
  3  /

Table altered.

SQL> alter table repeated_data_16k
  2  move compress
  3  /

Table altered.

SQL> alter table repeated_data_32k
  2  move compress
  3  /

Table altered.

SQL>

Finally, I checked the table sizes after compression. The tables in the tablespaces with larger block sizes were compressed much more:

SQL> select segment_name, bytes
  2  from dba_segments
  3  where segment_name like 'REPEATED_DATA_%K'
  4  /

SEGMENT_NAME              BYTES
-------------------- ----------
REPEATED_DATA_8K         720896
REPEATED_DATA_16K        524288
REPEATED_DATA_32K        458752
REPEATED_DATA_2K        2097152
REPEATED_DATA_4K         917504

SQL>

Failed Login Attempts

(Tested on an Oracle 9.2.0.7.0 database.)

This can be used to limit the number of times a user can enter an incorrect password. First create a test user and show what profile he is using:

  1  grant create session to andrew

  2* identified by reid

SQL> /

Grant succeeded.

SQL> select profile from dba_users

  2  where username = 'ANDREW';

PROFILE

------------------------------

DEFAULT

SQL>

Then change that profile so that only two incorrect password attempts are allowed:

SQL> alter profile default

  2  limit failed_login_attempts 2;

Profile altered.

SQL>

To demonstrate the limit, the user must then try to login twice with the wrong password:

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

SQL>

If he tries to login again he will see that the account is locked:

SQL> conn andrew/wrong_password

ERROR:

ORA-28000: the account is locked

SQL>

Note that this limit still applies even if resource_limit is set to false:

SQL> col value format a20

SQL> l

  1  select value from v$parameter

  2* where name = 'resource_limit'

SQL> /

VALUE

--------------------

FALSE

SQL>

Also note that the failed login attempts need to be consecutive. A successful login attempt sets the count of failed login attempts back to zero:

SQL> conn / as sysdba

Connected.

SQL> alter user andrew account unlock;

User altered.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL> conn andrew/wrong_password

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn andrew/reid

Connected.

SQL>