Script to Generate and Spool DDL / SQL statements for Tablespaces in Oracle Database

advertisements

_____________________________________________________________________________________________________________________

As a DBA sometimes we need to spool the scripts for the tablespace for creating the new databases. For that you can use DBMS_METADATA package to extract the DDL script for the tablespace easily.

Below mentioned script you can use for the same purpose.

Script to generate all tablespaces. 

Method 1

To generate the Data Definition Language (DDL) statements for all tablespaces in an Oracle database, you can use the following SQL script. This script queries the DBA_TABLESPACES view to retrieve information about all tablespaces and generates DDL statements to recreate them:

set heading off
set long 10000
set pages 0
set feedback off
spool tbs.sql
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)||';' from dba_tablespaces;
spool off

advertisements

Method 2 - PLSQL script

SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET PAGESIZE 0

DECLARE
  v_sql CLOB;
BEGIN
  FOR ts IN (SELECT tablespace_name FROM dba_tablespaces) LOOP
    v_sql := 'SELECT DBMS_METADATA.GET_DDL(''TABLESPACE'', ''' || ts.tablespace_name || ''') FROM DUAL';
    EXECUTE IMMEDIATE v_sql;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('DDL extraction completed successfully.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Script to generate script for single tablespace

SET VERIFY OFF
ACCEPT tablespace_name PROMPT 'Enter tablespace name: '

SELECT DBMS_METADATA.GET_DDL('TABLESPACE', '&tablespace_name') AS ddl
FROM DUAL;

Sample output

Enter tablespace name: YOUR_TABLESPACE_NAME
DDL
----------------------------------------------
CREATE TABLESPACE "YOUR_TABLESPACE_NAME" DATAFILE '/path/to/your_tablespace_file.dbf' SIZE 104857600 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

Extract DDL SQL using imp show=y

_____________________________________________________________________________________________________________________

Website Stats