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.

3 comments:

  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

      Delete


  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

    ReplyDelete