create table

[Fernando Almeida 0]

eu queria saber se existe algum comando que exporte todos os scripts de create das tabelas de uma base.caso exista seria de uma ajuda imensa para mim, uma vez que perdi boa parte da minha documentaçào e preiso refaze-la o mais rapido possivel, coisa que é acredito que manualmente será muito desgastante.grato.

[alphamek 2]

Tu pode tentar fazer um index file, exemplo:

 

C:\>exp system file=meu_dmp.dmp log=meu_dmp.log indexfile=meus_scripts.sql full=y

Ou caso utilize versão 9i ou superior, utilizar o pacote DBMS_METADATA.GET_DDL, no site da iMasters existem materias de como utilizar esse pacote.

 

Abraços,

[Fernando Almeida 0]

alphamek, realmente o DBMS_METADATA.GET_DDL e'o que eu estou procurando, mas pelo que entendi so funciona para uma tabela de cada vez, e eu precisaria de varias tabelas.caso eu coloque 'TST%' no local da tabela ele ira me retornar todas as que tiverem TST no inicio do nome?

[alphamek 2]

Veja os exemplos que fiz abaixo, pode resolver seu problema.

 

SQL> conn [email="distspo@PEL_DIST_CD"]distspo@PEL_DIST_CD[/email]Informe a senha:Conectado.SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle9i Enterprise Edition Release 9.2.0.5.0 - ProductionPL/SQL Release 9.2.0.5.0 - ProductionCORE	9.2.0.6.0	   ProductionTNS for 32-bit Windows: Version 9.2.0.5.0 - ProductionNLSRTL Version 9.2.0.5.0 - ProductionSQL> set long 500000SQL> select 'select dbms_metadata.get_ddl (''TABLE'',''' || table_name || ''',''DISTSPO'') from dual;'  2  from user_tables;'SELECTDBMS_METADATA.GET_DDL(''TABLE'','''||TABLE_NAME||''',''DISTSPO'')FROMDUAL;'--------------------------------------------------------------------------------------------select dbms_metadata.get_ddl ('TABLE','CRF_LOG_SITE_CD_CMD','DISTSPO') from dual;select dbms_metadata.get_ddl ('TABLE','LOG_SITE_CD_CMD','DISTSPO') from dual;select dbms_metadata.get_ddl ('TABLE','MAR','DISTSPO') from dual;select dbms_metadata.get_ddl ('TABLE','PLAN_TABLE','DISTSPO') from dual;select dbms_metadata.get_ddl ('TABLE','TESTE','DISTSPO') from dual;SQL> spool c:\tst_imasters.sqlSQL> /'SELECTDBMS_METADATA.GET_DDL(''TABLE'','''||TABLE_NAME||''',''DISTSPO'')FROMDUAL;'--------------------------------------------------------------------------------------------select dbms_metadata.get_ddl ('TABLE','CRF_LOG_SITE_CD_CMD','DISTSPO') from dual;select dbms_metadata.get_ddl ('TABLE','LOG_SITE_CD_CMD','DISTSPO') from dual;select dbms_metadata.get_ddl ('TABLE','MAR','DISTSPO') from dual;select dbms_metadata.get_ddl ('TABLE','PLAN_TABLE','DISTSPO') from dual;select dbms_metadata.get_ddl ('TABLE','TESTE','DISTSPO') from dual;SQL> spool offSQL> @c:\tst_imastersSP2-0042: comando desconhecido "SQL> /" - restante da linha ignorado.SP2-0734: inφcio de comando desconhecido "'SELECTDBM..." - restante da linha ignorado.DBMS_METADATA.GET_DDL('TABLE','CRF_LOG_SITE_CD_CMD','DISTSPO')--------------------------------------------------------------------------------  CREATE TABLE "DISTSPO"."CRF_LOG_SITE_CD_CMD"   (	"IDLOG" NUMBER(10,0) NOT NULL ENABLE,		"IDORIGEM" NUMBER(10,0) NOT NULL ENABLE,		"IDLOGDEST" NUMBER(10,0) NOT NULL ENABLE,		"IDDESTINO" NUMBER(10,0) NOT NULL ENABLE,		"COMANDO" VARCHAR2(2000) NOT NULL ENABLE   ) 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 "DIST" DBMS_METADATA.GET_DDL('TABLE','LOG_SITE_CD_CMD','DISTSPO')--------------------------------------------------------------------------------  CREATE TABLE "DISTSPO"."LOG_SITE_CD_CMD"   (	"IDLOG" NUMBER(10,0) NOT NULL ENABLE,		"IDORIGEM" NUMBER(10,0) NOT NULL ENABLE,		"IDLOGDEST" NUMBER(10,0) NOT NULL ENABLE,		"IDDESTINO" NUMBER(10,0) NOT NULL ENABLE,		"COMANDO" VARCHAR2(2000) NOT NULL ENABLE,		 CONSTRAINT "LSCDC_PK" PRIMARY KEY ("IDLOG", "IDORIGEM", "IDLOGDEST", "IDDESTINO")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 20971520 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "DIST_IDX"  ENABLE   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 41943040 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "DIST" DBMS_METADATA.GET_DDL('TABLE','MAR','DISTSPO')--------------------------------------------------------------------------------  CREATE TABLE "DISTSPO"."MAR"   (	"ID" NUMBER(10,0),		"OPER" VARCHAR2(1)   ) 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 "DIST" DBMS_METADATA.GET_DDL('TABLE','PLAN_TABLE','DISTSPO')--------------------------------------------------------------------------------  CREATE TABLE "DISTSPO"."PLAN_TABLE"   (	"STATEMENT_ID" VARCHAR2(30),		"PLAN_ID" NUMBER,		"TIMESTAMP" DATE,		"REMARKS" VARCHAR2(4000),		"OPERATION" VARCHAR2(30),		"OPTIONS" VARCHAR2(255),		"OBJECT_NODE" VARCHAR2(128),		"OBJECT_OWNER" VARCHAR2(30),		"OBJECT_NAME" VARCHAR2(30),		"OBJECT_ALIAS" VARCHAR2(65),		"OBJECT_INSTANCE" NUMBER(*,0),		"OBJECT_TYPE" VARCHAR2(30),		"OPTIMIZER" VARCHAR2(255),		"SEARCH_COLUMNS" NUMBER,		"ID" NUMBER(*,0),		"PARENT_ID" NUMBER(*,0),		"DEPTH" NUMBER(*,0),		"POSITION" NUMBER(*,0),		"COST" NUMBER(*,0),		"CARDINALITY" NUMBER(*,0),		"BYTES" NUMBER(*,0),		"OTHER_TAG" VARCHAR2(255),		"PARTITION_START" VARCHAR2(255),		"PARTITION_STOP" VARCHAR2(255),		"PARTITION_ID" NUMBER(*,0),		"OTHER" LONG,		"DISTRIBUTION" VARCHAR2(30),		"CPU_COST" NUMBER(*,0),		"IO_COST" NUMBER(*,0),		"TEMP_SPACE" NUMBER(*,0),		"ACCESS_PREDICATES" VARCHAR2(4000),		"FILTER_PREDICATES" VARCHAR2(4000),		"PROJECTION" VARCHAR2(4000),		"TIME" NUMBER(*,0),		"QBLOCK_NAME" VARCHAR2(30)   ) 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 "DIST" DBMS_METADATA.GET_DDL('TABLE','TESTE','DISTSPO')--------------------------------------------------------------------------------  CREATE TABLE "DISTSPO"."TESTE"   (	"NUMERO" NUMBER,		"DATA" DATE   ) 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 "DIST" SP2-0734: inφcio de comando desconhecido "SQL> spool..." - restante da linha ignorado.SQL> @idHORA EXECUTADA-------------------11-10-2007 10:51:32INSTANCE_NAME	HOST_NAME														STATUS---------------- ---------------------------------------------------------------- ----------cdpe			 PELSPOS8C														OPENUSER Θ "DISTSPO"SQL>

Abraços, http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif