Priscila, você quem fez este script ou pegou no metalink?
Case sim, você pode me passar o ID?
Att,
Welvis Douglas
-Mensagem original-
De: oracle_br@yahoogrupos.com.br [mailto:oracle...@yahoogrupos.com.br] Em
nome de Priscila Viana
Enviada em: terça-feira, 25 de maio de 2010 16:48
Para: oracle_br@yahoogrupos.com.br
Assunto: Re: [oracle_br] RES: Script para Criar TableSpaces
Cola esse script dentro de um txt conecta como dba na base que quer extrair
os scripts de criação,roda com @caminho/script.txt
Obs: remova o reuse da cada uma.
--Description:This script generates another with the reverse engineer
code for
--all tablespaces in the database. For Oracle database versions :
7.x/8.x/9.x
--Code:
set verify off
set feedback off
set echo off
set long 5000
set pagesize 0
set head off
set lines 1000
set termout on
set trimspool on
set serveroutput on
clear columns
spool c:\create_tablespaces.sql
DECLARE
CURSOR c_df (tbs_name VARCHAR2) IS
SELECT a.file_name,
a.tablespace_name,
a.bytes,
b.maxextend,
b.inc
FROM dba_data_files a,
sys.filext$ b
WHERE a.tablespace_name = tbs_name
AND a.file_id = b.file# (+);
/* dba_tablespaces columns */
v_tbsname VARCHAR2(30);
v_blksize NUMBER;
v_initial NUMBER;
v_next NUMBER;
v_minext NUMBER;
v_maxext NUMBER;
v_pctinc NUMBER;
v_extlen NUMBER;
v_status VARCHAR2(9);
v_contents VARCHAR2(9);
v_logging VARCHAR2(9);
v_flogging VARCHAR2(3);
v_extman VARCHAR2(10);
v_alloc VARCHAR2(9);
v_plugged VARCHAR2(3);
v_segman VARCHAR2(6);
v_namevarchar2(30);
cur INTEGER;
rec_tbs dba_tablespaces%ROWTYPE;
v_bs INTEGER;
v_ltt BOOLEAN := FALSE;
v_version VARCHAR2(10);
l_str VARCHAR2(10);
m_str VARCHAR2(20);
sSQLt VARCHAR2(1000);
v_return INTEGER;
BEGIN
DBMS_OUTPUT.ENABLE (100);
SELECT value
INTO v_bs
FROM v$parameter
WHERE name = 'db_block_size';
SELECT version
INTO v_version
FROM v$instance;
IF SUBSTR(v_version, 1, 1) = '7' THEN
sSQLt := 'SELECT
TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCRE
ASE,STATUS
FROM DBA_TABLESPACES WHERE TABLESPACE_NAME ' || 'SYSTEM' ;
ELSIF SUBSTR(v_version, 1, 3) = '8.0' THEN
sSQLt := 'SELECT
TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCRE
ASE,STATUS,MIN_EXTLEN,CONTENTS,LOGGING
FROM DBA_TABLESPACES WHERE TABLESPACE_NAME ''SYSTEM''';
ELSIF SUBSTR(v_version, 1, 3) = '8.1' THEN
sSQLt := 'SELECT TABLESPACE_NAME
INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE,STATUS,MIN_E
XTLEN,CONTENTS,LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE,
PLUGGED_IN FROM DBA_TABLESPACES WHERE TABLESPACE_NAME ''SYSTEM''';
ELSIF SUBSTR(v_version, 1, 3) = '9.0' THEN
sSQLt := 'SELECT
TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCRE
ASE,STATUS,MIN_EXTLEN,CONTENTS,LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE,PLU
GGED_IN,BLOCK_SIZE,SEGMENT_SPACE_MANAGEMENT
FROM DBA_TABLESPACES WHERE TABLESPACE_NAME ''SYSTEM''';
ELSE
sSQLt := 'SELECT
TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCRE
ASE,STATUS,MIN_EXTLEN,CONTENTS,LOGGING,EXTENT_MANAGEMENT,ALLOCATION_TYPE,PLU
GGED_IN,BLOCK_SIZE,SEGMENT_SPACE_MANAGEMENT,FORCE_LOGGING
FROM DBA_TABLESPACES WHERE TABLESPACE_NAME ''SYSTEM''';
END IF;
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, sSQLt, DBMS_SQL.native);
--DBMS_SQL.BIND_VARIABLE(cur, ':id1', v_name);
DBMS_SQL.DEFINE_COLUMN(cur, 1, v_tbsname, 30);
DBMS_SQL.DEFINE_COLUMN(cur, 2, v_initial);
DBMS_SQL.DEFINE_COLUMN(cur, 3, v_next);
DBMS_SQL.DEFINE_COLUMN(cur, 4, v_minext);
DBMS_SQL.DEFINE_COLUMN(cur, 5, v_maxext);
DBMS_SQL.DEFINE_COLUMN(cur, 6, v_pctinc);
DBMS_SQL.DEFINE_COLUMN(cur, 7, v_status, 9);
IF SUBSTR(v_version, 1, 3) = '8.0' THEN
BEGIN
DBMS_SQL.DEFINE_COLUMN(cur, 8, v_extlen);
DBMS_SQL.DEFINE_COLUMN(cur, 9, v_contents, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 10, v_logging, 9);
END;
ELSIF SUBSTR(v_version, 1, 3) = '8.1' THEN
BEGIN
DBMS_SQL.DEFINE_COLUMN(cur, 8, v_extlen);
DBMS_SQL.DEFINE_COLUMN(cur, 9, v_contents, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 10, v_logging, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 11, v_extman, 10);
DBMS_SQL.DEFINE_COLUMN(cur, 12, v_alloc, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 13, v_plugged, 3);
END;
ELSIF SUBSTR(v_version, 1, 3) = '9.0' THEN
BEGIN
DBMS_SQL.DEFINE_COLUMN(cur, 8, v_extlen);
DBMS_SQL.DEFINE_COLUMN(cur, 9, v_contents, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 10, v_logging, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 11, v_extman, 10);
DBMS_SQL.DEFINE_COLUMN(cur, 12, v_alloc, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 13, v_plugged, 3);
DBMS_SQL.DEFINE_COLUMN(cur, 14, v_blksize);
DBMS_SQL.DEFINE_COLUMN(cur, 15, v_segman, 9);
END;
ELSIF SUBSTR(v_version, 1, 3) = '9.2' THEN
BEGIN
DBMS_SQL.DEFINE_COLUMN(cur, 8, v_extlen);
DBMS_SQL.DEFINE_COLUMN(cur, 9, v_contents, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 10, v_logging, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 11, v_extman, 10);
DBMS_SQL.DEFINE_COLUMN(cur, 12, v_alloc, 9);
DBMS_SQL.DEFINE_COLUMN(cur, 13, v_plugged, 3);
DBMS_SQL.DEFINE_COLUMN(cur, 14, v_blksize);
DBMS_SQL.DEFINE_COLUMN(cur, 15, v_segman, 9);
