TRUNCATE TABLE ... DROP ALL STORAGE

This post looks at changes to the TRUNCATE statement between Oracle 11.2.0.1 and 11.2.0.2. I tested the first part on Oracle 11.2.0.1.0. I created a table and counted its extents: 

SQL> create table andrew

  2  storage (initial 8k next 8k)

  3  as select * from dba_tables

  4  /

 

Table created.

 

SQL> select count(*) from user_extents

  2  where segment_name = 'ANDREW'

  3  /

 

  COUNT(*)

----------

        15

 

SQL>

Then I truncated it and showed that it was left with only 1 extent. I think I have demonstrated this already in another post: 

SQL> truncate table andrew drop storage

  2  /

 

Table truncated.

 

SQL> select count(*) from user_extents

  2  where segment_name = 'ANDREW'

  3  /

 

  COUNT(*)

----------

         1

 

SQL>

Then I checked that the DROP ALL STORAGE option was not available: 

SQL> truncate table andrew drop all storage

  2  /

truncate table andrew drop all storage

                           *

ERROR at line 1:

ORA-03291: Invalid truncate option - missing STORAGE

keyword

 

SQL>

I tested the next part on Oracle 11.2.0.2.7. First I created a table as before. This time the number of extents was not so important: 

SQL> create table fred

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL>

Then I tried out the new TRUNCATE TABLE ... DROP ALL STORAGE command, which apparently first appeared in Oracle 11.2.0.2: 

SQL> truncate table fred drop all storage

  2  /

 

Table truncated.

 

SQL>

This removed ALL the table’s extents, left an entry for it in USER_TABLES but removed its entry from USER_SEGMENTS: 

SQL> select count(*) from user_extents

  2  where segment_name = 'FRED'

  3  /

 

  COUNT(*)

----------

         0

 

SQL> select count(*) from user_tables

  2  where table_name = 'FRED'

  3  /

 

  COUNT(*)

----------

         1

 

SQL> select count(*) from user_segments

  2  where segment_name = 'FRED'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>

ORA-02275

This was tested on Oracle 11.2.0.2.7. While investigating something else today, I came across a new error message. It seems that you cannot have more than one referential integrity check between the same two columns. That's one less thing I need to worry about I suppose:

SQL> alter table t2

  2  add constraint check_number1

  3  foreign key (col1)

  4  references t1(col1)

  5  on delete cascade

  6  /

 

Table altered.

 

SQL> alter table t2

  2  add constraint check_number2

  3  foreign key (col1)

  4  references t1(col1)

  5  on delete cascade

  6  /

foreign key (col1)

*

ERROR at line 3:

ORA-02275: such a referential constraint already

exists in the table

 

SQL>

Another Stolen Idea

I read this in a comment by Uwe Hesse on Tanel Poder's blog and tested it on Oracle 11.2.0.2.7. With deferred segment creation, you can create a table in a read only tablespace. You do not get an error until you try to insert data into the table:

 

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant

  2  create session,

  3  create table,

  4  select any dictionary to andrew

  5  /

 

Grant succeeded.

 

SQL> alter tablespace users read only

  2  /

 

Tablespace altered.

 

SQL> conn andrew/reid

Connected.

SQL> create table t1

  2  segment creation deferred

  3  as select * from dba_tables

  4  where 1 = 2

  5  /

 

Table created.

 

SQL> insert into t1

  2  select * from dba_tables

  3  /

insert into t1

            *

ERROR at line 1:

ORA-01647: tablespace 'USERS' is read-only, cannot

allocate space in it

 

SQL>

Idea stolen from Uwe Hesse

I saw this in a comment by Uwe Hesse on Tanel Poder’s blog and tested it on Oracle 11.2.0.2.7. With deferred segment creation, if a user with no quota creates a table, no error will be returned. If the user later tries to insert rows into that table, the statement will fail with an ORA-01536 error. You can see what I mean in the example below, which shows the old behaviour first followed by the new behaviour: 

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota 0 on users

  5  /

 

User created.

 

SQL> grant

  2  alter session,

  3  create session,

  4  create table,

  5  select any dictionary to andrew

  6  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> alter session set deferred_segment_creation = false

  2  /

 

Session altered.

 

SQL> create table t1 as select * from dba_tables where 1=2

  2  /

create table t1 as select * from dba_tables where 1=2

                                 *

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USERS'

 

SQL> alter session set deferred_segment_creation = true

  2  /

 

Session altered.

 

SQL> create table t1 as select * from dba_tables where 1=2

  2  /

 

Table created.

 

SQL> insert into t1 select * from dba_tables

  2  /

insert into t1 select * from dba_tables

            *

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USERS'

 

SQL>

ORA-14223

This was tested on Oracle 11.2.0.2.7. You can create a table with deferred segment creation as follows: 

SQL> conn andrew/reid

Connected.

SQL> l

  1  create table blah (col1 number)

  2* segment creation deferred

SQL> /

 

Table created.

 

SQL>

... but some users, e.g. SYS, are not allowed to do this: 

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> l

  1  create table blah (col1 number)

  2* segment creation deferred

SQL> /

create table blah (col1 number)

*

ERROR at line 1:

ORA-14223: Deferred segment creation is not supported

for this table

 

SQL>

In Spanish / en español

ORA-01097

I did this on Oracle 11.2.0.2.7. While testing something completely different, I noticed that you cannot close a database from a session which has an uncommitted transaction:

 

SQL> show user

USER is "SYS"

SQL> delete aud$;

 

38 rows deleted.

 

SQL> shutdown

ORA-01097: cannot shutdown while in a transaction - commit or rollback first

SQL> commit

  2  /

 

Commit complete.

 

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

How to Open a Database in READ ONLY Mode

This was tested on Oracle 11.2.0.2.7. You can open a database in READ ONLY mode as follows: 

SQL> startup open read only;

ORACLE instance started.

 

Total System Global Area  522092544 bytes

Fixed Size                  2159904 bytes

Variable Size             314575584 bytes

Database Buffers          197132288 bytes

Redo Buffers                8224768 bytes

Database mounted.

Database opened.

SQL>

 

You can check how a database was opened like this:

 

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ ONLY

 

SQL>

 

When a database is open in READ ONLY mode, you cannot create tables in it:

 

SQL> create table andrews_table (col1 number);

create table andrews_table (col1 number)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

 

SQL>

 

You cannot update tables either:

 

SQL> insert into another_table

  2  select * from another_table;

insert into another_table

            *

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL> update another_table

  2  set col1 = 2;

update another_table

       *

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL> delete another_table;

delete another_table

       *

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL>

 

You cannot even drop a table. I wanted to try this out because, strangely enough, you CAN drop tables from READ ONLY tablespaces:

 

SQL> drop table another_table;

drop table another_table

*

ERROR at line 1:

ORA-16000: database open for read-only access

 

SQL>

 

Bouncing the database returns it to READ WRITE mode:

 

SQL> startup force

ORACLE instance started.

 

Total System Global Area  522092544 bytes

Fixed Size                  2159904 bytes

Variable Size             314575584 bytes

Database Buffers          197132288 bytes

Redo Buffers                8224768 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;

 

OPEN_MODE

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

READ WRITE

 

SQL>

sar -r

I read this on page 970 of the book advertised above. You can see the amount of free memory and swap space on a server as follows: 

Solaris > sar -r 5 4

 

SunOS yfd-ljsqdc-cca1 5.10 Generic_142900-15 sun4u    10/15/2012

 

17:33:09 freemem freeswap

17:33:14 2282983 145529158

17:33:19 2282920 145519952

17:33:24 2284281 145554707

17:33:29 2285210 145576483

 

Average  2283848 145545075

Solaris > 

So, if the server has 21 open databases: 

Solaris > ps -ef|grep pmon|wc -l

      21

Solaris >

... and you open another one: 

Solaris > sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 15 17:34:33 2012

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup

ORACLE instance started.

 

Total System Global Area  522092544 bytes

Fixed Size                  2159904 bytes

Variable Size             314575584 bytes

Database Buffers          197132288 bytes

Redo Buffers                8224768 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 

Solaris > 

... you will have 22 open databases: 

Solaris > ps -ef|grep pmon|wc -l

      22

Solaris > 

Then, if you run the sar command again, the available memory and swap space will be lower: 

Solaris > sar -r 5 4

 

SunOS yfd-ljsqdc-cca1 5.10 Generic_142900-15 sun4u    10/15/2012

 

17:35:21 freemem freeswap

17:35:26 2182124 143973616

17:35:31 2181901 143735869

17:35:36 2185627 144082067

17:35:41 2188363 144147203

 

Average  2184391 143984162

Solaris >

Reply to: Reply to Andrew Reid

Send

ORA-02382

This was tested on Oracle 11.2. I wanted to see if you could drop a profile if somebody was using it. First I created a profile.

SQL> create profile andrews_profile

  2  limit failed_login_attempts 3

  3  /

Profile created. 

SQL> 

Then I assigned the profile to a user:

SQL> create user andrew identified by reid

  2  profile andrews_profile

  3  /

User created.

SQL> select profile from dba_users

  2  where username = 'ANDREW'

  3  /

PROFILE

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

ANDREWS_PROFILE 

SQL> 

I tried to drop the profile but this failed as it was in use:

SQL> drop profile andrews_profile

  2  /
drop profile andrews_profile

*

ERROR at line 1:

ORA-02382: profile ANDREWS_PROFILE has users assigned,

cannot drop without CASCADE

SQL>

I ran the command again, adding CASCADE at the end:

SQL> drop profile andrews_profile cascade

  2  /

Profile dropped.

SQL>

This dropped the profile and assigned the DEFAULT profile to ANDREW instead:

SQL> select profile from dba_users

  2  where username = 'ANDREW'

  3  /

PROFILE

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

DEFAULT

SQL>

In French / en français

Reply to: Reply to Andrew Reid

Send

ORA-02000 and ORA-02379

This was tested on Oracle 10 on Linux. You need to include a LIMIT clause when you create a profile. If you don't, you get an ORA-02000: 
 
SQL> create profile andrews_profile
  2  /
create profile andrews_profile
                             *
ERROR at line 1:
ORA-02000: missing LIMIT keyword

SQL>

You can specify a LIMIT as follows:

SQL> create profile andrews_profile
  2  limit password_life_time 60
  3  /

Profile created.

SQL>

In the example above, the LIMIT is on the number of days you are allowed to use the same password. There are various other LIMITs you can set. I will be looking at them in other posts.

You cannot have more than one profile with a given name. If you try to do so, you get an ORA-02379:

SQL> create profile andrews_profile
  2  limit failed_login_attempts 5
  3  /
create profile andrews_profile
*
ERROR at line 1:
ORA-02379: profile ANDREWS_PROFILE already exists

SQL>

ORA-04050

This was tested on Oracle 11.2. When you create a profile, its PASSWORD_VERIFY_FUNCTION (PVF) will be shown as DEFAULT if you do not specify it explicitly:

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> create profile andrews_profile

  2  limit password_life_time 60

  3  /

Profile created.

SQL> select limit from dba_profiles

  2  where profile = 'ANDREWS_PROFILE'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

LIMIT

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

DEFAULT 

SQL> 

This does not mean that it has a PVF called DEFAULT. It means that it has the same PVF as the DEFAULT profile. In this database the DEFAULT profile's PVF is NULL, which means it has no PVF:

SQL> select limit from dba_profiles

  2  where profile = 'DEFAULT'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

LIMIT

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

NULL 

SQL> 

You can create a function to use as a PVF.  Here is a very simple example:

SQL> create or replace function andrews_verify_function(

  2    username     varchar2,

  3    password     varchar2,

  4    old_password varchar2)

  5    return boolean as

  6  begin

  7    if length(password) < 4 then

  8      return false;

  9    else

 10      return true;

 11    end if;

 12  end andrews_verify_function;

 13  /

Function created. 

SQL> 

You can make a profile use it like this:

SQL> alter profile andrews_profile limit

  2  password_verify_function andrews_verify_function

  3  /

 

Profile altered.

 

SQL>

... and the name of the function will be stored in DBA_PROFILES:

SQL> select limit from dba_profiles

  2  where profile = 'ANDREWS_PROFILE'

  3  and resource_name = 'PASSWORD_VERIFY_FUNCTION'

  4  /

 

LIMIT

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

ANDREWS_VERIFY_FUNCTION

 

SQL>

Then I saw a potential problem. If I had a function called DEFAULT, and a profile with PVF set to DEFAULT, how would Oracle know if I was referring to the function called DEFAULT or the PVF used by the DEFAULT profile? Fortunately the clever people at Oracle have thought of that. If you try to create a function called DEFAULT, you get an error:

SQL> create or replace function default(

  2    username     varchar2,

  3    password     varchar2,

  4    old_password varchar2)

  5    return boolean as

  6  begin

  7    if length(password) < 4 then

  8      return false;

  9    else

 10      return true;

 11    end if;

 12  end default;

 13  /

create or replace function default(

                           *

ERROR at line 1:

ORA-04050: invalid or missing procedure, function, or

package name

SQL>

Reply to: Reply to Andrew Reid

Send

Oracle 12c New Feature?

I was given some SQL today which included a CREATE OR REPLACE TABLE command. Unfortunately no such command exists in Oracle 11.2 so the statement failed.

SQL> l

  1* create or replace table blah (col1 number)

SQL> /

create or replace table blah (col1 number)

                  *

ERROR at line 1:

ORA-00922: missing or invalid option

SQL>

Perhaps it will be introduced in Oracle 12c!

QUERY_REWRITE_ENABLED

In some books and also in Oracle’s own documentation, it says that the QUERY_REWRITE_ENABLED initialisation parameter has to be set to TRUE before the optimiser will be able to use a function based index. I decided to check this for myself.

According to Oracle’s own documentation, the default for this parameter in Oracle 9 was FALSE. This does seem to be correct:

ORACLE 9 > sqlplus /

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Oct 9 16:57:46 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> col value format a10

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'query_rewrite_enabled'

  4  /

VALUE      ISDEFAULT

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

false      TRUE

SQL>

Oracle’s own documentation also says that, in later versions, the default value is TRUE. This seems to be correct too:

ORACLE 10 > sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 9 17:04:04 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> col value format a10

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'query_rewrite_enabled';

VALUE      ISDEFAULT

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

TRUE       TRUE

SQL>

I did the rest of this test on Oracle 9.2.0.7.0. First I set the parameter to FALSE:

SQL> alter session

  2  set query_rewrite_enabled = false

  3  /

Session altered.

SQL>

Then I created a table with a function based index:

SQL> create table andrew

  2  as select * from dba_objects

  3  /

Table created.

SQL> create index fbi

  2  on andrew(to_char(created,'YYYYMM'))

  3  /

Index created.

SQL>

I checked that Oracle knew it was a function based index:

SQL> select index_type, funcidx_status

  2  from user_indexes

  3  where index_name = 'FBI'

  4  /

INDEX_TYPE             FUNCIDX_STATUS

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

FUNCTION-BASED NORMAL  ENABLED

SQL>

I monitored the usage of the index:

SQL> alter index fbi monitoring usage

  2  /

Index altered.

SQL>

Then I ran a query on the table. I did not expect Oracle to use the index, as QUERY_REWRITE_ENABLED was set to FALSE, but it did (you may need to use your browser's zoom button to read the execution plan):

SQL> set autotrace on explain

SQL> select count(*) from andrew

  2  where to_char(created,'YYYYMM') = '201201'

  3  /

  COUNT(*)

----------

       181

Execution Plan

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

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=9)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=1 Card=35

          1 Bytes=3159)

   

SQL> set autotrace off

SQL>

... and when I checked afterwards, Oracle confirmed that the index had been used:

SQL> select index_name, start_monitoring,

  2  monitoring, used from v$object_usage

  3  /

INDEX_NAME START_MONITORING    MONITORING USED

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

FBI        10/09/2012 18:27:01 YES        YES

SQL>

So, on the basis of this test, I do not agree with the documentation. Once I had finished this test, I had a look on the Internet and found the URL below on OTN. It seems that an Oracle ACE called Justin Cave agrees with me so at least I am in good company!

https://forums.oracle.com/forums/thread.jspa?threadID=946971