Re: Rollback segment and Tablespace questions

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 19 Aug 2002 18:09:21 -0700
Message-ID: <92eeeff0.0208191709.1ea4f8a6@posting.google.com>

"David" <davidlee_at_diamond.com> wrote in message news:<ajqqrc$klq2_at_imsp212.netvigator.com>...
> hi all, I have two questions about Oracle (oracle8i 8.1.7)
>
> i) how can I find the rollback segment is public or private (from which data
> dictionary)?
> ii) I found (from Oracle Metalink, Note:1020180.6) that I can create "CREATE
> TABLESPACE" SQL statement by using cursors. Is it possible to generate the
> "CREATE TABLESPACE" SQL statement by using one SQL statement (without using
> cursors)? The difficult part is how to join dba_tablespaces and
> dba_data_files into one statement if the tablespace has more than 1 data
> file.
>
> David

select segment_name, owner from dba_rollback_segs; If owner is SYS then it is private and if owner is PUBLIC then it is public.

2)
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET HEADING OFF
SPOOL CreateTablespace.sql
SELECT 'CREATE TABLESPACE ' || a.tablespace_name ||

' DATAFILE ' || CHR(39) || b.file_name || CHR(39) || ' SIZE ' || b.bytes || ' REUSE' ||

       ' DEFAULT STORAGE (INITIAL ' || a.initial_extent ||
       ' NEXT ' || a.next_extent ||
       ' MINEXTENTS ' || a.min_extents ||
       ' MAXEXTENTS ' || a.max_extents ||
       ' PCTINCREASE ' || a.pct_increase || ') ' || a.status || ';'

FROM sys.dba_tablespaces a, sys.dba_data_files b WHERE a.tablespace_name != 'SYSTEM'
AND a.tablespace_name = b.tablespace_name; SPOOL OFF //Rauf Sarwar Received on Mon Aug 19 2002 - 20:09:21 CDT