Oracle....the small things

I have been neglecting my blog recently and decided to devote a page to some of the small things I have done.   They are not necessarily about how something works instead some are showing syntax and others useful things.  Also this is a reminder for myself something.  I will be adding to this page with more stuff.
Oracle - CREATE TABLE <name> AS........
The Oracle Recycle Bin
Oracle - Getting the DDL for an object
Corrupt UNDO segment. 
Loading Large Tables with a PK



Oracle - CREATE TABLE <name> AS........
Using Oracle's CREATE TABLE <name> AS SELECT * FROM syntax creates a replica of a table but does not preserve partition, primark keys or indexes.
MySQL uses the CREATE TABLE <name> LIKE... which copies the structure.

The Oracle Recycle Bin
Whats in it
select object_name, original_name, type, can_undrop, can_purge, droptime  from recyclebin;
Getting tables back
flashback table t1 to before drop;
Getting specific tables back
flashback table “<recycle bin name>″ to before drop;

Turning the recyclebin off
ALTER SESSION SET RECYCLEBIN=OFF

Ref - http://www.orafaq.com/node/968

Oracle - Getting the DDL for an object
MySQL, MariaDB and other variants have a great way of getting the DDL for an object - SHOW CREATE TABLE <name>.  Oracle doesn't have the same facility but you can use DBMS_METADATA:
SQL> SET long 9999999
SQL>  select DBMS_METADATA.get_ddl ('TABLE','TESTTAB') from user_tables where table_name = 'TESTTAB';
For indexes:
SQL>  select DBMS_METADATA.get_ddl ('INDEX','TESTTAB_IDX') from user_indexes where table_name = 'TESTTAB';

Corrupt UNDO segment.
I have never seen this before but after what I can only think was a session that had been aborted the rbs segment was corrupt:
11:25:17 SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU8_2012382730$           NEEDS RECOVERY   UNDOTBS1

I was not sure how to rectify this but I noticed the database took over 5 minutes to shutdown.  When tailing the alert.log it would look like it was hung after the system did the following:
Mon May 12 11:47:32 2014
SMON: slave died unexpectedly, downgrading to serial recovery
ALTER DATABASE CLOSE NORMAL
Mon May 12 11:51:32 2014
I found a great post that gave me some good tips on solving the issue
http://mikegeorgiou.blogspot.co.uk/2008/06/drop-corrupted-undo-tablespace-with.html

Loading Large Tables with a PK
Recently I had to load 111 million rows into an Oracle table from another table.  The table was pre-created and I ran a simple INSERT INTO...SELECT * FROM command.  This was taking a long time (10 hours +) so I dropped the PK and rerun the statement.  It was much quicker.  Next I need to add the PK.  I created the unique index first (using the parallel clause) and then ran an alter table to add the constraint.  This allowed me to create the PK with a parallel clause which is far quicker.
create unique index pk_index_name on <table> (col1, col2...) parallel 4;
Took 40 mins


ALTER TABLE <table_name> ADD CONSTRAINT pk_con_name PRIMARY KEY (col1, col2...) using index pk_index_name;
Took 1 second.