try this re script...!! DECLARE CURSOR get_ts IS SELECT * FROM dba_tablespaces WHERE tablespace_name != 'SYSTEM'; CURSOR get_df (p_ts VARCHAR2) IS SELECT * from dba_data_files WHERE tablespace_name = p_ts; l_str VARCHAR2(10); BEGIN FOR ts_rec IN get_ts LOOP dbms_output.put_line ('CREATE TABLESPACE '||ts_rec.tablespace_name); -- For each tablespace loop through the datafiles FOR df_rec IN get_df (ts_rec.tablespace_name) LOOP IF get_df%ROWCOUNT = 1 THEN l_str := 'DATAFILE'; ELSE l_str := ','; END IF; dbms_output.put_line (l_str||' ' ||chr(39)||df_rec.file_name||chr(39) ||' SIZE '||df_rec.bytes||' REUSE '); if df_rec.autoextensible = 'YES' then dbms_output.put_line (' AUTOEXTEND ON' ||' NEXT '||df_rec.increment_by ); if df_rec.maxbytes = 68719443968 then dbms_output.put_line (' MAXSIZE UNLIMITED'); else dbms_output.put_line (' MAXSIZE '||df_rec.maxbytes); end if; end if; END LOOP; /* Extent Management Clause */ dbms_output.put_line ('EXTENT MANAGEMENT ' ||ts_rec.extent_management ); if ts_rec.extent_management = 'LOCAL' then if ts_rec.allocation_type = 'SYSTEM' then dbms_output.put_line (' AUTOALLOCATE '); else dbms_output.put_line (' UNIFORM SIZE '||ts_rec.initial_extent); end if; end if; if ts_rec.extent_management = 'DICTIONARY' then dbms_output.put_line ('DEFAULT STORAGE (INITIAL '||ts_rec.initial_extent ||' NEXT '||ts_rec.next_extent ||' MINEXTENTS '||ts_rec.min_extents ||' MAXEXTENTS '||ts_rec.max_extents ||' PCTINCREASE '||ts_rec.pct_increase||' ) '); end if; dbms_output.put_line (' ONLINE;'); dbms_output.new_line; END LOOP; END; / On 10/12/05, Hallas, John, Tech Dev <John.Hallas@xxxxxxxxxxxxxxxxx> wrote: > > Perform a set long 2000 and then try again > > Set long 2000 > > SELECT DBMS_METADATA.GET_DDL('TABLESPACE', tablespace_name) FROM > DBA_tablespaces; > > CREATE TABLESPACE "SYSTEM" DATAFILE > '/u01/oradata/WLINT1DB/system_01.dbf' SIZE 314572800 REUSE , > '/u02/oradata/WLINT1DB/system_02.dbf' SIZE 314572800 REUSE > LOGGING ONLINE PERMANENT BLOCKSIZE 8192 > EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL > > > > John > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Norman Dunbar > Sent: 12 October 2005 11:49 > To: davidsharples@xxxxxxxxx; sjaffarhussain@xxxxxxxxx > Cc: oracle-l@xxxxxxxxxxxxx > Subject: Re: tablespaces ddl extract script > > > Unfortunately, on my 9.2.0.4 <http://9.2.0.4> system, it is incomplete : > > SQL>select dbms_metadata.get_ddl('TABLESPACE','NORMAN') as fred from > dual; > > FRED > ---------------------------------------------------------- > > CREATE TABLESPACE "NORMAN" DATAFILE > '/u00/oracle/oradata/lccsdev/norman_01 > > Which is a tad upsetting :o( > > > I would suggest doing a full export with no actual rows etc being > exported, then run an import with SHOW=YES LOGFILE=SHOW.LOG and > extracting the commands from SHOW.LOG if the metadata thing doesn't work > for your system either. > > > Cheers, > Norman. > > > Norman Dunbar. > Contract Oracle DBA. > Rivers House, Leeds. > > Internal : 7 28 2051 > External : 0113 231 2051 > > > >>> David Sharples <davidsharples@xxxxxxxxx> 10/12/05 11:29am >>> > SQL> select dbms_metadata.get_ddl('TABLESPACE','TS_NAME') from dual; > google and metalink are your friends > > > > Information in this message may be confidential and may be legally > privileged. If you have received this message by mistake, please notify > the sender immediately, delete it and do not copy it to anyone else. > > We have checked this email and its attachments for viruses. But you > should still check any attachment before opening it. > > We may have to make this message and any reply to it public if asked to > under the Freedom of Information Act, Data Protection Act or for > litigation. Email messages and attachments sent to or from any > Environment Agency address may also be accessed by someone other than > the sender or recipient, for business purposes. > > If we have sent you information and you wish to use it please read our > terms and conditions which you can get by calling us on 08708 506 506. > Find out more about the Environment Agency at > www.environment-agency.gov.uk <http://www.environment-agency.gov.uk> > -- > //www.freelists.org/webpage/oracle-l > > > -- > //www.freelists.org/webpage/oracle-l >