Home > Database, Oracle > Oracle DDL Generation using DBMS_METADATA.GET_DDL

Oracle DDL Generation using DBMS_METADATA.GET_DDL

August 26th, 2014

I find it rather useful to extract and reuse DDL from existing systems.

This allows me to create objects with confidence depending on the system of extraction.

I recently had to recreate a couple of new tablespaces in line with the existing ones.

In the example below, the existing tablespace name is TAB_BIN_OBJ.

Run the below as SYS user

set long 20000
set linesize 163
set pagesize 2000
column ddl format a160
SQL>  select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)||';' ddl from dba_tablespaces where TABLESPACE_NAME in ('TAB_BIN_OBJ');

The output in my case is as follows:

CREATE TABLESPACE "TAB_BIN_OBJ" DATAFILE
'/u02/oradata/OBJINST/tab_bin_obj_01.dbf' SIZE 5242880000
AUTOEXTEND ON NEXT 106496 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'/u02/oradata/OBJINST/tab_bin_obj_01.dbf' RESIZE 101711872;

Hope this helps!

 

Categories: Database, Oracle Tags:
Comments are closed.