Oracle tablespaces provide storage of permanent and temporary data within the an Oracle database. The two forms of Oracle tablespaces are permanent which is used to store data which can survive restart of the database and temporary which is used for sort and other temporary storage requirements and does not require survival on restart of the database. In this article we will detail a simple script to recreation of the DDL necessary to transport a tablespace structure between databases. Feel free to modify the script to allow for more complex tablespace definitions.
1. Connect to your Oracle database server and logon to SQL*PLUS.
MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue Aug 22 13:30:13 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Create a permanent tablespace to demonstrate the recreation of your DDL with the following statements.
create tablespace test_data datafile ‘/u02/ORADATA/ORCL10G/test_data01.dbf’ size 1m, ‘/u02/ORADATA/ORCL10G/TEST_data02.dbf’ size 1m;
3. Execute the following PL\SQL block to reproduce the DDL for a tablespace defined by the variable V_TS_NM.
set serveroutput on
spool ./create_tablespace.lst
declare
v_ct number:=0;
v_ct_files number;
v_ts_nm varchar2(30) := ‘TEST_DATA’;
begin
dbms_output.put_line(‘create tablespace ‘||v_ts_nm||’ datafile ‘);
select count(*) into v_ct_files from dba_data_files where tablespace_name=v_ts_nm;
for v_file in(select file_name, bytes/1024/1024 as v_size from dba_data_files
where tablespace_name=v_ts_nm)
loop
dbms_output.put_line(””||v_file.file_name||””||’ size ‘||v_file.v_size||’m’);
v_ct:=v_ct+1;
if v_ct
4. The statements produced can now be executed against another database to recreate this tablespace structure. NOTE: The directory structure on the destination server may be different from this system, so edit the physical structure of the datafiles if necessary.
Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com