Re: SQL*Plus Question

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: