DROP TABLE ... PURGE

I tested this on Oracle 11.2.0.2.7. If the recyclebin is in use:

SQL> conn / as sysdba

Connected.

SQL> select value from v$parameter

  2  where name = 'recyclebin'

  3  /

 

VALUE

----------

on

 

SQL> 

... and you create a table in a tablespace other than system: 

SQL> create table tab1

  2  (col1 number)

  3  tablespace users

  4  /

 

Table created.

 

SQL>

... it goes in the recyclebin when you drop it:

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL> select original_name from recyclebin

  2  /

 

ORIGINAL_NAME

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

TAB1

 

SQL>

… and you can restore it from there if you need to: 

SQL> flashback table tab1 to before drop

  2  /

 

Flashback complete.

 

SQL> desc tab1

Name                       Null?    Type

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

COL1                                NUMBER

 

SQL>

If you do not want this to happen, add the word purge at the end of the drop table statement:

SQL> drop table tab1 purge

  2  /

 

Table dropped.

 

SQL>

... then it does not go into the recyclebin when you drop it: 

SQL> select original_name from recyclebin

  2  /

 

no rows selected

 

SQL>

... and you cannot restore it afterwards:

SQL> flashback table tab1 to before drop

  2  /

flashback table tab1 to before drop

*

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

 

SQL>

Oracle export and deferred segment creation

A colleague told me about an issue when using export on an Oracle 11.2.0.1 database. He said that if the database contains tables created with segment creation deferred, those tables will not be exported. I decided to check this out. First I created a couple of tables in an Oracle 11.2.0.1 database, one with segment creation deferred and one with segment creation immediate: 

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 to andrew

  4  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> create table scd (col1 number)

  2  segment creation deferred

  3  /

 

Table created.

 

SQL> create table sci (col1 number)

  2  segment creation immediate

  3  /

 

Table created.

 

SQL>

 

Then I exported the Andrew schema. Here is the log file produced. As you can see, only the table with segment creation immediate was exported:

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

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

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P15 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user ANDREW

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user ANDREW

About to export ANDREW's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export ANDREW's tables via Conventional Path ...

. . exporting table                            SCI          0 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

 

I had a look at the page about export in Oracle’s 11.2 documentation on the Internet and saw the following:

 

The original Export utility is no longer being updated to support new datatypes.

 

So I repeated the process in an Oracle 11.2.0.2.7 database. First I created the tables as before:

 

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 to andrew

  4  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> create table scd (col1 number)

  2  segment creation deferred

  3  /

 

Table created.

 

SQL> create table sci (col1 number)

  2  segment creation immediate

  3  /

 

Table created.

 

SQL>

 

Then I did the export and, to my surprise, both tables were copied. You can see what I mean in the log below:

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

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

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8MSWIN1252 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user ANDREW

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user ANDREW

About to export ANDREW's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export ANDREW's tables via Conventional Path ...

. . exporting table                            SCD          0 rows exported

. . exporting table                            SCI          0 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

Raw Columns

This was tested on Oracle 11.2. You can use a raw column to store binary data:

SQL> create table my_table (raw_column raw(20))

  2  /

Table created.

SQL>

If you try to insert character data into it directly, you get an ORA-01465:

SQL> insert into my_table values ('Andrew Reid')

  2  /

insert into my_table values ('Andrew Reid')

                              *

ERROR at line 1:

ORA-01465: invalid hex number

SQL>

You have to convert it to binary first as follows:

SQL> insert into my_table values

  2  (utl_raw.cast_to_raw('Andrew Reid'))

  3  /

1 row created.

SQL>

Conversely, if you try to read the data, it is displayed in hexadecimal format:

SQL> select * from my_table

  2  /

RAW_COLUMN

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

416E647265772052656964

SQL>

… but you can read it like this:

SQL> select utl_raw.cast_to_varchar2(raw_column)

  2  from my_table

  3  /

UTL_RAW.CAST_TO_VARCHAR2(RAW_COLUMN)

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

Andrew Reid

SQL>