Re: SQL*Plus Question
- From: Jared Still <jkstill@xxxxxxxxx>
- To: oracledba.williams@xxxxxxxxx
- Date: Mon, 9 Feb 2009 12:58:24 -0800
On Mon, Feb 9, 2009 at 11:16 AM, Dennis Williams <
oracledba.williams@xxxxxxxxx> wrote:
> List,
>
> So, I'm trying to generate a list of CREATE TABLESPACE statements from
> DBA_DATA_FILES. I'm having trouble putting single quotes around the datafile
> name. This is frustrating because I seem to have forgotten how to do this. I
> thought there is a SQL function that would let you enter the hexadecimal
> value of a character you want printed.
>
> This is 10.2.0.4 on Solaris, but I don't think that matters.
>
Why not use dbms_metadata?
=================================================
set linesize 1000
set long 10000
col mydb noprint new_value mydb
set term off
select lower(name) mydb from v$database;
set term on
define sqlfile=_gen_tbs_&&mydb
-- dbms_metadata setup
begin
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',
TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECIFICATION',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY',TRUE);
dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',TRUE);
end;
/
spool &&sqlfile..sql
prompt set echo on
prompt spool &&sqlfile..log
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)
from dba_tablespaces
/
prompt spool off
prompt set echo off
spool off
=================================================
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
