Tablespace Creation Goodies

Back in the day. The method I used for a database clone would be to

1. Create a shell database


2. create the schema owner


3. grant connect, session etc sometimes all privledges


4. Create the table spaces


5. then import the database using the Fromuser=Sysadm toUser=sysadm function from an existing export.

Now assuming the tablespaces were already there, this would be cake. But if it was a new database this tends to be a pain if you dont already have a script. I found a new package that helps to create the DDL for tablespace creation. So I made a script:

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLESPACE','HRLARGE') from dual;
spool off;

here is what it returns:

CREATE TABLESPACE "HRLARGE" DATAFILE '/u03/oradata/fs8prd/hrlarge.dbf' SIZE 1048576 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO X
1 row selected.

You could probably use sql to make sql to select all tablespaces then combine it with the package name. Anyone done this?