Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: tablespaces ddl extract script

Re: tablespaces ddl extract script

From: Chirag DBA <chiragdba_at_gmail.com>
Date: Wed, 12 Oct 2005 12:07:51 -0400
Message-ID: <1a3629600510120907y34c3880drb1f64d8a50017fc4@mail.gmail.com>


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_at_gb.vodafone.co.uk> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Norman Dunbar
> Sent: 12 October 2005 11:49
> To: davidsharples_at_gmail.com; sjaffarhussain_at_gmail.com
> Cc: oracle-l_at_freelists.org
> 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_at_gmail.com> 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>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 12 2005 - 11:10:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US