All,
I have been tasked with creating a script that will dump the DDL for all tablespaces in our instances. Now I know this can be easily done using the DBMS_METADATA.GET_DDL package but my supervisor wants specific output. For example, GET_DDL outputs as bytes and he is requesting all file sizes to be in Megabytes. I wrote the following PL/SQL below that does exactly that but he insist that this can be done without be getting a total number of data files records for the tablespace prior to creating the section for the data files and their sizes. My current script takes into account if there are multiple data files for a tablespace and will create the appropriate formatting as per his Megabyte file size specification. My question is can I create the section for multiple data files without knowing the total number of records prior to?
Here is my code (Updated to get TEMP file data files):
set pages 0
set long 90000
set lines 131
column txt format a121 word_wrapped
set feedback on
set echo off
set longchunksize 250
set serveroutput on
execute dbms_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
spool /opt/oracle/admin/${SID}/scripts/create_DBCAT_tablespaces.sql
SET SERVEROUTPUT ON;
DECLARE
multiple_datafiles NUMBER :=0;
multiple_tempfiles NUMBER :=0;
CURSOR dbatbsp IS
SELECT tablespace_name, encrypted FROM dba_tablespaces ORDER BY tablespace_name;
CURSOR dbadf (t in VARCHAR2) IS
SELECT tablespace_name, file_name, bytes, increment_by FROM dba_data_files WHERE tablespace_name = upper(t) ORDER BY file_name;
CURSOR dbatf (o in VARCHAR2) IS
SELECT tablespace_name, file_name, bytes, increment_by FROM dba_temp_files WHERE tablespace_name = upper(o) ORDER BY file_name;
BEGIN
dbms_output.enable(1000000);
FOR tbsprec IN dbatbsp LOOP
dbms_output.put_line('CREATE TABLESPACE ' || tbsprec.tablespace_name || ' LOGGING DATAFILE ');
FOR dfrectemp IN dbadf (tbsprec.tablespace_name) LOOP
multiple_datafiles := dbadf%rowcount;
END LOOP;
FOR tfrectemp IN dbatf (tbsprec.tablespace_name) LOOP
multiple_tempfiles := dbatf%rowcount;
END LOOP;
FOR dfrec IN dbadf (tbsprec.tablespace_name) LOOP
IF multiple_datafiles != dbadf%rowcount THEN
dbms_output.put_line('''' || dfrec.file_name || ''''|| ' SIZE ' || TRUNC(dfrec.bytes/1024/1024,0) || 'M REUSE AUTOEXTEND ON NEXT ' || TRUNC(dfrec.increment_by*16/1024,0) || 'M MAXSIZE UNLIMITED, ');
ELSE
dbms_output.put_line('''' || dfrec.file_name || '''' || ' SIZE ' || TRUNC(dfrec.bytes/1024/1024,0) || 'M REUSE AUTOEXTEND ON NEXT ' || TRUNC(dfrec.increment_by*16/1024,0) || 'M MAXSIZE UNLIMITED ');
END IF;
END LOOP;
FOR tfrec IN dbatf (tbsprec.tablespace_name) LOOP
IF multiple_tempfiles != dbatf%rowcount THEN
dbms_output.put_line('''' || tfrec.file_name || ''''|| ' SIZE ' || TRUNC(tfrec.bytes/1024/1024,0) || 'M REUSE AUTOEXTEND ON NEXT ' || TRUNC(tfrec.increment_by*16/1024,0) || 'M MAXSIZE UNLIMITED, ');
ELSE
dbms_output.put_line('''' || tfrec.file_name || '''' || ' SIZE ' || TRUNC(tfrec.bytes/1024/1024,0) || 'M REUSE AUTOEXTEND ON NEXT ' || TRUNC(tfrec.increment_by*16/1024,0) || 'M MAXSIZE UNLIMITED ');
END IF;
END LOOP;
IF tbsprec.encrypted = 'YES' THEN
dbms_output.put_line('EXTENT MANAGEMENT LOCAL ENCRYPTION DEFAULT STORAGE(ENCRYPT) SEGMENT SPACE MANAGEMENT AUTO;');
dbms_output.put(chr(10));
dbms_output.put(chr(10));
ELSE
dbms_output.put_line('EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;');
dbms_output.put(chr(10));
dbms_output.put(chr(10));
END IF;
END LOOP;
END;
/
spool off
exit;
And here is the output generated by my code:
SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 27 10:14:21 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
CREATE TABLESPACE DBCAT LOGGING DATAFILE
'/opt/oracle/oradata/DBCAT/dbcat01.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT 1000M
MAXSIZE UNLIMITED,
'/opt/oracle/oradata/DBCAT/dbcat02.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 200M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE DBCAT_ENC LOGGING DATAFILE
'/opt/oracle/oradata/DBCAT/dbcat_enc01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT
200M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL ENCRYPTION DEFAULT STORAGE(ENCRYPT) SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE DBCAT_INDEX_ENC LOGGING DATAFILE
'/opt/oracle/oradata/DBCAT/dbcatind_enc01.dbf' SIZE 1024M REUSE AUTOEXTEND ON
NEXT 200M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL ENCRYPTION DEFAULT STORAGE(ENCRYPT) SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE SAO_DBCAT LOGGING DATAFILE
'/opt/oracle/oradata/DBCAT/sao_dbcat01.dbf' SIZE 1M REUSE AUTOEXTEND ON NEXT 2M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL ENCRYPTION DEFAULT STORAGE(ENCRYPT) SEGMENT SPACE
MANAGEMENT AUTO;
CREATE TABLESPACE STATSPACK_DATA LOGGING DATAFILE
'/opt/oracle/oradata/DBCAT/STATSPACK_DATA.DBF' SIZE 500M REUSE AUTOEXTEND ON
NEXT 20M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE SYSAUX LOGGING DATAFILE
'/opt/oracle/oradata/DBCAT/sysaux01.dbf' SIZE 2000M REUSE AUTOEXTEND ON NEXT
400M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE SYSTEM LOGGING DATAFILE
'/opt/oracle/oradata/DBCAT/system01.dbf' SIZE 2000M REUSE AUTOEXTEND ON NEXT
400M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE TEMP LOGGING DATAFILE
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE UNDOTBS1 LOGGING DATAFILE
'/opt/oracle/oradata/DBCAT/undotbs01.dbf' SIZE 870M REUSE AUTOEXTEND ON NEXT 10M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE USERS LOGGING DATAFILE
'/opt/oracle/oradata/DBCAT/users01.dbf' SIZE 203M REUSE AUTOEXTEND ON NEXT 2M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
PL/SQL procedure successfully completed.
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Message was edited by: wgraydba