Tablespace and userid info

Question

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.

Answer 1

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.

Answer 2

An option that can be interesting for this type of thing is to use expdp/impdp to get all the metadata from your source database en masse.

You'd do this by, for example:

1. Running a full export of your source database's metadata
2. Doing a few SQLFILE-mode imports, which don't actually import anything but generates SQL scripts for the objects you're interested in.

You can filter by schema for instance, and use most of the options expdp and impdp have to customize the SQL, and modify it if you need to before using it.

Sample usage:

expdp user/*** directory=EXPORT_DIR dumpfile=metadata.dmp \
               full=Y content=METADATA_ONLY exclude=STATISTICS 

This will export all your database's metadata to metadata.dmp (provided you've created the EXPORT_DIR directory in the database), excluding statistics information (I'm assuming you're not going to reload the same data, so the source statistics are irrelevant).

For tablespace definitions:

impdp user/*** directory=EXPORT_DIR sqlfile=tablespaces.sql \
               include=TABLESPACE

To generate a table and index creation SQL script (tables.sql):

impdp user/*** directory=EXPORT_DIR sqlfile=tables.sql \
               include=TABLE schemas=USER1,USER2,USER3

To generate user and grant creation script:

impdp user/*** directory=EXPORT_DIR sqlfile=users.sql \
               include=USER,GRANT schemas=USER1,USER2,USER3

This is pretty handy if you want to script the process outside the database (the generated SQL is very regular, so relatively safe to reprocess). If you want to script the process in PL/SQL, DBMS_METADATA is definitely the way to go as Alex Poole's answer describes.