Usando o pacote DBMS_METADATA no SQL*Plus
-- Verificando algumas das funções que utilizarei para geração dos
comandos DDL's
SCOTT> desc dbms_metadata;
FUNCTION GET_DDL RETURNS CLOB
Nome do Argumento Tipo In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME VARCHAR2 IN
SCHEMA VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT
FUNCTION GET_DEPENDENT_DDL RETURNS CLOB
Nome do Argumento Tipo In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
BASE_OBJECT_NAME VARCHAR2 IN
BASE_OBJECT_SCHEMA VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT
OBJECT_COUNT NUMBER IN DEFAULT
FUNCTION GET_GRANTED_DDL RETURNS CLOB
Nome do Argumento Tipo In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
GRANTEE VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT
OBJECT_COUNT NUMBER IN DEFAULT
-- Configurando o ambiente
SCOTT> set linesize 1000
SCOTT> set pagesize 1000
SCOTT> set long 9999999
-- Adicionando um terminador SQL (; ou /) para cada sentença DDL gerada
SCOTT> exec dbms_metadata.set_transform_param(
dbms_metadata.session_transform,'SQLTERMINATOR',true);
Procedimento PL/SQL concluído com sucesso.
-- Suprimindo qualquer informação de atributos de armazenamento de segmentos
SCOTT> exec dbms_metadata.set_transform_param(
dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
Procedimento PL/SQL concluído com sucesso.
-- Gerando DDL para a tabela T1
SCOTT> select dbms_metadata.get_ddl('TABLE','T1') "DDL TABLE" from dual;
DDL TABLE
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100),
CONSTRAINT "PK_T1" PRIMARY KEY ("ID") ENABLE
);
-- Gerando DDL para a view VIEW_T1_NOME
SCOTT> select dbms_metadata.get_ddl('VIEW','VIEW_T1_NOME') "DDL VIEW" from dual;
DDL VIEW
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SCOTT"."VIEW_T1_NOME" ("NOME") AS
SELECT NOME FROM T1;
-- Gerando DDL apenas para a chave estrangeira definida na tabela T2
SCOTT> select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','T2') "DDL FK" from dual;
DDL FK
--------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."T2" ADD CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REFERENCES "SCOTT"."T1" ("ID") ENABLE;
-- Gerando DDL para restrições do tipo (PK/UK/CHK) existentes definidas em T1
SCOTT> select dbms_metadata.get_dependent_ddl('CONSTRAINT','T1') "DDL PK/UK/CHK" from dual;
DDL PK/UK/CHK
--------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID") ENABLE;
-- Gerando o comando DDL para qualquer gatilho existente para a tabela T1
SCOTT> select dbms_metadata.get_dependent_ddl('TRIGGER','T1') "DDL TRIGGER" from dual;
DDL TRIGGER
--------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "SCOTT"."TRG_BI_T1"
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
:NEW.ID := DBMS_RANDOM.RANDOM;
END;
/
ALTER TRIGGER "SCOTT"."TRG_BI_T1" ENABLE;
-- Gerando comando DDL para qualquer índice existente para a tabela T1
SCOTT> select dbms_metadata.get_dependent_ddl('INDEX','T1') "DDL ÍNDICE" from dual;
DDL ÍNDICE
--------------------------------------------------------------------------------
CREATE INDEX "SCOTT"."I_T1_NOME" ON "SCOTT"."T1" ("NOME");
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "SCOTT"."T1" ("ID");
-- Gerando DDL para a view materializada
SCOTT> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MVIEW_T1') "DDL MVIEW" from dual;
DDL MVIEW
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "SCOTT"."MVIEW_T1"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT sysdate + 1/1440
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "T1"."ID" "ID","T1"."NOME" "NOME" FROM "T1" "T1";
-- Exemplo para geração de comandos DDL's para todas as tabelas existentes
SCOTT> select dbms_metadata.get_ddl(object_type, object_name) ddl
2 from user_objects
3 where object_type = 'TABLE';
DDL
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."MVIEW_T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100),
CONSTRAINT "PK_T11" PRIMARY KEY ("ID") ENABLE
);
CREATE TABLE "SCOTT"."T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100),
CONSTRAINT "PK_T1" PRIMARY KEY ("ID") ENABLE
);
CREATE TABLE "SCOTT"."T2"
( "ID" NUMBER,
CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REFERENCES "SCOTT"."T1" ("ID") ENABLE
);
SCOTT> desc dbms_metadata;
FUNCTION GET_DDL RETURNS CLOB
Nome do Argumento Tipo In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME VARCHAR2 IN
SCHEMA VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT
FUNCTION GET_DEPENDENT_DDL RETURNS CLOB
Nome do Argumento Tipo In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
BASE_OBJECT_NAME VARCHAR2 IN
BASE_OBJECT_SCHEMA VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT
OBJECT_COUNT NUMBER IN DEFAULT
FUNCTION GET_GRANTED_DDL RETURNS CLOB
Nome do Argumento Tipo In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
GRANTEE VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT
OBJECT_COUNT NUMBER IN DEFAULT
-- Configurando o ambiente
SCOTT> set linesize 1000
SCOTT> set pagesize 1000
SCOTT> set long 9999999
-- Adicionando um terminador SQL (; ou /) para cada sentença DDL gerada
SCOTT> exec dbms_metadata.set_transform_param(
dbms_metadata.session_transform,'SQLTERMINATOR',true);
Procedimento PL/SQL concluído com sucesso.
-- Suprimindo qualquer informação de atributos de armazenamento de segmentos
SCOTT> exec dbms_metadata.set_transform_param(
dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
Procedimento PL/SQL concluído com sucesso.
-- Gerando DDL para a tabela T1
SCOTT> select dbms_metadata.get_ddl('TABLE','T1') "DDL TABLE" from dual;
DDL TABLE
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100),
CONSTRAINT "PK_T1" PRIMARY KEY ("ID") ENABLE
);
-- Gerando DDL para a view VIEW_T1_NOME
SCOTT> select dbms_metadata.get_ddl('VIEW','VIEW_T1_NOME') "DDL VIEW" from dual;
DDL VIEW
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "SCOTT"."VIEW_T1_NOME" ("NOME") AS
SELECT NOME FROM T1;
-- Gerando DDL apenas para a chave estrangeira definida na tabela T2
SCOTT> select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','T2') "DDL FK" from dual;
DDL FK
--------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."T2" ADD CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REFERENCES "SCOTT"."T1" ("ID") ENABLE;
-- Gerando DDL para restrições do tipo (PK/UK/CHK) existentes definidas em T1
SCOTT> select dbms_metadata.get_dependent_ddl('CONSTRAINT','T1') "DDL PK/UK/CHK" from dual;
DDL PK/UK/CHK
--------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID") ENABLE;
-- Gerando o comando DDL para qualquer gatilho existente para a tabela T1
SCOTT> select dbms_metadata.get_dependent_ddl('TRIGGER','T1') "DDL TRIGGER" from dual;
DDL TRIGGER
--------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER "SCOTT"."TRG_BI_T1"
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
:NEW.ID := DBMS_RANDOM.RANDOM;
END;
/
ALTER TRIGGER "SCOTT"."TRG_BI_T1" ENABLE;
-- Gerando comando DDL para qualquer índice existente para a tabela T1
SCOTT> select dbms_metadata.get_dependent_ddl('INDEX','T1') "DDL ÍNDICE" from dual;
DDL ÍNDICE
--------------------------------------------------------------------------------
CREATE INDEX "SCOTT"."I_T1_NOME" ON "SCOTT"."T1" ("NOME");
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "SCOTT"."T1" ("ID");
-- Gerando DDL para a view materializada
SCOTT> select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MVIEW_T1') "DDL MVIEW" from dual;
DDL MVIEW
--------------------------------------------------------------------------------
CREATE MATERIALIZED VIEW "SCOTT"."MVIEW_T1"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT sysdate + 1/1440
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "T1"."ID" "ID","T1"."NOME" "NOME" FROM "T1" "T1";
-- Exemplo para geração de comandos DDL's para todas as tabelas existentes
SCOTT> select dbms_metadata.get_ddl(object_type, object_name) ddl
2 from user_objects
3 where object_type = 'TABLE';
DDL
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."MVIEW_T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100),
CONSTRAINT "PK_T11" PRIMARY KEY ("ID") ENABLE
);
CREATE TABLE "SCOTT"."T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100),
CONSTRAINT "PK_T1" PRIMARY KEY ("ID") ENABLE
);
CREATE TABLE "SCOTT"."T2"
( "ID" NUMBER,
CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REFERENCES "SCOTT"."T1" ("ID") ENABLE
);
Apenas como demonstração,
irei gerar abaixo os comandos DDL's necessários para concessão de privilégios,
criação do usuário, role e tablespace:
SCOTT> connect / as sysdba
Conectado.
SYS> set linesize 1000
SYS> set pagesize 1000
SYS> set long 9999999
SYS> exec dbms_metadata.set_transform_param(
dbms_metadata.session_transform,'SQLTERMINATOR',true);
Procedimento PL/SQL concluído com sucesso.
SYS> exec dbms_metadata.set_transform_param(
dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
Procedimento PL/SQL concluído com sucesso.
-- Gerando DDL para criação da tablespace USERS
SYS> select dbms_metadata.get_ddl('TABLESPACE','USERS') DDL from dual;
DDL
--------------------------------------------------------------------------------
CREATE TABLESPACE "USERS" DATAFILE
'C:\ORACLEXE\ORADATA\XE\USERS.DBF' SIZE 104857600
AUTOEXTEND ON NEXT 10485760 MAXSIZE 5120M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
ALTER DATABASE DATAFILE
'C:\ORACLEXE\ORADATA\XE\USERS.DBF' RESIZE 3145728000;
-- Gerando DDL para criação do usuário SCOTT
SYS> select dbms_metadata.get_ddl('USER','SCOTT') DDL from dual;
DDL
--------------------------------------------------------------------------------
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
-- Gerando DDL de privilégios de sistema concedidos ao usuário SCOTT
SYS> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','SCOTT') DDL from dual;
DDL
--------------------------------------------------------------------------------
GRANT CREATE MATERIALIZED VIEW TO "SCOTT";
GRANT CREATE VIEW TO "SCOTT";
GRANT UNLIMITED TABLESPACE TO "SCOTT";
-- Gerando DDL de roles conedidas ao usuário SCOTT
SYS> select dbms_metadata.get_granted_ddl('ROLE_GRANT','SCOTT') DDL from dual;
DDL
--------------------------------------------------------------------------------
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
-- Gerando DDL de privilégios de objetos que foram concedidos pelo usuário
-- SCOTT ao usuário ADAM
SYS> select dbms_metadata.get_granted_ddl('OBJECT_GRANT','ADAM') DDL from dual;
DDL
--------------------------------------------------------------------------------
GRANT SELECT ON "SCOTT"."T1" TO "ADAM";
GRANT UPDATE ON "SCOTT"."T1" TO "ADAM";
-- Gerando DDL de criação da role CONNECT
SYS> select dbms_metadata.get_ddl('ROLE','CONNECT') DDL from dual;
DDL
--------------------------------------------------------------------------------
CREATE ROLE "CONNECT";
Usando os utilitários exp/imp
-- exportando
as tabelas do schema SCOTT
C:\exp scott/tiger file=c:\scott grants=n statistics=none rows=n
Export: Release 10.2.0.1.0 - Production on Sex Jul 18 12:58:03 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Exportação executada no conjunto de caracteres de WE8PC850 e no conjunto de
caracteres de AL16UTF16 NCHAR o servidor usa WE8MSWIN1252 conjunto de caracteres
(conversão de conjunto de caracteres possível)
OBS: dados (linhas) da tabela não serão exportados
OBS: concessões em tabelas/views/seqüências/funções não serão exportadas
. exportando objetos e ações procedurais anteriores ao esquema
. exportando os nomes da biblioteca de função externa para usuário SCOTT
. exportando sinônimos do tipo PÚBLICO
. exportando sinônimos do tipo privado
. exportando definições de tipos de objeto para usuário SCOTT
Sobre exportar objetos de SCOTT ...
. exportando vínculos de banco de dados
. exportando números de seqüência
. exportando definições de cluster
. sobre exportar tabelas de SCOTT ... via Caminho Convencional ...
. . exportando tabela MVIEW_T1
. . exportando tabela T1
. . exportando tabela T2
. exportando sinônimos
. exportando views
. exportando procedimentos armazenados
. exportando operadores
. exportando restrições referenciais de integridade
. exportando gatilhos
. exportando tipos de índices
. exportando índices funcionais, extensíveis e de bitmap
. exportando ações contabilizáveis
. exportando views materializadas
. exportando logs de snapshot
. exportando filas de serviço
. exportando filhos e grupos de renovação
. exportando dimensões
. exportando objetos e ações procedurais posteriores ao esquema
. exportando estatística
Exportação encerrada com sucesso, sem advertências.
-- Gerando os comandos DDL's para arquivo texto
C:\>imp scott/tiger file=c:\scott indexfile=c:\ddl.sql
Import: Release 10.2.0.1.0 - Production on Sex Jul 18 12:59:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Arquivo de exportação criado por EXPORT:V10.02.01 via caminho convencional
importação realizada nos conjuntos de caracteres WE8PC850 e NCHAR AL16UTF16
o servidor de importação usa o conjunto de caracteres WE8MSWIN1252 (conversão
de charset possível)
. . saltando a tabela "MVIEW_T1"
. . saltando a tabela "T1"
. . saltando a tabela "T2"
Importação encerrada com sucesso, sem advertências.
-- Verificando os comandos DDL's gerados
C:\>type C:\ddl.sql
REM CREATE TABLE "SCOTT"."MVIEW_T1" ("ID" NUMBER, "NOME" VARCHAR2(100))
REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
REM FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
REM LOGGING NOCOMPRESS ;
CONNECT SCOTT;
CREATE UNIQUE INDEX "SCOTT"."PK_T11" ON "MVIEW_T1" ("ID" ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING;
REM ALTER TABLE "SCOTT"."MVIEW_T1" ADD CONSTRAINT "PK_T11" PRIMARY KEY
REM ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
REM 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM "USERS" LOGGING ENABLE;
REM CREATE TABLE "SCOTT"."T1" ("ID" NUMBER, "NOME" VARCHAR2(100)) PCTFREE
REM 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS
REM 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
REM NOCOMPRESS;
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "T1" ("ID" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING;
CREATE INDEX "SCOTT"."I_T1_NOME" ON "T1" ("NOME" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING;
REM ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID")
REM USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536
REM FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
REM LOGGING ENABLE;
REM CREATE TABLE "SCOTT"."T2" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS;
REM ALTER TABLE "SCOTT"."T2" ADD CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REM REFERENCES "T1" ("ID") ENABLE NOVALIDATE ;
REM ALTER TABLE "SCOTT"."T2" ENABLE CONSTRAINT "FK_T2_T1";
C:\exp scott/tiger file=c:\scott grants=n statistics=none rows=n
Export: Release 10.2.0.1.0 - Production on Sex Jul 18 12:58:03 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Exportação executada no conjunto de caracteres de WE8PC850 e no conjunto de
caracteres de AL16UTF16 NCHAR o servidor usa WE8MSWIN1252 conjunto de caracteres
(conversão de conjunto de caracteres possível)
OBS: dados (linhas) da tabela não serão exportados
OBS: concessões em tabelas/views/seqüências/funções não serão exportadas
. exportando objetos e ações procedurais anteriores ao esquema
. exportando os nomes da biblioteca de função externa para usuário SCOTT
. exportando sinônimos do tipo PÚBLICO
. exportando sinônimos do tipo privado
. exportando definições de tipos de objeto para usuário SCOTT
Sobre exportar objetos de SCOTT ...
. exportando vínculos de banco de dados
. exportando números de seqüência
. exportando definições de cluster
. sobre exportar tabelas de SCOTT ... via Caminho Convencional ...
. . exportando tabela MVIEW_T1
. . exportando tabela T1
. . exportando tabela T2
. exportando sinônimos
. exportando views
. exportando procedimentos armazenados
. exportando operadores
. exportando restrições referenciais de integridade
. exportando gatilhos
. exportando tipos de índices
. exportando índices funcionais, extensíveis e de bitmap
. exportando ações contabilizáveis
. exportando views materializadas
. exportando logs de snapshot
. exportando filas de serviço
. exportando filhos e grupos de renovação
. exportando dimensões
. exportando objetos e ações procedurais posteriores ao esquema
. exportando estatística
Exportação encerrada com sucesso, sem advertências.
-- Gerando os comandos DDL's para arquivo texto
C:\>imp scott/tiger file=c:\scott indexfile=c:\ddl.sql
Import: Release 10.2.0.1.0 - Production on Sex Jul 18 12:59:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Arquivo de exportação criado por EXPORT:V10.02.01 via caminho convencional
importação realizada nos conjuntos de caracteres WE8PC850 e NCHAR AL16UTF16
o servidor de importação usa o conjunto de caracteres WE8MSWIN1252 (conversão
de charset possível)
. . saltando a tabela "MVIEW_T1"
. . saltando a tabela "T1"
. . saltando a tabela "T2"
Importação encerrada com sucesso, sem advertências.
-- Verificando os comandos DDL's gerados
C:\>type C:\ddl.sql
REM CREATE TABLE "SCOTT"."MVIEW_T1" ("ID" NUMBER, "NOME" VARCHAR2(100))
REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
REM FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
REM LOGGING NOCOMPRESS ;
CONNECT SCOTT;
CREATE UNIQUE INDEX "SCOTT"."PK_T11" ON "MVIEW_T1" ("ID" ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING;
REM ALTER TABLE "SCOTT"."MVIEW_T1" ADD CONSTRAINT "PK_T11" PRIMARY KEY
REM ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
REM 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM "USERS" LOGGING ENABLE;
REM CREATE TABLE "SCOTT"."T1" ("ID" NUMBER, "NOME" VARCHAR2(100)) PCTFREE
REM 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS
REM 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING
REM NOCOMPRESS;
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "T1" ("ID" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING;
CREATE INDEX "SCOTT"."I_T1_NOME" ON "T1" ("NOME" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING;
REM ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID")
REM USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536
REM FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
REM LOGGING ENABLE;
REM CREATE TABLE "SCOTT"."T2" ("ID" NUMBER) PCTFREE 10 PCTUSED 40
REM INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS;
REM ALTER TABLE "SCOTT"."T2" ADD CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REM REFERENCES "T1" ("ID") ENABLE NOVALIDATE ;
REM ALTER TABLE "SCOTT"."T2" ENABLE CONSTRAINT "FK_T2_T1";
Usando os utilitários expdp/impdp (Datapump 10g)
-- Realizando a exportação apenas dos metadados
C:\>expdp scott/tiger directory=data_pump_dir dumpfile=scott content=metadata_only
Export: Release 10.2.0.1.0 - Production on Sexta-Feira, 18 Julho, 2008 13:32:50
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Iniciando "SCOTT"."SYS_EXPORT_SCHEMA_01":
scott/******** directory=data_pump_dir dumpfile=scott content=metadata_only
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TRIGGER
Processando o tipo de objeto SCHEMA_EXPORT/MATERIALIZED_VIEW
Processando o tipo de objeto SCHEMA_EXPORT/JOB
Tabela-mestre "SCOTT"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SCOTT.SYS_EXPORT_SCHEMA_01 é:
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\DPDUMP\SCOTT.DMP
O job "SCOTT"."SYS_EXPORT_SCHEMA_01" foi concluído com sucesso em 13:33:06
-- Gerando os comandos DDL's para arquivo texto
C:\>impdp scott/tiger directory=data_pump_dir dumpfile=scott sqlfile=ddl.sql
Import: Release 10.2.0.1.0 - Production on Sexta-Feira, 18 Julho, 2008 13:34:24
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-39154: Objetos de esquemas estrangeiros foram removidos da importação
Tabela-mestre "SCOTT"."SYS_SQL_FILE_FULL_01" carregada/descarregada com sucesso
Iniciando "SCOTT"."SYS_SQL_FILE_FULL_01":
scott/******** directory=data_pump_dir dumpfile=scott sqlfile=ddl.sql
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TRIGGER
Processando o tipo de objeto SCHEMA_EXPORT/MATERIALIZED_VIEW
O job "SCOTT"."SYS_SQL_FILE_FULL_01" foi concluído com sucesso em 13:34:28
-- Verificando os comandos DDL's gerados
C:\>type ddl.sql
-- CONNECT SCOTT
-- SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT
('USERENV','CURRENT_SCHEMA'),
export_db_name=>'XE', inst_scn=>'372450');
COMMIT;
END;
/
-- SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";
CREATE TABLE "SCOTT"."T2"
( "ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";
CREATE TABLE "SCOTT"."MVIEW_T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";
-- SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT SELECT ON "SCOTT"."T1" TO "ADAM";
GRANT UPDATE ON "SCOTT"."T1" TO "ADAM";
-- SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "SCOTT"."T1" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1;
ALTER INDEX "SCOTT"."PK_T1" NOPARALLEL;
CREATE INDEX "SCOTT"."I_T1_NOME" ON "SCOTT"."T1" ("NOME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1;
ALTER INDEX "SCOTT"."I_T1_NOME" NOPARALLEL;
CREATE UNIQUE INDEX "SCOTT"."PK_T11" ON "SCOTT"."MVIEW_T1" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1;
ALTER INDEX "SCOTT"."PK_T11" NOPARALLEL;
-- SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE;
ALTER TABLE "SCOTT"."MVIEW_T1" ADD CONSTRAINT "PK_T11" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE;
-- SCHEMA_EXPORT/TABLE/COMMENT
COMMENT ON MATERIALIZED VIEW "SCOTT"."MVIEW_T1" IS
'snapshot table for snapshot SCOTT.MVIEW_T1';
-- SCHEMA_EXPORT/VIEW/VIEW
CREATE FORCE VIEW "SCOTT"."VIEW_T1_NOME" ("NOME") AS
SELECT NOME FROM T1;
-- SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ALTER TABLE "SCOTT"."T2" ADD CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REFERENCES "SCOTT"."T1" ("ID") ENABLE;
-- SCHEMA_EXPORT/TABLE/TRIGGER
CREATE TRIGGER "SCOTT"."TRG_BI_T1"
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
:NEW.ID := DBMS_RANDOM.RANDOM;
END;
/
ALTER TRIGGER "SCOTT"."TRG_BI_T1" ENABLE;
ALTER TRIGGER "SCOTT"."TRG_BI_T1"
COMPILE
PLSQL_OPTIMIZE_LEVEL = 2
PLSQL_CODE_TYPE= INTERPRETED;
-- SCHEMA_EXPORT/MATERIALIZED_VIEW
CREATE MATERIALIZED VIEW "SCOTT"."MVIEW_T1" USING
("MVIEW_T1", (8, 'XE', 1, 0, 0, "SCOTT", "T1", '2008-07-18 13:32:43', 0, 14796,
'1950-01-01 12:00:00', '', 0, 372263, 0, NULL, (1, "ID", "ID", 0, 321, 0)),
2097249, 8, ('1950-01-01 12:00:00', 4, 0, 0, 372263, 0, 0, 2, NULL, NULL))
REFRESH FORCE WITH PRIMARY KEY AS
SELECT "T1"."ID" "ID","T1"."NOME" "NOME" FROM "T1" "T1";
ALTER MATERIALIZED VIEW "SCOTT"."MVIEW_T1" COMPILE;
C:\>expdp scott/tiger directory=data_pump_dir dumpfile=scott content=metadata_only
Export: Release 10.2.0.1.0 - Production on Sexta-Feira, 18 Julho, 2008 13:32:50
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Iniciando "SCOTT"."SYS_EXPORT_SCHEMA_01":
scott/******** directory=data_pump_dir dumpfile=scott content=metadata_only
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TRIGGER
Processando o tipo de objeto SCHEMA_EXPORT/MATERIALIZED_VIEW
Processando o tipo de objeto SCHEMA_EXPORT/JOB
Tabela-mestre "SCOTT"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SCOTT.SYS_EXPORT_SCHEMA_01 é:
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\DPDUMP\SCOTT.DMP
O job "SCOTT"."SYS_EXPORT_SCHEMA_01" foi concluído com sucesso em 13:33:06
-- Gerando os comandos DDL's para arquivo texto
C:\>impdp scott/tiger directory=data_pump_dir dumpfile=scott sqlfile=ddl.sql
Import: Release 10.2.0.1.0 - Production on Sexta-Feira, 18 Julho, 2008 13:34:24
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-39154: Objetos de esquemas estrangeiros foram removidos da importação
Tabela-mestre "SCOTT"."SYS_SQL_FILE_FULL_01" carregada/descarregada com sucesso
Iniciando "SCOTT"."SYS_SQL_FILE_FULL_01":
scott/******** directory=data_pump_dir dumpfile=scott sqlfile=ddl.sql
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TRIGGER
Processando o tipo de objeto SCHEMA_EXPORT/MATERIALIZED_VIEW
O job "SCOTT"."SYS_SQL_FILE_FULL_01" foi concluído com sucesso em 13:34:28
-- Verificando os comandos DDL's gerados
C:\>type ddl.sql
-- CONNECT SCOTT
-- SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT
('USERENV','CURRENT_SCHEMA'),
export_db_name=>'XE', inst_scn=>'372450');
COMMIT;
END;
/
-- SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";
CREATE TABLE "SCOTT"."T2"
( "ID" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";
CREATE TABLE "SCOTT"."MVIEW_T1"
( "ID" NUMBER,
"NOME" VARCHAR2(100)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS";
-- SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
GRANT SELECT ON "SCOTT"."T1" TO "ADAM";
GRANT UPDATE ON "SCOTT"."T1" TO "ADAM";
-- SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "SCOTT"."PK_T1" ON "SCOTT"."T1" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1;
ALTER INDEX "SCOTT"."PK_T1" NOPARALLEL;
CREATE INDEX "SCOTT"."I_T1_NOME" ON "SCOTT"."T1" ("NOME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1;
ALTER INDEX "SCOTT"."I_T1_NOME" NOPARALLEL;
CREATE UNIQUE INDEX "SCOTT"."PK_T11" ON "SCOTT"."MVIEW_T1" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1;
ALTER INDEX "SCOTT"."PK_T11" NOPARALLEL;
-- SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "SCOTT"."T1" ADD CONSTRAINT "PK_T1" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE;
ALTER TABLE "SCOTT"."MVIEW_T1" ADD CONSTRAINT "PK_T11" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE;
-- SCHEMA_EXPORT/TABLE/COMMENT
COMMENT ON MATERIALIZED VIEW "SCOTT"."MVIEW_T1" IS
'snapshot table for snapshot SCOTT.MVIEW_T1';
-- SCHEMA_EXPORT/VIEW/VIEW
CREATE FORCE VIEW "SCOTT"."VIEW_T1_NOME" ("NOME") AS
SELECT NOME FROM T1;
-- SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ALTER TABLE "SCOTT"."T2" ADD CONSTRAINT "FK_T2_T1" FOREIGN KEY ("ID")
REFERENCES "SCOTT"."T1" ("ID") ENABLE;
-- SCHEMA_EXPORT/TABLE/TRIGGER
CREATE TRIGGER "SCOTT"."TRG_BI_T1"
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
:NEW.ID := DBMS_RANDOM.RANDOM;
END;
/
ALTER TRIGGER "SCOTT"."TRG_BI_T1" ENABLE;
ALTER TRIGGER "SCOTT"."TRG_BI_T1"
COMPILE
PLSQL_OPTIMIZE_LEVEL = 2
PLSQL_CODE_TYPE= INTERPRETED;
-- SCHEMA_EXPORT/MATERIALIZED_VIEW
CREATE MATERIALIZED VIEW "SCOTT"."MVIEW_T1" USING
("MVIEW_T1", (8, 'XE', 1, 0, 0, "SCOTT", "T1", '2008-07-18 13:32:43', 0, 14796,
'1950-01-01 12:00:00', '', 0, 372263, 0, NULL, (1, "ID", "ID", 0, 321, 0)),
2097249, 8, ('1950-01-01 12:00:00', 4, 0, 0, 372263, 0, 0, 2, NULL, NULL))
REFRESH FORCE WITH PRIMARY KEY AS
SELECT "T1"."ID" "ID","T1"."NOME" "NOME" FROM "T1" "T1";
ALTER MATERIALIZED VIEW "SCOTT"."MVIEW_T1" COMPILE;
Nenhum comentário:
Postar um comentário