Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
You should store all scripts to create all your database objects in a version control system. Then you just replay them on the new server. You should really consider doing that once you have extracted everything. –  a_horse_with_no_name Oct 13 '12 at 8:05
    
I agree, this important info should always be available. I will make sure it is all stored when I am finished this small project. –  Haglis Oct 15 '12 at 9:11
add comment

2 Answers

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.

share|improve this answer
    
Hi Alex, thanks for this, its exactly what I was looking for. Perhaps you can answer a further question pls/ I tried to create a new userid but there was a PASSWORD-VERIFY_FUNCTION listed and it couldnt find the "user_function". I tried to look up the "function" in the same way you have listed but it could not find it on the current system, so I dont know how to add this fuctions I have no idea whats in it. Any suggestions ? –  Haglis Oct 15 '12 at 9:09
    
@Haglis - you should be able to see it like this, but maybe it's in a different schema (which you can specify with the third parameter to get_ddl)? Can you see it in dba_objects? Also check you're using the right case for the function name. –  Alex Poole Oct 15 '12 at 10:23
    
Alex, I am not sure what you mean by can I see it in dba_objects ? –  Haglis Oct 15 '12 at 10:58
    
@Haglis - select owner, object_type, object_name from dba_objects where object_type = 'FUNCTION' and lower(object_name) = 'user_function' will show if the function exists, which schema owns it, and if you're using the wrong case. –  Alex Poole Oct 15 '12 at 11:03
    
It doest come back with anything other than the headings. –  Haglis Oct 15 '12 at 12:30
show 1 more comment

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.

share|improve this answer
    
Thanks for this info too, we will need to export/import in the future too which will also make me nervous ! :) –  Haglis Oct 15 '12 at 9:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.