Tablespace and userid info

oracle

I have to create some scripts to create a new and empty DB ready for a new install of our apps on a new server.

I am easily getting the tables, views etc info from SQL Developers Export facility (without the data of course), but I have a problem where I need to define the userid and the TableSpace.

I realise these are quite straight forward, but what I would like to know is how the original ones are defined on our current DB, as the original scripts (and info) are no longer available, and I need to make sure I create them correctly with the new scripts.

So how do I display all the required info for a userid and for a TableSpace so that I can use this info in the create commands in the scripts ?

It may be helpful for you to know this is my first attempt at scripting and only my 2nd attempt at and interfacing with Oracle, so I have limited knowledge in this field, hence the simple request. 🙂

Any help or advice would be most appreciated.

Cheers, George.

Best Answer

Have a look at the DBMS_METADATA package. First set long to some big number so the output isn't truncated, and set the SQL terminator so you can see how statements are separated:

SQL> set long 30000
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

To get the tablespace (substitute your own for USERS of course):

SQL> select dbms_metadata.get_ddl('TABLESPACE', 'USERS') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE
  '/home/oracle/app/oracle/oradata/orcl/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;
   ALTER DATABASE DATAFILE
  '/home/oracle/app/oracle/oradata/orcl/users01.dbf' RESIZE 256901120;

You'll probably need to modify the datafile path for the new server. To get the user:

SQL> select dbms_metadata.get_ddl('USER', 'SCOTT') from dual;

DBMS_METADATA.GET_DDL('USER','SCOTT')
--------------------------------------------------------------------------------

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:64F10A7102E73EFFAC1BE155794F1E8DA6C8F948A5713D5D5EC462AA0382;4DD35D67F0372586'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";

Then you need the grants the user has. Depending on your actual user some of these may not return anything:

SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
--------------------------------------------------------------------------------

  GRANT CHANGE NOTIFICATION TO "SCOTT";
  GRANT DROP ANY DIRECTORY TO "SCOTT";
  GRANT CREATE ANY DIRECTORY TO "SCOTT";
  GRANT CREATE DATABASE LINK TO "SCOTT";
  GRANT CREATE VIEW TO "SCOTT";
  GRANT CREATE SYNONYM TO "SCOTT";
  GRANT CREATE TABLE TO "SCOTT";
  GRANT UNLIMITED TABLESPACE TO "SCOTT";
  GRANT ALTER SESSION TO "SCOTT";


SQL> select dbms_metadata.get_granted_ddl('ROLE_GRANT', 'SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT')
--------------------------------------------------------------------------------

   GRANT "CONNECT" TO "SCOTT";
   GRANT "RESOURCE" TO "SCOTT";
   GRANT "XFILES_USER" TO "SCOTT";


SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')
--------------------------------------------------------------------------------

  GRANT EXECUTE ON "SYS"."DBMS_LOCK" TO "SCOTT";
  GRANT EXECUTE ON "SYS"."DBMS_CRYPTO" TO "SCOTT";
  GRANT EXECUTE, READ, WRITE ON DIRECTORY "SO_DIR" TO "SCOTT";

You can use the same functions to get your table and view scripts if you prefer - I believe SQL Developer's export uses them under the hood anyway - and to get role definitions and grants.

Related Question