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

Ref -

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
Mon May 12 11:51:32 2014
I found a great post that gave me some good tips on solving the issue

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

col1, col2...) using index pk_index_name;
Took 1 second.


  1. Replies
    1. Great Article IoT Projects for Students

      Deep Learning Projects for Final Year

      JavaScript Training in Chennai

      JavaScript Training in Chennai

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training


  2. Really appreciate this wonderful post that you have provided for us.Great site and a great topic as well I really get amazed to read this. It's really good.
    I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!.
    mobile phone repair in Fredericksburg
    iphone repair in Fredericksburg
    cell phone repair in Fredericksburg
    phone repair in Fredericksburg
    tablet repair in Fredericksburg
    mobile phone repair in Fredericksburg
    mobile phone repair Fredericksburg
    iphone repair Fredericksburg
    cell phone repair Fredericksburg
    phone repair Fredericksburg

  3. Established in 2011 and based in Malta, Videoslots is a web-based on line casino with more than eight,000 gaming slots. Alexander Stevendahl is the founder and chief government officer. In 2017, Videoslots bought the expertise assets of, a web-based poker operator. You don’t need to be a fan of the tv game present to take pleasure in this thrilling slot game from IGT. Traditional fruit symbols mix 1xbet it up with exotic objects like jewels, a convertible sports activities automotive, and a yacht.