dbms_metadata.get_ddl user

Extract CREATE USER DDL

It is a DBA task to create users or migrate users from one database environment to another.

Let’s use DBMS_METADATA.GET_DDL package to extract CREATE USER DDL.

SQL> select dbms_metadata.get_ddl

  2   ('USER',username)

  3   as col1

  4  from dba_users

  5  where username

  6   = 'SCOTT';

COL1

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

CREATE USER "SCOTT" IDENTIFIED BY

VALUES

'S:6BA47D5BAA9A46E9A3E9073AA77F486B

01E2AF0AA28D98515B969693B8AE;191F2D

E583F11B20'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP";

Let’s get the system grants and object grants using function GET_GRANTED_DLL for this user SCOTT.

SQL> select dbms_metadata.get_granted_ddl

  2   ('SYSTEM_GRANT',username)

  3  as col1

  4  from dba_users

  5  where username

  6   = 'SCOTT';

COL1

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

GRANT SELECT ANY TABLE TO "SCOTT";

GRANT UNLIMITED TABLESPACE TO

"SCOTT";

GRANT CREATE SESSION TO "SCOTT";

Changing the value from SYSTEM_GRANT to OBJECT_GRANT, and running the same SQL, gives the following object grants given to SCOTT.

SQL> select dbms_metadata.get_granted_ddl

  2   ('OBJECT_GRANT',username)

  3  as col1

  4  from dba_users

  5  where username

  6   = 'SCOTT';

COL1

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

GRANT ALTER, DELETE, INDEX, INSERT,

SELECT, UPDATE, REFERENCES, ON

COMMIT REFRESH, QUERY REWRITE,

DEBUG, FLASHBACK ON "BHAVESH"."T11"

TO "SCOTT";

GRANT DELETE, INSERT, SELECT,

UPDATE ON "BHAVESH"."T2" TO

"SCOTT";

GRANT DELETE, INSERT, SELECT,

UPDATE ON "BHAVESH"."T3" TO

"SCOTT";

GRANT DELETE, INSERT, SELECT,

UPDATE ON "BHAVESH"."T4" TO

"SCOTT";

The following SQL gives the roles granted to username SCOTT.

SQL> select dbms_metadata.get_granted_ddl

  2   ('ROLE_GRANT',username)

  3  as col1

  4  from dba_users

  5  where username

  6   = 'SCOTT';

COL1

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

GRANT "CONNECT" TO "SCOTT";

GRANT "RESOURCE" TO "SCOTT";

GRANT "DEV_ROLE" TO "SCOTT";

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

If you are interested in the book "Oracle SQL Scipting". here is the link.
http://www.amazon.com/Oracle-SQL-Scripting-ebook/dp/B00E2T1DVA/ref=sr_1_1?ie=UTF8&qid=1374436123&sr=8-1&keywords=oracle+sql+scripting

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 

reset sequence Oracle

When you export Oracle data and then import to another environment, sometimes the sequence number gives primary key  errors. In this situation, we need to reset Oracle sequences.

This example, we bump up all the sequences by 100.

I have 10 sequences in my schema and I want to increase all of them by 100.

SQL> select sequence_name, last_number from user_sequences;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
S1                                       1
S10                                      1
S2                                       1
S3                                       1
S4                                       1
S5                                       1
S6                                       1
S7                                       1
S8                                       1
S9                                       1

10 rows selected.

Currently, their last_number is one because I just created them. In real world, your sequnces' last_number will be different!

Here is a little PL/SQL block that will bump up the sequence numbers by 100.

declare
   my_seq_number number;
   my_sql varchar2(200);
  
   -- increase the sequence by 100..change it if you want
  my_increase_seq_by_number NUMBER := 100;
begin
   for my_sequneces in ( select sequence_name from user_sequences
                       )
loop 
    my_sql := 'select ' || my_sequneces.sequence_name || '.nextval from dual';
      for i in 1..my_increase_seq_by_number
      loop
        execute immediate my_sql into my_seq_number;
      end loop;
end loop;
end;
/


After executing the above PL/SQL block, here is the last_number for all my sequences.

SQL> select sequence_name, last_number from user_sequences;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
S1                                     101
S10                                    101
S2                                     101
S3                                     101
S4                                     101
S5                                     101
S6                                     101
S7                                     101
S8                                     101
S9                                     101

10 rows selected.

Hope, this helps.
Please send your feedbacks  to poojasoft2011@gmail.com
Thank you.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

If you are interested in the book "Oracle SQL Scipting". here is the link.
http://www.amazon.com/Oracle-SQL-Scripting-ebook/dp/B00E2T1DVA/ref=sr_1_1?ie=UTF8&qid=1374436123&sr=8-1&keywords=oracle+sql+scripting

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++