11g SYSASM Role

Well well well, Oracle we hear you.

So, SYSDBA does not have privileges to administer ASM instance any more!!! OK fine we will start using SYSASM role to administer then...

/home/oracle:(+ASM)$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 8 13:50:43 2010

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

SQL> conn / as sysdba

Connected.

SQL> shutdown

ORA-01031: insufficient privileges

Now try this:

SQL> conn / as sysasm

Connected.

SQL> shutdown

SQL> database dismounted.

As I said something to learn new everyday... things are never same...

Flashback in 1,2,3

No need to say what flashback means as we all seen atleast one movie that either takes us to our previous memories or the movie actors itself will go back in their memories!!!

Well, the same concept applies for Oracle Database also.

In simple terms, you just feel like to revert all your database changes to certain point of time before you either you messed up the data (well, by doing some kinda testing) or altering some structure of the database.

Or, can be used to prepare your database to restore to the point where you wanted to be after allowing other departments to work on the database.

OK, enough theory over here. Lets get to practical as to how easy it is:

Pre-Req:

1. Database needs to be running in Archive Log Mode (no need to be in archive log from the day one. But only when you want to start FLASHBACK till the time you are done with your FLASHBACK).

2. Some diskspace to record/log all the objects (that are only being modified) old images/copy while the database is in FLASHBACK mode.

Steps:

If the Database is already in ARCHIVELOG then move on to step #4:

1. shutdown immediate;

2. startup mount;

3. alter database archivelog;

4. alter system set db_recovery_file_dest_size=2G;

Note: 2G is to store all the modified object image copies before the modification is done.

5. alter system set db_recovery_file_dest='/location_where_you_want_the_FlashBack_Log_should be created';

6. alter database flashback on;

7. alter database open;

8. create restore point started_messing_it_up;

Note: remember the name that you are giving here. In this case it's "started_messing_it_up"

Let the users start using the database for a while now.

Once you are ready to Flashback/Re-Wind to the point where you wanted to be:

1. shutdown immediate;

2. startup mount;

3. flashback database to restore point started_messing_it_up;

Note: the name is the one that you have given at the time of creating the restore point.

4. alter database open resetlogs;

Now, your database is just like how it was before creating the restore point.

All Tables in Hierarchical Order (Tables in Parent Child order)

Here is the script that can be used to produce a nice Hierarchical View for all the tables with in the schema. (parent-children-grand children-grand grand children ... and continues).

select level, rpad( '*', (level-1)*2, '*' ) || table_name table_name

, pkey_constraint, fkey_constraint, r_constraint_name

from (select a.table_name, a.constraint_name pkey_constraint,

b.constraint_name fkey_constraint, b.r_constraint_name

from user_constraints a, user_constraints b

where a.table_name = b.table_name

and a.constraint_type in ( 'P','U')

and b.constraint_type = 'R'

union all

select table_name, constraint_name, null, null

from user_constraints

where constraint_type= 'P'

--TO COVER THE CHILDRENS WHO DONT HAVE EITHER PK OR UK

union all

select table_name, null, null, r_constraint_name

from user_constraints uc

where constraint_type = 'R'

and not exists (select 1 from user_constraints uc1

where uc1.table_name = uc.table_name

and uc1.constraint_type in ('P','U'))

)

start with fkey_constraint is null

--and table_name = 'SANTHOSHCHANNA' --un-comment this line and provide particular table name that you are looking for to get it child's.

connect by nocycle prior pkey_constraint = r_constraint_name