Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, ramesh.

Asked: February 15, 2001 - 11:04 am UTC

Answered by: Tom Kyte - Last updated: March 27, 2020 - 10:40 am UTC

Category: Database - Version: 8.0.5

Viewed 50K+ times! This question is

You Asked

I would like to extract DDL of indexes to store it outside the database. The purpose of this effort is to save the DDL to use it on fly when we need. I could use export/import tool but it requires editing when we want to run the text from SQL. I am planning to write a procedure to do this. Please advise.

and we said...

Don't write a procedure, use exp/imp.


exp userid=/ owner=some_schema
imp userid=/ indexfile=foo.sql


that'll get you the ddl in a format that requires no editing.

and you rated our response

  (182 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Script for extracting DDL

October 29, 2001 - 9:44 am UTC

Reviewer: Andre Nasser from Rio de Janeiro, Brazil

This is a script I coded to help me get the DDL for my database in order to carry out a "reverse engineering". The problem with EXP is that you cannot format the ouput. You are stuck to the .dmp file's format. If I want something different, I have to write the code myself. Since I did not want all clauses to show up at this time, I programmed this.

Please note that this is a very basic script that can HELP you develop your own DDL commands.

It works only for tables, PKs, FKs, indexes and sequences, because that is what I need now. You can add your own clauses and specific stuff, but I believe it's a good starting point.

------------------
-- Created by: Andre Whittick Nasser, OCP
-- Email: andre_nasser@yahoo.com.br
-- Website: www.geocities.com/andre_nasser
-- Date: Oct 24, 2001
-- Description: Reverse Engineer your existing schema objects
------------------

set termout off
set feedback off
set serveroutput on size 100000
spool schema.sql

begin
dbms_output.put_line('--');
dbms_output.put_line('-- DROP TABLES --');
dbms_output.put_line('--');
for rt in (select tname from tab order by tname) loop
dbms_output.put_line('DROP TABLE '||rt.tname||' CASCADE CONSTRAINTS;');
end loop;
end;
/

declare
v_tname varchar2(30);
v_cname char(32);
v_type char(20);
v_null varchar2(10);
v_maxcol number;
v_virg varchar2(1);
begin
dbms_output.put_line('--');
dbms_output.put_line('-- CREATE TABLES --');
dbms_output.put_line('--');
for rt in (select table_name from user_tables order by 1) loop
v_tname:=rt.table_name;
v_virg:=',';
dbms_output.put_line('CREATE TABLE '||v_tname||' (');
for rc in (select table_name,column_name,data_type,data_length,
data_precision,data_scale,nullable,column_id
from user_tab_columns tc
where tc.table_name=rt.table_name
order by table_name,column_id) loop
v_cname:=rc.column_name;
if rc.data_type='VARCHAR2' then
v_type:='VARCHAR2('||rc.data_length||')';
elsif rc.data_type='NUMBER' and rc.data_precision is null and
rc.data_scale=0 then
v_type:='INTEGER';
elsif rc.data_type='NUMBER' and rc.data_precision is null and
rc.data_scale is null then
v_type:='NUMBER';
elsif rc.data_type='NUMBER' and rc.data_scale='0' then
v_type:='NUMBER('||rc.data_precision||')';
elsif rc.data_type='NUMBER' and rc.data_scale<>'0' then
v_type:='NUMBER('||rc.data_precision||','||rc.data_scale||')';
elsif rc.data_type='CHAR' then
v_type:='CHAR('||rc.data_length||')';
else v_type:=rc.data_type;
end if;

if rc.nullable='Y' then
v_null:='NULL';
else
v_null:='NOT NULL';
end if;
select max(column_id)
into v_maxcol
from user_tab_columns c
where c.table_name=rt.table_name;
if rc.column_id=v_maxcol then
v_virg:='';
end if;
dbms_output.put_line (v_cname||v_type||v_null||v_virg);
end loop;
dbms_output.put_line(');');
end loop;
end;
/

declare
v_virg varchar2(1);
v_maxcol number;
begin
dbms_output.put_line('--');
dbms_output.put_line('-- PRIMARY KEYS --');
dbms_output.put_line('--');
for rcn in (select table_name,constraint_name
from user_constraints
where constraint_type='P'
order by table_name) loop
dbms_output.put_line ('ALTER TABLE '||rcn.table_name||' ADD (');
dbms_output.put_line ('CONSTRAINT '||rcn.constraint_name);
dbms_output.put_line ('PRIMARY KEY (');
v_virg:=',';
for rcl in (select column_name,position
from user_cons_columns cl
where cl.constraint_name=rcn.constraint_name
order by position) loop
select max(position)
into v_maxcol
from user_cons_columns c
where c.constraint_name=rcn.constraint_name;
if rcl.position=v_maxcol then
v_virg:='';
end if;
dbms_output.put_line (rcl.column_name||v_virg);
end loop;
dbms_output.put_line(')');
dbms_output.put_line('USING INDEX );');
end loop;
end;
/

declare
v_virg varchar2(1);
v_maxcol number;
v_tname varchar2(30);
begin
dbms_output.put_line('--');
dbms_output.put_line('-- FOREIGN KEYS --');
dbms_output.put_line('--');
for rcn in (select table_name,constraint_name,r_constraint_name
from user_constraints
where constraint_type='R'
order by table_name) loop
dbms_output.put_line ('ALTER TABLE '||rcn.table_name||' ADD (');
dbms_output.put_line ('CONSTRAINT '||rcn.constraint_name);
dbms_output.put_line ('FOREIGN KEY (');
v_virg:=',';
for rcl in (select column_name,position
from user_cons_columns cl
where cl.constraint_name=rcn.constraint_name
order by position) loop
select max(position)
into v_maxcol
from user_cons_columns c
where c.constraint_name=rcn.constraint_name;
if rcl.position=v_maxcol then
v_virg:='';
end if;
dbms_output.put_line (rcl.column_name||v_virg);
end loop;
select table_name
into v_tname
from user_constraints c
where c.constraint_name=rcn.r_constraint_name;
dbms_output.put_line(') REFERENCES '||v_tname||' (');

select max(position)
into v_maxcol
from user_cons_columns c
where c.constraint_name=rcn.r_constraint_name;
v_virg:=',';
select max(position)
into v_maxcol
from user_cons_columns c
where c.constraint_name=rcn.r_constraint_name;
for rcr in (select column_name,position
from user_cons_columns cl
where rcn.r_constraint_name=cl.constraint_name
order by position) loop
if rcr.position=v_maxcol then
v_virg:='';
end if;
dbms_output.put_line (rcr.column_name||v_virg);
end loop;
dbms_output.put_line(') );');
end loop;
end;
/

begin
dbms_output.put_line('--');
dbms_output.put_line('-- DROP SEQUENCES --');
dbms_output.put_line('--');
for rs in (select sequence_name
from user_sequences
where sequence_name like 'SQ%'
order by sequence_name) loop
dbms_output.put_line('DROP SEQUENCE '||rs.sequence_name||';');
end loop;
dbms_output.put_line('--');
dbms_output.put_line('-- CREATE SEQUENCES --');
dbms_output.put_line('--');
for rs in (select sequence_name
from user_sequences
where sequence_name like 'SQ%'
order by sequence_name) loop
dbms_output.put_line('CREATE SEQUENCE '||rs.sequence_name||' NOCYCLE;');
end loop;
end;
/

declare
v_virg varchar2(1);
v_maxcol number;
begin
dbms_output.put_line('--');
dbms_output.put_line('-- INDEXES --');
dbms_output.put_line('--');
for rid in (select index_name, table_name
from user_indexes
where index_name not in (select constraint_name from user_constraints)
and index_type<>'LOB'
order by index_name) loop
v_virg:=',';
dbms_output.put_line('CREATE INDEX '||rid.index_name||' ON '||rid.table_name||' (');
for rcl in (select column_name,column_position
from user_ind_columns cl
where cl.index_name=rid.index_name
order by column_position) loop
select max(column_position)
into v_maxcol
from user_ind_columns c
where c.index_name=rid.index_name;
if rcl.column_position=v_maxcol then
v_virg:='';
end if;
dbms_output.put_line (rcl.column_name||v_virg);
end loop;
dbms_output.put_line(');');
end loop;
end;
/

spool off
set feedback on
set termout on

-- End of script

Tom, please comment !


Tom Kyte

Followup  

October 29, 2001 - 10:07 am UTC

did you try exp/imp as I suggested?  For example, I just issued:

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x int, y int, z int, primary key(x,y) );

Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create index t_idx on t(z);

Index created.


and then did the exp/imp trick and have in my file system:

REM  CREATE TABLE "OPS$TKYTE"."T" ("X" NUMBER(*,0), "Y" NUMBER(*,0), "Z" 
REM  NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING 
REM  STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 1024 
REM  PCTINCREASE 10 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
REM  TABLESPACE "SYSTEM" ;
REM  ... 0 rows
CONNECT OPS$TKYTE;
CREATE INDEX "OPS$TKYTE"."T_IDX" ON "T" ("Z" ) PCTFREE 10 INITRANS 2 
MAXTRANS 255 STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 1024 
PCTINCREASE 10 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE "SYSTEM" LOGGING ;
REM  ALTER TABLE "OPS$TKYTE"."T" ADD PRIMARY KEY ("X", "Y") USING INDEX 
REM  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 16384 NEXT 16384 
REM  MINEXTENTS 1 MAXEXTENTS 1024 PCTINCREASE 10 FREELISTS 1 FREELIST 
REM  GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE ;

Now, all I need to do is get rid of lines that start with:

REM  ...
CONNECT 

and c/REM  // and I've got what I need (without any code).

My comments -- I don't like to write code unless I have to, exp/imp give me what I need in this case.
 

... continuing about the script above

October 29, 2001 - 11:32 am UTC

Reviewer: Andre Whittick Nasser from Rio de Janeiro, Brazil

Tom, the problem is there is too much "garbage" ganerated this way. As I stated, I would like to get the output MY OWN WAY. That is the BIG AND ONLY reason I wrote the code. Imagine a schema with lots of objects and clauses and I just want to get specific (in my case, basic) parts without any editing (imagine I continually have to send this output to my developing team, which is my case here). I tried EXP indeed, but it was too "chaotic" for my puposes.

Thanks again !

Codepages and downgrade

October 31, 2001 - 1:24 am UTC

Reviewer: Sergey Sugak from Russia

You can't just use exp/imp when you need to create schema copy from 8i DB into 7.1 Db for example. You can't use this method easy if your codepages are different for different databases. Besides this method doesn't give you the clear _text_ representation of your DDL.

Tom Kyte

Followup  

October 31, 2001 - 7:49 am UTC

Yes you can. You just use the 7.1 EXP tool against 8i. And since you don't care about the data (just getting DDL) character sets don't count.

Consider

$ exp userid=scott/tiger@ora817dev.us.oracle.com owner=scott rows=n

Export: Release 7.1.6.2.0 - Production on Wed Oct 31 07:47:25 2001

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
Note: table data (rows) will not be exported
About to export SCOTT's objects ...
. exporting snapshots
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables ...
. exporting table A
. exporting table BONUS
. exporting table C
. exporting table CHAINED_ROWS
. exporting table DEPT
. exporting table DUMMY
. exporting table EMP
. exporting table JAVA$CLASS$MD5$TABLE
. exporting table MLOG$_EMP
. exporting table P
. exporting table SALGRADE
. exporting table T
. exporting table T1
. exporting table T2
. exporting table TMP
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting referential integrity constraints
. exporting triggers
. exporting posttables actions
Export terminated successfully with warnings.

$ imp userid=scott/tiger full=y indexfile=foo.sql

Import: Release 7.1.6.2.0 - Production on Wed Oct 31 07:47:40 2001

Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.


Connected to: Oracle7 Server Release 7.1.6.2.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.1.6.2.0 - Production

Export file created by EXPORT:V07.01.06
Import terminated successfully without warnings.


$ head foo.sql

REM CREATE TABLE "SCOTT"."A" ("OWNER" VARCHAR2(30) NOT NULL,
REM "OBJECT_NAME" VARCHAR2(30) NOT NULL, "SUBOBJECT_NAME" VARCHAR2(30),
REM "OBJECT_ID" NUMBER NOT NULL, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE"
REM VARCHAR2(18), "CREATED" DATE NOT NULL, "LAST_DDL_TIME" DATE NOT NULL,
REM "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY"
REM VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1))
REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 PARALLEL ( DEGREE
REM DEFAULT) STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS
REM 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE

The foo.sql is clear _text_ representation. I didn't misunderstand anything here.....

IMP/EXP is good but...

August 08, 2002 - 11:41 pm UTC

Reviewer: Rajesh from md, USA

Tom and others,
I hate to write PL/SQL Code for extracting DDL when good tools like IMP/EXP exist, but I got into a situation where I have to use a "connect" account for conversion project, the "connect" account has synonyms to the owner account where the real tables exist.

I browsed thru metalink but could not find a way to grant the *connect* account to export / import the owner tables. I cannot also grant the EXP_FULL_DATABASE because of the other privileges that go with the role.

I cannot give out the owner account password to the conversion team.

The only option that i can think of is writing a PL/SQL
code to generate all the DDLs for constraints and indexes.

One more issue is the connect account should be able to
1)disable constraints, drop indexes
2)load the data
3)create the indexes and enable all disabled constraints
from the connect account.
I know i can write procedures in the owner account, which has dynamic sql to figure out the constraints and indexes
and do (1), (2) and (3) and grant exec privs to the connect account.

Any workaround for this situation Tom?
If otherwise could you provide me with generic scripts or point me to any if available?

Thanks.



Tom Kyte

Followup  

August 09, 2002 - 8:50 am UTC

I'm not understanding the problem here???

You have a "connect" account -- it owns NO DATA.

You have another account "ownerAccount" -- it has the real stuff.

You want to export "ownerAccount" apparently.

If the "connect" account is to do all of those things, the connect account should be the OWNER ACCOUNT.

IMP/EXP for extracting DDL from another user

August 09, 2002 - 10:32 am UTC

Reviewer: Rajesh from md usa

Tom,
Sorry to keep this thread going. I can post it as a separate question.

The only reason I am doing EXP/IMP from the connect account is to get the DDL & constraints of the owner account tables.

Here is a simple layout of our system:
--------------------------------------

Synonyms
Connect Account ----------> Owner Account
(Tables)


Is there any way at all to extract DDL and constraints of a table without being the owner of the table and not having DBA/EXP_FULL_DATABASE privileges ?

Unfortunately the *owner* account passwords cannot be given out to the team that runs a conversion project. They operate only on the connect account.

Sorry if my question is still not clear.



Tom Kyte

Followup  

August 09, 2002 - 10:54 am UTC

Then, as I said, you'll NEED to do it from the owner account.  Period.

The connect account HAS NO DDL to extract.

The owner account doesn't have to be given to a conversion team, just a DMP file generated by someone at some point does.  Seems simple enough to me (have a DBA to a one time export -- you have what you need)


In Oracle9i, you can also:

ops$tkyte@ORA920.US.ORACLE.COM> select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  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 "SYSTEM"  ENABLE,
         CONSTRAINT "EMP_FK_EMP" FOREIGN KEY ("MGR")
          REFERENCES "SCOTT"."EMP" ("EMPNO") ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") 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 "SYSTEM"


Note that I was ops$tkyte, not scott in this case. 

August 09, 2002 - 12:32 pm UTC

Reviewer: A reader

Tom, Can I create DBMS_METADATA in any version prior to Oracle 9i? If not why? After all it is only PL/SQL code.

Tom Kyte

Followup  

August 09, 2002 - 12:43 pm UTC

no, it is NOT just plsql code. It is lots of underlying support code in the database kernel itself.

Very nice, but....

August 11, 2002 - 6:04 pm UTC

Reviewer: TomazZ from Slovenia

when i try to test this:
SQL> select dbms_metadata.get_ddl('TABLE','BLA') from dual;

DBMS_METADATA.GET_DDL('TABLE','BLA')
-----------------------------------------------------------------

  CREATE TABLE "TOMAZZ"."BLA"
   (    "A" NUMBER,
        "B" VARCHAR2(30)
   ) PCTFREE


SQL> 

I get an invalid output script. Have I hit an issue or something? Do I need some special privileges? I am connected as owner, though.

Second question if I may: 
I was trying to get a create table script with querying the dba_tables with cursor expression on dba_tab_columns. I have a problem getting rid of those CURSOR STATEMENT headings in sqlplus. Any idea?

Tnx, Tomaz
 

Tom Kyte

Followup  

August 11, 2002 - 7:54 pm UTC

SQL> set long 50000

or some appropriately big size to get the entire statement.


You'll have to use PLSQL to use dbms_output to format the results -- sqlplus is a rather rudimentary reporting tool.  Not much control in that regards (you have 9i, just use dbms_metadata) 

Dbms_metadata

August 11, 2002 - 10:50 pm UTC

Reviewer: shanmugam from Singapore

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name) FROM USER_indexes u;
By using the above query I can get all the Indexes.
Is it possible to get all the constraints in the same way?
If yes, How ?


Tom Kyte

Followup  

August 12, 2002 - 7:56 am UTC

constraints come with the tables:



ops$tkyte@ORA9I.WORLD> create table t ( x int constraint check_x check ( x > 0 ) );

Table created.

ops$tkyte@ORA9I.WORLD> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0),<b>
         CONSTRAINT "CHECK_X" CHECK ( x > 0 )</b> ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCRE
ASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" 

Cleaning index creation script

August 12, 2002 - 6:51 am UTC

Reviewer: Basharat from Dubai

Tom, can we use below mention procedure to remove garbage from foo.sql.
1.Create a table with one column say col1 with enough length to hold one full line of foo.sql.
2. Load foo.sql into this table except for lines starting with REM and CONNECT by using when cluase in Control file.
3.Spool and select clo1 from this table into a file and then use this file as DDL script.

Tom Kyte

Followup  

August 12, 2002 - 8:09 am UTC

what "garbage" is there in foo.sql?

REM statements = comments = they will be ignored by sqlplus.

They are also part of your DDL, removing them would remove information you need.


grep would be easier to remove unwanted stuff, heck, just a text editor would be easier (global changes)

ora-01436

August 12, 2002 - 12:41 pm UTC

Reviewer: Cesar Salas from Mexico

Hi Tom,

I have a 8.1.6 DB and I need a exp file for 8.1.5, I was triying with exp73 (7.3.2.3.1) but this error ocurs at View part:


EXP-00008: ORACLE error 1436 encountered
ORA-01436: CONNECT BY loop in user data
EXP-00000: Export terminated unsuccessfully

Can you help me?

Thanks!

Tom Kyte

Followup  

August 12, 2002 - 1:55 pm UTC

Please contact support for this one. Java in the database put in some recursive dependencies that messed it up. They may have a workaround for you.

reference bug 1170962, there is an 8162 patch for that.


August 13, 2002 - 5:16 pm UTC

Reviewer: A reader

Tom, I want to extract the DDL for creating the tablespaces of the database. Our SAP stress test environment will be refreshed from the installation day backup soon. But I have to recreate the new locally managed tablespaces we created in this DB (There are about a hundred of them....). So I need the DDL for "create tablespace..." and "alter tablespace add datafile..." as it is.

I know that I can create the DDL from the data dictionary. Can this be extracted using indexfile? I just did a imp with indexfile=y, I don't see the tablespace DDL here. Thanks for your time.


Tom Kyte

Followup  

August 13, 2002 - 7:16 pm UTC

Here is a quick and dirty way:

$ exp userid=/ full=y

Export: Release 8.1.7.3.0 - Production on Tue Aug 13 19:14:13 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions^C <<<<<=== note I did that
EXP-00008: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
EXP-00000: Export terminated unsuccessfully


$ grep 'CREATE TABLESPACE' expdat.dmp
CREATE TABLESPACE "RBS_TS_01" DATAFILE '/d01/oradata/ora817dev/rbs_ts_01.dbf' SIZE 31563776 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_02" DATAFILE '/d02/oradata/ora817dev/rbs_ts_02.dbf' SIZE 157392896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_03" DATAFILE '/d03/oradata/ora817dev/rbs_ts_03.dbf' SIZE 26320896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_04" DATAFILE '/d04/oradata/ora817dev/rbs_ts_04.dbf' SIZE 48340992 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_05" DATAFILE '/d01/oradata/ora817dev/rbs_ts_05.dbf' SIZE 26320896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "USERS" DATAFILE '/d04/oradata/ora817dev/users.dbf' SIZE 661127168 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT NOLOGGING
CREATE TABLESPACE "DRSYS" DATAFILE '/d04/oradata/ora817dev/drsys.dbf' SIZE 36700160 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "XXX" DATAFILE '/tmp/xxx.dbf' SIZE 105373696 REUSE AUTOEXTEND ON NEXT 104857600 MAXSIZE 32767M DEFAULT STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50) ONLINE PERMANENT
CREATE TABLESPACE "TMP" DATAFILE '/tmp/tmp.dbf' SIZE 1048576 REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 65536
CREATE TABLESPACE "CLAMS" DATAFILE '/d03/oradata/ora817dev/clams.dbf' SIZE 78118912 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 524288


They'll all be there....

how to extract ddl in 9i

November 05, 2002 - 2:48 pm UTC

Reviewer: steve from dallas, orlando

How do we get around the import with show=y where
now instead of REM's we have double quotes and words that wrap like the following

"CREATE UNIQUE INDEX "PKCOURSE" ON "COURSE" ("COURSEID" ) PCTFREE 10 INITRA"
"NS 2 MAXTRANS 255 STORAGE(INITIAL 69632 FREELISTS 1 FREELIST GROUPS 1) TABL"
"ESPACE "USERS" LOGGING"

I want all the ddl as developers did not use any naming conventions in this db so I want to go and recreate most of the constraint names.

Tom Kyte

Followup  

November 05, 2002 - 4:30 pm UTC

well, use indexfile=t.sql instead of show=y that is a very editable file.  fairly trivial to remove REM on the beginning of each line.

Or, use dbms_metadata.get_ddl

OR <b>best yet!!!, in 9iR2 (only r2 and up, not r1, r2) -- just rename the constraint:</b>

ops$tkyte@ORA920.LOCALHOST> drop table t;

Table dropped.

ops$tkyte@ORA920.LOCALHOST> create table t ( x int primary key );

Table created.

ops$tkyte@ORA920.LOCALHOST> column constraint_name new_val C;
ops$tkyte@ORA920.LOCALHOST> select constraint_name from user_constraints;

CONSTRAINT_NAME
------------------------------
SYS_C003271

ops$tkyte@ORA920.LOCALHOST> alter table t rename constraint &c to t_pk;
old   1: alter table t rename constraint &c to t_pk
new   1: alter table t rename constraint SYS_C003271 to t_pk

Table altered.

ops$tkyte@ORA920.LOCALHOST> select constraint_name from user_constraints;

CONSTRAINT_NAME
------------------------------
T_PK

ops$tkyte@ORA920.LOCALHOST>

 

November 06, 2002 - 6:23 am UTC

Reviewer: Sagi from India

Hi Tom,

I have a general Doubt. Normally the validations can be done using different ways i.e.

a) Using the CONSTRAINTS.
b) Using Triggers or
c) Packages

Why 'a' is faster when compared to the others. For example say we have a FOREIGN KEY. Still internally something like this would be done

While inserting into EMP .....

Select count(*) from DEPT where deptno = <value> that is being inserted in EMP table.....

Still these routines need to be parsed etc etc. They how come they work faster. Can you tell about the depth of this. Is it something related to Kernel. I head once someone saying that CONSTRAINTS work at Kernel level and therefore they are faster.

Anticipating your valuable explanation.

Regards,
Sagi

Tom Kyte

Followup  

November 06, 2002 - 6:44 am UTC

constraints = builtin code = optimized to do exactly one thing = as fast as you can get.

triggers = you write it = does anything = generic = not as fast as you can get if you are specific.


constraints do one thing, they are implemented to "just do that".
triggers are user code, they do anything, they are interpreted code, they cannot do it as fast.

Also -- it is virtually impossible for YOU to write a foreign key check properly using triggers. If you want -- you can give it a try, post it here and I'll tell you why your code is wrong ;) You need to use declaritive constraints to get it right as well as get it fast (and further -- why would you consider writing tons of code in a trigger when "references" does all of that for you?)




November 06, 2002 - 8:04 am UTC

Reviewer: Sagi from India

Hi Tom,

Thanx for your valuable explanation.

I also do preferr constraints because its faster and easy. As you very well mentioned that even if we right it might not be that accurate and optimized. All I wanted to know why? Anything to do with Oracle Kernel or so.

Regards,
Sagi

How to find table order to load data from flat files?

November 06, 2002 - 11:22 am UTC

Reviewer: Rishi from India

This may be a bit irrelevant in the context of ongoing discussion
but I have a query about loading data in various tables in
a database.

Assume that I have few flat files each representing new data to be inserted
into a table.
For eg I have two files emp.txt and dept.txt for data to be loaded into
EMP and DEPT tables respectively.

Now if we load data in the following order
1.emp.txt into EMP table
2.dept.txt into DEPT table

Then we might get an error
due to Foreign Key constraint on
EMP table(EMP.DEPTNO = DEPT.DEPTNO)

Whereas if we load data
1.dept.txt into DEPT table
2.emp.txt into EMP table

Then we may not encounter the above error as we are loading data in the master table
first and then in the detail table.

So my question is how do we determine the correct order of tables
while loading data from flat files so that number of rows rejected
is minimum as in a real system we have 100s of tables.

Tom Kyte

Followup  

November 06, 2002 - 3:25 pm UTC

and you have no documentation on said system? no er's? no pictures?

me, I would disable the constraints...
loaded in any order I saw fit...
enable the constraints... (using exceptions into to catch bad records)

Export from differ user

March 19, 2003 - 12:09 pm UTC

Reviewer: Thomas from CA

Say I export the scott user data using sys user how do I import back using sys user.
ie. I will use owner in exp but how do i re import back the scott user.

Tom Kyte

Followup  

March 19, 2003 - 3:41 pm UTC

Just run imp, it'll put the objects back into SCOTT all by itself.

use imp .... show=y to SEE what it'll do.

March 19, 2003 - 4:34 pm UTC

Reviewer: Thomas from CA

I am sorry, I was not clear, my question was how to import using an indexfile=filename option from 2 different users

Tom Kyte

Followup  

March 19, 2003 - 4:54 pm UTC

well, that is in fact totally different from above -- however, I don't understand what you mean.

but if you have a full=y dmp file, and you would like the indexfile for usera and userb, then:

imp userid=/ 'fromuser=(scott,ops$tkyte)' indexfile=foo



does that. 

problem!

March 19, 2003 - 8:01 pm UTC

Reviewer: Chandru from Harlingen, TX

Tom, could you please help why I am not getting (pl see below) create tablespace definition. Thanks.

[oracle@tux /home]$ uname -a
Linux tux 2.2.14-5.0 #1 Wed Oct 31 17:07:11 EST 2001 i?86 unknown

[oracle@tux /home]$ exp id/pwd full=y

Export: Release 8.0.5.1.0 - Production on Wed Mar 19 19:50:11 2003

(c) Copyright 1998 Oracle Corporation. All rights reserved.


Connected to: Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.5.1.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting foreign function library names
. exporting object type definitions
. exporting cluster definitions
EXP-00008: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
EXP-00000: Export terminated unsuccessfully


[oracle@tux /home]$ grep 'CREATE TABLESPACE' expdat.dmp
Binary file expdat.dmp matches


Tom Kyte

Followup  

March 20, 2003 - 8:20 am UTC

it is the grep command -- it realizes "hey, binary file, special characters, lets just tell him we found something"


[tkyte@tkyte-pc tkyte]$ grep 'CREATE TABLESPACE' expdat.dmp
Binary file expdat.dmp matches

[tkyte@tkyte-pc tkyte]$ grep -a 'CREATE TABLESPACE' expdat.dmp
CREATE TABLESPACE "CWMLITE" BLOCKSIZE 8192 DATAFILE '/usr/oracle/ora920/oradata/ora920/cwmlite01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SEGMENT SPACE MANAGEMENT AUTO
....




Thank you

March 21, 2003 - 1:31 am UTC

Reviewer: Chandru from Harlingen, TX


March 25, 2003 - 7:08 pm UTC

Reviewer: Andrew Olsen from SLC Utah

Tom, you are the Master!

question regarding dbms_metadata

May 05, 2003 - 7:58 am UTC

Reviewer: A reader

Hi

I have a query regarding running functions in a SQL Query, when we deal with procedures we can say

exec PROCA(PAR1 => 'LAS', PAR2 => 'LOS') etc

however when I tried with dbms_metadata

select
dbms_metadata.get_ddl(OBJECT_TYPE => 'TABLE', NAME => TABLE_NAME, SCHEMA => 'HR')
from dba_tables where owner='HR'

I get ORA-00907: missing right parenthesis....

:-?

Tom Kyte

Followup  

May 05, 2003 - 8:22 am UTC

you cannot use named notation in SQL, only positional is supported.

it is not a dbms_metadata issue -- but rather a requirement imposed by calling plsql from sql -- only positional notation may be used.

dbms_metadata error

May 05, 2003 - 8:00 am UTC

Reviewer: A reader

Hi again

forgot to say, when I run the query as

select
dbms_metadata.get_ddl('TABLE', TABLE_NAME, 'HR')
from dba_tables where owner='HR';
ERROR:
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 345
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

I dont understand these errors :-((((

Tom Kyte

Followup  

May 05, 2003 - 8:33 am UTC

I'll have to guess lots since you don't provide much info.

you are trying to generate DDL for objects you are not permitted to see. unfortunately, the wrong error is being thrown.


Use a block like this:

a@ORA920> declare
2 l_clob clob;
3 begin
4 for x in ( select * from sys.dba_tables where owner = 'SCOTT' )
5 loop
6 begin
7 dbms_output.put_line
8 ( dbms_lob.substr(
9 dbms_metadata.get_ddl( 'TABLE', x.table_name, 'SCOTT' ),
10 250, 1 ) );
11 exception
12 when others then
13 dbms_output.put_line( 'Error on ' || x.table_name );
14 end;
15 end loop;
16 end;
17 /
Error on BONUS
Error on DEPT
Error on DUMMY
Error on EMP
Error on SALGRADE

PL/SQL procedure successfully completed.






ORA-06512

May 05, 2003 - 9:51 am UTC

Reviewer: A reader

Hi

I gt ORA-06512 because it tried to extract an IOT DDL, which seems not supported!

Thx!

Create TYPE DDL generation

July 08, 2003 - 7:46 pm UTC

Reviewer: Prudent from CA

Ok, I can use indexfile option in imp in 8i for tables and indexes, what if I need create type DDL from the db, how do I get the create type ddl generation script generated from the database.

Thanks

Tom Kyte

Followup  

July 09, 2003 - 10:23 am UTC

> imp userid=/ full=y show=y

Import: Release 8.1.7.4.0 - Production on Wed Jul 9 10:21:16 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path
import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing OPS$TKYTE's objects into OPS$TKYTE
 "CREATE TYPE "FOOBAR" TIMESTAMP '2003-07-09:10:19:04' OID 'C208059C93C36E28E"
 "034080020884DD6'  "
 "as object"
 "( x int, y date, z varchar2(250) );"
Import terminated successfully without warnings.



You will have to edit it up, alternatively, this works well:

<b>> strings expdat.dmp</b>
EXPORT:V08.01.07
DOPS$TKYTE
RUSERS
1024
                                    Wed Jul 9 10:19:25 2003expdat.dmp
#G##
#G##
+00:00
8.1.6
BEGINTYPE "FOOBAR" "C208059C93C36E28E034080020884DD6"
CREATE TYPE
CREATE TYPE "FOOBAR" TIMESTAMP '2003-07-09:10:19:04' OID 'C208059C93C36E28E034080020884DD6'
as object
( x int, y date, z varchar2(250) );
ENDTYPE
ENDTABLE
EXIT
EXIT
 

ORA-00972

July 09, 2003 - 7:45 pm UTC

Reviewer: prudent from CA

Tom,

I used strings with dmp file and this is what I got, when I ran it I got ORA-00972.
CREATE TYPE DAT1 TIMESTAMP '2003-07-09:19:35:38' OID 'C86A1E6565764D6C8A49555B1AEA5B82'
*
ERROR at line 1:
ORA-00972: identifier is too long

Is it ok to remove the TIMESTAMP and OID and execute the create type script like the one below

CREATE TYPE DAT1
is object (
nm varchar2(20),
sl number)
/

PS: I am using 8.1.6 and generating the create type and running it in 9i database.

Thanks for your input

Tom Kyte

Followup  

July 09, 2003 - 8:28 pm UTC

yes, if all you want is the DDL, you would want to remove those

July 12, 2003 - 3:33 pm UTC

Reviewer: A reader


long settings worked for me

July 22, 2003 - 11:47 am UTC

Reviewer: Sikandar Hayat from Pindi Bhattian - Pakistan

I was just searching for "set long".

Thanks

Getting the DDL for sequences via exp/imp

August 14, 2003 - 4:13 pm UTC

Reviewer: Ma$e

Hi Tom:

I have some sequences in my schema and they get exported.

However when I create the indexfile, I don't see the ddl for them.
Is it possible to generate DDL for this objects ?

I'm using Oralce 9i Rel 2. Am I missing something ?

Thanks.

Ma$e

Tom Kyte

Followup  

August 14, 2003 - 7:57 pm UTC

you can use

strings <dump file> | grep -i SEQUENCE

to get them fairly easily...

Sometimes Scripting becomes necessary - Stuck with Initial Extents

August 29, 2003 - 3:25 pm UTC

Reviewer: GovindanK from CA, USA

Hello Tom

Thanks a million for helping thousands of DBA's. Your column
brings to light many of the features which otherwise one may
not be aware of. Great!!!!.

Now onto the topic. I have a different openion on this issue.
Tell me if i am wrong.

With both exp/imp OR DBMS_METADATA one is stuck with 
initial, next extents setting.  Suppose i have to get the 
schema defn. from a schema with (say) initial extents as 
512K, 1M, 2M, 350M etc and setit up elsewhere, i need
to have that much freespace avbl. This is because the 
initial extent clause in the extracted stmt is likely to
override the tablespace default extent settings.
May be one need to use awk/perl to change the values in the
extracted ddl OR run the ddl and then deallocate unused.
(export to be down with rows=n). Is there any option in 
tablespace setting which will FORCE objects to be created
with the same settings as that of the tbs?

Am i missing something? Below is an example.


<example>
$ sqlplus system@mefngSQL*Plus: Release 9.2.0.1.0 - Production on Fri Aug 29 11:36:31 2003
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Enter password: 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> create tablespace my_lmt datafile '/u005/oradata/oradata/MEFNG/my_lmt.dbf'
  2  size 2M extent management local uniform size 64K;
Tablespace created.
SQL> alter user medev quota 2M on my_lmt;
User altered.
SQL> connect mesaledevown@mefng
Enter password: 
Connected.
SQL> show user
USER is "MESALEDEVOWN"
SQL> select tablespace_name, block_size, initial_extent , next_extent from user_tablespaces;
TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT
------------------------------ ---------- -------------- -----------
MESALEOWN                            4096         163840      163840
MESALEOWN_IDX                        4096         163840      163840
SQL> show user    
USER is "MESALEDEVOWN"
SQL> create table my_table (empno number ,empname varchar2(30)) storage(initial 1M next 512K);
Table created.
SQL> set linesize 132
SQL> select table_name, tablespace_name , initial_extent, next_extent from user_tables where table_name='MY_TABLE';
TABLE_NAME                     TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ -------------- -----------
MY_TABLE                       MESALEOWN                             1048576      524288

SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
$ 
$ exp mesaledevown@mefng file=my_table.dmp log=my_table.log tables=my_table rows=n
Export: Release 9.2.0.1.0 - Production on Fri Aug 29 12:01:53 2003
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Password: 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table                       MY_TABLE
Export terminated successfully without warnings.
$ 
$ 
$ sqlplus system@mefng                                                            
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Aug 29 12:02:52 2003
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Enter password: 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select username,default_tablespace from dba_users where username='MEDEV';
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
MEDEV                          MEDEV_SMALL
SQL> alter user medev default tablespace my_lmt;
User altered.
SQL> select username,default_tablespace from dba_users where username='MEDEV';
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
MEDEV                          MY_LMT
SQL> quit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> quit
$ 
$ imp medev@mefng file=my_table.dmp fromuser=mesaledevown touser=medev indexfile=foo.sql
Import: Release 9.2.0.1.0 - Production on Fri Aug 29 12:04:04 2003
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Password: 
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by MESALEDEVOWN, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
Import terminated successfully without warnings.
$ 
$ 
$ cat foo.sql
REM  CREATE TABLE "MEDEV"."MY_TABLE" ("EMPNO" NUMBER, "EMPNAME" 
REM  VARCHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
REM  STORAGE(INITIAL 1064960 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 249 
REM  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
REM  TABLESPACE "MESALEOWN" LOGGING NOCOMPRESS ;
$ 
$ 
$ cat foo.sql|sed 's/^REM  //g' |sed 's/MESALEOWN/MY_LMT/g' 
CREATE TABLE "MEDEV"."MY_TABLE" ("EMPNO" NUMBER, "EMPNAME" 
VARCHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
STORAGE(INITIAL 1064960 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 249 
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE "MY_LMT" LOGGING NOCOMPRESS ;
$ 
$ 
$ sqlplus medev@mefng
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Aug 29 12:04:52 2003
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Enter password: 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> REM the table will be created in the new tbs since sed too care of changing tbs
SQL> CREATE TABLE "MEDEV"."MY_TABLE" ("EMPNO" NUMBER, "EMPNAME"
  2  VARCHAR2(30)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  3  STORAGE(INITIAL 1064960 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 249 
  4  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
  5  TABLESPACE "MY_LMT" LOGGING NOCOMPRESS ;
Table created.
SQL> set linesize 132;
SQL> select table_name, tablespace_name , initial_extent, next_extent from user_tables where table_name='MY_TABLE';
TABLE_NAME                     TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT
------------------------------ ------------------------------ -------------- -----------
MY_TABLE                       MY_LMT                                1064960       65536
SQL>


</example>

You find now that the initial extent and next extents
are both gone.  One observes that the LMT setting had 
"overwritten" the next extent of the create table stmt.
My question is , 'Is there any way to override the 
Initial Extentsetting?'.

Thanks in Advance

 

Tom Kyte

Followup  

August 29, 2003 - 4:01 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7490088329317 <code>

dbms_metadata doesn't have to get the storage at all.

I should have checked

August 29, 2003 - 4:56 pm UTC

Reviewer: Govindan K from CA USA

Tom

Sorry that i did not look into the manual with more attention and bothered you.

It should be as follows:
begin
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', true );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', false );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', false );
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );
end;
/

SQL> set pagesize 0
SQL> set long 1000000
SQL> select dbms_metadata.get_ddl('TABLE','REGISTRATIONS') x from dual;

  CREATE TABLE "MESALEDEVOWN"."REGISTRATIONS"
   (    "REG_SYS_NO" NUMBER(10,0),
        "REG_REG_NO" VARCHAR2(15),
        ..... column definitions.....  
        "REG_ROLLED_FL" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "MESALEOWN" ;
SQL> 

Tom Kyte

Followup  

August 29, 2003 - 6:11 pm UTC

no worries -- there is alot of doc there :)

How to suppress Schema Owner / Substitute another schema name

September 05, 2003 - 11:33 am UTC

Reviewer: GovindanK from CA, USA

Tom

Thanks a lot to the answers you are providing. "Wealth might
seek one, Wisdom must be sought" it is said. You are an
example.

Now onto the question.

Is there any option in dbms_meta data.set_transform to
suppress the name of the schema "owner." from appearing in
the get_ddl procedure output. I found options to
suppress storage,tbs etc. May be i am missing something.
Also, is there any option to replace the schema name
("OWNER." with "SCHEMA_TO_BE."); This would enable one
to generate ddls from a user with CREATE ANY TABLE privs.

TIA

Tom Kyte

Followup  

September 05, 2003 - 6:52 pm UTC

No, there is not currently any such filter. you would have to use the xml interface and develop a style sheet to do a custom transformation.

using get_ddl() for tablesspace info

October 11, 2003 - 6:34 pm UTC

Reviewer: Peter Tran from Houston, TX USA

Hi Tom,

Can you help me with the syntax on using dbms_metadata.get_ddl for tablespaces?

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','FOO_TBS','USER') from dual;
ERROR:
ORA-31600: invalid input value SCHEMA for parameter NAME in function SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1980
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3665
ORA-06512: at "SYS.DBMS_METADATA", line 670
ORA-06512: at "SYS.DBMS_METADATA", line 577
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

Thanks,
-Peter
 

Tom Kyte

Followup  

October 11, 2003 - 7:43 pm UTC

 
ops$tkyte@ORA920> select dbms_metadata.get_ddl( 'TABLESPACE', 'USERS' ) from dual;
 
DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------
 
  CREATE TABLESPACE "USERS" DATAFILE
  '/usr/oracle/ora920/OraHome1/oradata/ora920/users01.dbf' SIZE 26214400 REUSE
  AUTOEXTEND ON NEXT 1310720 MAXSIZE UNLIMITED
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
 


don't know why you passed 'USER' in -- i doubt that you have a schema named user and I'm sure it doesn't "own" the FOO_TBS tablespace...  No one really "owns" a tablespace.
 

Forgot the fundamental...

October 11, 2003 - 11:12 pm UTC

Reviewer: Peter Tran from Houston, TX USA

"No one really "owns" a tablespace."

I actually knew that, but it totally slip my mind.

Argh...Time to refresh my fundamentals with a pass through the Concept guide.

Thanks again,
-Peter


Importing PL/SQL code

October 14, 2003 - 11:41 am UTC

Reviewer: Arun Mathur from Marietta,GA

Tom,

You provide scripts to extract code from a certain schema and install them to another (Chapter 8 from Expert One On One Oracle, pp.337-338). I tried it in a development environment, and it works great. Someone on the Metalink forum had a question on how to import code, and I thought she'd benefit from your scripts. However, I wanted to check with you first. Since the scripts came from your book, I'll understand if you'd rather them not be pasted out in the open.

Thanks again for providing such a great site to everyone. It's pleasure being a "student" of yours.

Take care,
Arun



Tom Kyte

Followup  

October 14, 2003 - 12:43 pm UTC

put them anywhere -- they are probably on here somewhere already!

End of communication channel when executing get_ddl

October 23, 2003 - 6:15 am UTC

Reviewer: Sasa from Belgrade, Serbia

Hi Tom,

I tried your test case :

SELECT DBMS_METADATA.GET_DDL ('TABLE','DUAL','SYS') FROM DUAL;

and got this:

SQL> 
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> 
SQL> SELECT DBMS_METADATA.GET_DDL ('TABLE','DUAL','SYS') FROM DUAL;
ERROR:
ORA-03113: end-of-file on communication channel

Thanks 

Tom Kyte

Followup  

October 23, 2003 - 12:47 pm UTC

contact support.

connect problem with imp

December 24, 2003 - 9:45 am UTC

Reviewer: A reader from Boston, MA

Greetings Tom !!
I am huge fan of yours. Thank you.

db -> 9.2.0.2.
Am moving a db from HP to Tru64 platform. So, am doing a full export/import. I am creating an indexfile(for parallelism) to create them after the import. The question is, I see "CONNECT SUPPORT;", "CONNECT SYSTEM;" and similar connect statements but WITHOUT any passwords. And if connected to sqlplus as sysdba and run this script, wouldn't it complain about the password ?
The destination database was created and all the tablespaces have been created before doing the full import.
+++++++++++++++++++++++++++++++++++++++++
======exp.sh==========
exp "'sys/test as sysdba'" full=Y file=AST_X.DMP compress=N statistics=NONE
======ind_cr.sh=======
imp "'sys/f0rb1den as sysdba'" file=AST_X.DMP full=Y indexfile=ind_cr.sql
======exp.sh==========
imp "'sys/f0rb1den as sysdba'" file=AST_X.DMP full=Y indexes=N commit=Y ignore=Y
=======





Tom Kyte

Followup  

December 24, 2003 - 10:23 am UTC

sqlplus won't complain -- it'll simply stop and ASK you for the password.

you see -- exp doesn't have access to the password and it would be really bad to put it in a clear text file.

So, either you edit the file beforehand, add the passwords

OR

you provide them when prompted

OR

you can use my "su.sql" script and change the connect to @su -- see </code> http://asktom.oracle.com/~tkyte <code>

thats awesome

December 24, 2003 - 12:32 pm UTC

Reviewer: A reader from Boston ,MA

Wow, thats a fantastic script.
Thank you very much !!!

New issue?

February 05, 2004 - 9:48 pm UTC

Reviewer: Kimathi from Los Angeles, USA

Tom,
I am getting the following when I try to extract the comments from tables.
What could I be doing wrong?

1 SELECT DBMS_METADATA.GET_DDL('COMMENT',comments) a1
2 FROM ALL_TAB_COMMENTS
3 WHERE OWNER ='MYTABLE'
4* AND TABLE_NAME = 'SCOTT'
self@self> /
ERROR:
ORA-31600: invalid input value LONGNAME for parameter NAME in function SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1980
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3665
ORA-06512: at "SYS.DBMS_METADATA", line 670
ORA-06512: at "SYS.DBMS_METADATA", line 571
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1



no rows selected


Tom Kyte

Followup  

February 07, 2004 - 12:08 pm UTC

  1  select dbms_metadata.get_dependent_ddl( 'COMMENT', table_name, user )
  2*   from (select table_name from user_tab_comments where comments is not null )
ops$tkyte@ORA9IR2> /
 
DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT',TABLE_NAME,USER)
--------------------------------------------------------------------------------
 
   COMMENT ON TABLE "OPS$TKYTE"."FOO"  IS 'snapshot table for snapshot OPS$TKYTE
.FOO'
 
 
 
   COMMENT ON TABLE "OPS$TKYTE"."T"  IS 'hello world'
 
 
 


You are passing "comments" in your call as the "object name".

That and -- COMMENT is only allowed in the GET_DEPENDENT_DDL api as per the table in the supplied packages guide.....
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1031509

See
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1028659
and give that section a read thru... 

excellent

February 09, 2004 - 3:27 pm UTC

Reviewer: Kimathi Njeru from El Segundo, CA

Thanks Tom!!

export/import inside a package

March 17, 2004 - 11:49 am UTC

Reviewer: snagaraj from pittsburgh,pa

Tom,

I have a situation that needs to drop and create tables dynamically. Before I drop I need to get the structure of the table and then recreate it.
Is there a way to export the structure of the table and import it within a package?
In this thread I learnt the use of dbms_metadata package to extract the ddl. Still I feel export and import may be a better deal and I want to know how can that be incorporated within a stored package.

thanks

Tom Kyte

Followup  

March 17, 2004 - 3:17 pm UTC

what is wrong with TRUNCATE in this case?

March 17, 2004 - 3:53 pm UTC

Reviewer: A reader

Well, it is not exactly drop and create, sometimes there is going to be new columns or dropping unwanted columns. Right now, the developer in his pl/sql program do it dynamically and we are thinking of revoking the privilege of doing DDL and instead call a procedure that does all kinds of DDL.

Thanks


Tom Kyte

Followup  

March 17, 2004 - 4:07 pm UTC

hows about

truncate (when you want to remove data)
alter table t add (when you want to add a column)
alter table t drop or set unused (when you want to remove a column)


be easier then parsing some ddl to remove columns/add columns and such.


(dbms_meta_data can extract the ddl, but this ddl would be *easier*)

Is There a Way to Use DBMS_Metadata Remotely?

March 18, 2004 - 8:20 pm UTC

Reviewer: Doug Wingate from New Orleans, LA USA

Is it somehow possible to use DBMS_Metadata.Get_DDL in an Oracle9i database to query the data dictionary of a remote database that's at the Oracle8i level and produce DDL for database objects in that Oracle8i database? I anticipate that your answer will be, "No," but of course if this is possible, it would be a handy workaround in some shops--like mine--in which some of the databases are still at the Oracle8i level and thus lack the DBMS_Metadata package.

Thanks.

Tom Kyte

Followup  

March 19, 2004 - 8:09 am UTC

dbms_metadata doesn't exist in 8i, and the data dictionary format dbms_metadata would be expecting in 9i differes radically from what was n 8i.

In 8i -- you would use

exp ...... rows=n
imp ...... indexfile=foo.sql

wow lots of time coding ddl's and related problems

March 19, 2004 - 9:49 am UTC

Reviewer: rene dagenais from USA

by the way quest software as you know can do the job correctly under 1 minute and generate any ddl on the fly.. so i kind of debating the notepad dba's that have to much time on their hands and try to sql to death a function that can be done in a more productive way. but this does not take away the knowlegde base needed to complet dba task and understanding how oracle database work. and like most of us we like a good chalenge anyway. Thanks for the info tom you are still the best!

extract FKs

March 20, 2004 - 3:42 pm UTC

Reviewer: A reader

Hi

I am currently writing a PL/SQL package to delete several 100 million rows tables. On of steps is disabling constraints. Since I am using skip_unusable_indexes in my code I am forced to drop my parent primary keys in order for my insert to work. When dropping PKs all FKs are dropped as well so I am currently seeking a way to extract the affected FKs DDL. I am using 8.1.7.4 so DBMS_METADATA is out of question...

Is there any way using PL/SQL?


Just out of curiosity I was testing in 9i if I could extract FKs DDL using dbms_metadata, I used get_dependant_ddl function but it does not work :-0

thx

Tom Kyte

Followup  

March 21, 2004 - 9:46 am UTC

you could use deferrable primary key constraints, which would be implemented with non-unique indexes, allowing you to disable them -- not drop.

this shows how to extract fkeys:

column fkey format a80 word_wrapped
select
'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ');' fkey
from
( select a.table_name child_tname, a.constraint_name child_cons_name,
b.r_constraint_name parent_cons_name,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name, b.r_constraint_name ) child,
( select a.constraint_name parent_cons_name, a.table_name parent_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = upper('&1')
/


you dont need to drop the PKs

March 21, 2004 - 10:41 am UTC

Reviewer: A reader

To last reader

You can simply do this

ALTER TABLE A DISABLE NOVALIDATE CONSTRAINT A_PK CASCADE;

Then simply enable them, there is no need to extract DDL

dbms_metadata

April 22, 2004 - 3:06 pm UTC

Reviewer: Jim from Phila, Pa

is there anyway to avoid the chopped lines in the dbms_metadata.get_ddl output? I usually find a few lines where the line is broken at about the 80 character mark. Is this a limitation of put_line? or Is there someway around it?

Tom Kyte

Followup  

April 23, 2004 - 9:11 am UTC

alias the column and use "column text format a<nnnnn>" to control the max line width (set linesize nnnnn as well!). consider:




scott@ORA9IR2> column text format a20
scott@ORA9IR2> select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) text from dual;

TEXT
--------------------

CREATE TABLE "SCOT
T"."EMP"
( "EMPNO" NUMBER(
4,0) NOT NULL ENABLE
,
"ENAME" VARCHAR2(10
),
"JOB" VARCHAR2(9),

"MGR" NUMBER(4,0),

"HIREDATE" DATE,
"SAL" NUMBER(7,2),

"COMM" NUMBER(7,2),

"DEPTNO" NUMBER(2,0
)
) PCTFREE 10 PCTU
SED 40 INITRANS 1 MA
XTRANS 255 NOCOMPRES
S LOGGING
STORAGE(INITIAL 65
536 NEXT 1048576 MIN
EXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREE
LISTS 1 FREELIST GRO
UPS 1 BUFFER_POOL DE
FAULT)
TABLESPACE "SYSTEM
"



scott@ORA9IR2> column text format a200
scott@ORA9IR2> select dbms_metadata.get_ddl( 'TABLE', 'EMP', 'SCOTT' ) text from dual;

TEXT
------------------------------------------------------------------------

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) 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 "SYSTEM"


what setting an I missing?

April 23, 2004 - 3:15 pm UTC

Reviewer: Jim from Phila, PA

I tried your suggestion, actually I had already tried it:
set lines 200
col text for a200
select dbms_metadata.get_ddl('INDEX',index_name,'OWNER')
from user_indexes;

And I still get:
CREATE UNIQUE INDEX "MIIDBA"."XPKMIIL_REQ_CNTL"ON "MIIDBA"."MIIL_REQ_CNTL("
REQ_ID")
where ""REQ_ID")"" is distinctly on a second line
so what setting am I missing?


Tom Kyte

Followup  

April 23, 2004 - 3:32 pm UTC

you did not alias the column.

select dbms_metadata.get_ddl( ... ) TEXT from user_indexes;



duh1

April 26, 2004 - 10:42 am UTC

Reviewer: Jim from Phila,PA

Thanks. I get it now

DBMS_METADATA.GET_DDL - object_type parameter is case sensitive ?

April 27, 2004 - 12:51 pm UTC

Reviewer: Michal from Bratislava, Slovakia

hi Tom,
I only need to confirm, that i solved one problem succesfuly :-)

this query return error :

test@ora9ir2 > select dbms_metadata.get_ddl('table', 'MY_TABLE') from dual;
ERROR:
ORA-31600: invalid input value table for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1536
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1900
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3606
ORA-06512: at "SYS.DBMS_METADATA", line 504
ORA-06512: at "SYS.DBMS_METADATA", line 560
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

no rows selected

... and this is good :
test@ora9ir2 > select dbms_metadata.get_ddl('TABLE', 'MY_TABLE') from dual;

CREATE TABLE "TEST"."MY_TABLE"
( "ID" NUMBER(10,0),
....... rest of the DDL.

so, my question is - is the parameter object_type of the procedure DBMS_METADATA.GET_DDL (or probably the core procedure .OPEN) case sensitive ? I read the reference for this package and i can't find any note that it MUST be upper-case (except the table of object types).
:-))

Tom Kyte

Followup  

April 28, 2004 - 1:18 pm UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_metad2.htm#1031458 <code>

is the table of valid values -- it doesn't say it can be lower case, they are all listed in upper case -- and since lower case doesn't work but upper does, it seems safe to assume "must be from the list, as the list presents them"

Dropping tables in order

April 29, 2004 - 9:55 am UTC

Reviewer: Jim

Tom,

If I want to drop all the tables in the schema, can I get the list in an order so that the child tables are dropped first and then the parent table.

Right now I run the script multiple times till all the tables are dropped. But definitely you might be having a trick :)

Always appreciate your help!

Regards,
Jim

Tom Kyte

Followup  

April 29, 2004 - 10:39 am UTC

drop table t CASCADE CONSTRAINTS;

do them in any order.

extract ddl without partition

May 03, 2004 - 11:49 am UTC

Reviewer: Lee from NY, USA

Tom,

I want to generate/create tables for later "exchange" based on partitioned tables. I want the generated tables to be identical to the partition table but with out the partitinos. can this be done with metadata package?

Thanks.

Tom Kyte

Followup  

May 03, 2004 - 7:06 pm UTC

what about

create table to_be_exchanged
as
select * from partitioned_table where 1=0;


Generating an insert statment?

September 10, 2004 - 6:23 pm UTC

Reviewer: A reader

Hi Tom,
I created a script to move data from a DW staging tables (about 100 tables) to a new schema to hold history. It is monthly job. First time I wrote a scripts
CREATE TABLE a_hist as select 200407 snapshot_key, a.*, sysdate record_creation_date FROM a;
...
...
...
In subsequent months, I am planning to do
INSERT INTO a_hist SELECT 200408, a.*, sysdate FROM a
...
...
I want to know when I do a.* without explicitly defining the column names, will Oracle match column in exact sequence? I know I will get error for wrong data types, but what if data types and size is same for columns?
For example, Table a (a1 number, b1 number)
INSERT INTO a_hist SELECT 200407, a.*, sysdate
from a
where a_hist is: snapshot_key,a1,b1,record_creation_date
I want result from:
a.a1 to go into a_hist.a1,
a.b1 to go into a_hist.b1, and so on...
I know you will say, use explict column names. As this is a temporary job till we develop complete solution, I don't want to waste time, if Oracle automatically takes care of this, as you always mention why to write code, if it can be done otherwise easily.
Your input will be highly appreciated.
Thanks

Tom Kyte

Followup  

September 10, 2004 - 7:45 pm UTC

* will resolve in column position -- as you see it in user_tab_columns

it will not do anything "by name", it is all positional.

My understanding...

September 10, 2004 - 8:20 pm UTC

Reviewer: A reader

Hi Tom,
It means I won't have any problem by using this technique.
Thanks

Tom Kyte

Followup  

September 11, 2004 - 8:00 am UTC

as long as you want positional notation

September 11, 2004 - 8:54 am UTC

Reviewer: A reader

"* will resolve in column position -- as you see it in user_tab_columns"

user_tab_columns order by column_id, right?

For a table with 10 columns, what if I drop column #5 and add another column? Would that preserve the column order and add the new column at #11 or #10? i.e. would it keep a "hole" at #5?

Thanks

Tom Kyte

Followup  

September 11, 2004 - 10:45 am UTC

yes, by column_id

new columns are added at the "end"

pl/vision & dumpsql

September 13, 2004 - 2:47 am UTC

Reviewer: sergei

1. in db prior to 9i it is possible to use pl/vision's PLVDDD package to extract ddl for many types of db objects (however it cannot extract ddl for object types, indextypes, libraries etc.)

2. here's a simple "utility" (written in perl) which makes output of the "imp show=y" command more readable (hope it may be useful for somebody):

#####################
# file: clean_imp_results.pl
$MAX_LINE_LENGTH = 75;
while (<>) {
if (/^\s*"/) { #data
$_ =~ s/^\s+"//;
$_ =~ s/"\s*$//;

print;

if (length($_) < $MAX_LINE_LENGTH) {
print "\n";
}
}
}
#####################

#####################
# file: dumpsql.bat
exp userid=%1 file=_expdat.dmp compress=y rows=n
imp userid=%1 file=_expdat.dmp show=y full=y 2>&1 | perl clean_imp_results.pl > %2

it is possible to use like follows:
dumpsql <userid> <outfile>

it removes unnecessary quotes (works in most cases except when source line was 75 positions wide).
unfortunately the output file cannot be run in sqlplus because it has no "/" and ";", but nevertheless it is more readable than "raw" imp output.

Is not strange ?

September 20, 2004 - 10:09 am UTC

Reviewer: parag jayant patankar

Tom,

While going thru dbms_metadata.get_ddl details I found that my database in Oracle 9i on Windows NT having all LMTs

19:04:49 atlas@ATP1P1> l
INDX LOCAL AUTO
ODM LOCAL AUTO
TOOLS LOCAL AUTO
USERS LOCAL AUTO
XDB LOCAL AUTO
BNPV2 LOCAL AUTO
NETCASH LOCAL AUTO
HRIS LOCAL AUTO

When I am trying to extract ddl details it is giving following

SELECT DBMS_METADATA.GET_DDL(‘TABLE’, ‘EMP’, ‘SCOTT’) from dual;

CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
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,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE NOVALIDATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

My Question is that my tablespaces are of LMT then this extraction of DDL why giving storage cluase details including pctfree, pctused, freelist ...etc. I found it very strange !!! Do you agree ?

thanks & best regards
pjp



Tom Kyte

Followup  

September 20, 2004 - 10:55 am UTC

pctfree, pctused, freelists all 100% apply to LMTs that are not ASSM.

pctfree applies in all cases.

pctused/freelists are ignored with assm.

initial, next, minextents all affect storage allocation (initial storage allocation) in LMTS.

If you do not wish for it to be there, you can certainly filter it off.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7490088329317 <code>


but it is 100% valid and materially affects the objects definition (storage allocated, how space is managed)

Extracting constraints

October 21, 2004 - 5:14 pm UTC

Reviewer: A reader

Is there a way to extract constraints on a table using DBMS_METADATA and suppress the storage, tablespace, etc stuff?

I tried

create table t(i int primary key,j int);
begin
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, upper('segment_attributes'),false);
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, upper('storage'),false);
dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, upper('tablespace'),false);
end;
/

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','T') FROM DUAL;

But it still gives me all the USING INDEX ..., TABLESPACE, etc stuff.

What am I missing? Thanks


Tom Kyte

Followup  

October 22, 2004 - 3:39 pm UTC

ops$tkyte@ORA10G> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','T') from dual;
 
DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','T')
-------------------------------------------------------------------------------
 
  ALTER TABLE "OPS$TKYTE"."T" ADD PRIMARY KEY ("I") ENABLE
 
 


Looks like a 'product issue' that is corrected in 10g.  You might contact support and see if there is a patch for your release to fix the transform, it should have worked (but does not in 9ir2) 

formatting problem of dbms_metadata.get_ddl

November 08, 2004 - 3:02 pm UTC

Reviewer: Jianhui from DC

Tom,
It seems the sql*plus formats the dbms_metadata.get_ddl output to fixed linesize (80) default, I have changed it to 200 but it still cuts through the middle of a line which causes syntax error, it's virtually impossible to edit the scirpt since there are thousands of objects, do you have any better solution to generate DDL script which is ready to kick off without editing? Same for exp/imp, since the number of objects is huge, there is no way to edit the script manually.

Tom Kyte

Followup  

November 08, 2004 - 5:40 pm UTC

<b>
ops$tkyte@ORA9IR2> column x format a20 word_wrapped</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) x from dual
  2  /
 
X
--------------------
CREATE TABLE
"OPS$TKYTE"."T"
(       "OWNER"
VARCHAR2(30) NOT
NULL ENABLE,
"VIEW_NAME"
VARCHAR2(30) NOT
NULL ENABLE,
"TEXT" CLOB
) 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"
LOB ("TEXT") STORE
AS (
TABLESPACE "USERS"
ENABLE STORAGE IN
ROW CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE(INITIAL
65536 NEXT 1048576
MINEXTENTS 1
MAXEXTENTS
2147483645
PCTINCREASE 0
FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL
DEFAULT))
 
 
 <b>
ops$tkyte@ORA9IR2> column x format a200 word_wrapped</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) x from dual
  2  /
 
X
-------------------------------------------------------------------------------
CREATE TABLE "OPS$TKYTE"."T"
(       "OWNER" VARCHAR2(30) NOT NULL ENABLE,
"VIEW_NAME" VARCHAR2(30) NOT NULL ENABLE,
"TEXT" CLOB
) 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"
LOB ("TEXT") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 
 
 

extracting ddl from another schema

November 18, 2004 - 4:08 pm UTC

Reviewer: Bonny Kapou from Montreal, QC Canada

Hi Tom
I have a situation when my user have select_catalog_role and I can get index ddl from others schema, but I have an error when I encapsulate the call of dbms_metadata.get_ddl in my own function.

SQL<devdmcfr>CONNECT util/util
Connected.
SQL<devdmcfr>select dbms_metadata.get_ddl('INDEX','I_DSTEMPPIATERMINAL_01','DEVDW1MINI') from dual;

DBMS_METADATA.GET_DDL('INDEX',
--------------------------------------------------------------------------------

CREATE INDEX "DEVDW1MINI"."I_DSTEMPPIATERMINAL_01" ON "DEVDW1MINI"."DS_TEMP_PI
A_TERMINAL" ("NPA", "FRAME_NXX", "TAPER_CODE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DEVDW1MINI_INDX_32K_C"

SQL<devdmcfr>create or replace function my_get_ddl(vobject_type in varchar2, vobject_name in varchar2, vschema in varchar2)
2 return clob as
3 my_ddl clob;
4 begin
5 my_ddl := dbms_metadata.get_ddl(vobject_type, vobject_name, vschema);
6 return my_ddl;
7 end;
8 /

Function created.

SQL<devdmcfr>select my_get_ddl('INDEX','I_DSTEMPPIATERMINAL_01','DEVDW1MINI') from dual;
ERROR:
ORA-31603: object "I_DSTEMPPIATERMINAL_01" of type INDEX not found in schema "DEVDW1MINI"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at "UTIL.MY_GET_DDL", line 5

where is the problem?
I used also the programmatical interface of dbms_metadata and the function fetch_clob return null.

Tom Kyte

Followup  

November 19, 2004 - 9:58 am UTC

roles are not enabled during the execution of a definer rights procedure.

http://asktom.oracle.com/Misc/RolesAndProcedures.html



ops$tkyte@ORA9IR2> select dbms_metadata.get_ddl('INDEX','EMP_IDX','SCOTT') from dual;
 
DBMS_METADATA.GET_DDL('INDEX','EMP_IDX','SCOTT')
-------------------------------------------------------------------------------
 
  CREATE INDEX "SCOTT"."EMP_IDX" ON "SCOTT"."EMP" ("ENAME")
  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 "SYSTEM"
 
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function my_get_ddl
  2  (vobject_type in varchar2, vobject_name in varchar2, vschema in varchar2)
  3  return clob
  4  as
  5      my_ddl clob;
  6  begin
  7        my_ddl := dbms_metadata.get_ddl(vobject_type, vobject_name, vschema);
  8        return my_ddl;
  9  end;
 10  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select my_get_ddl('INDEX', 'EMP_IDX', 'SCOTT' ) from dual;
ERROR:
ORA-31603: object "EMP_IDX" of type INDEX not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 628
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at "OPS$TKYTE.MY_GET_DDL", line 7
 
 
 
no rows selected
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function my_get_ddl
  2  (vobject_type in varchar2, vobject_name in varchar2, vschema in varchar2)
  3  return clob<b>
  4  AUTHID CURRENT_USER</b>
  5  as
  6      my_ddl clob;
  7  begin
  8        my_ddl := dbms_metadata.get_ddl(vobject_type, vobject_name, vschema);
  9        return my_ddl;
 10  end;
 11  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select my_get_ddl('INDEX', 'EMP_IDX', 'SCOTT' ) from dual;
 
MY_GET_DDL('INDEX','EMP_IDX','SCOTT')
-------------------------------------------------------------------------------
 
  CREATE INDEX "SCOTT"."EMP_IDX" ON "SCOTT"."EMP" ("ENAME")
  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 "SYSTEM"
 
<b>but before you just go through that on, make sure you understand the ramifications (the person RUNNING the procedure needs to have access to the index, not the owner of the procedure)

the other alternative is direct grants to the owner of the procedure, not via a role</b>
 

datapump seems superior for generating ddl

February 21, 2005 - 1:03 pm UTC

Reviewer: mhthomas from USA

datapump seems superior for generating ddl

Tom Kyte

Followup  

February 21, 2005 - 1:06 pm UTC

yes it is, but question was asked about 8.0.5

actually, dbms_metadata.get_ddl would be superior to either -- but did not exist in the versions mentioned here. (although I did mention it too above)

How does dbms_metadata compare to datapump for index stats?

February 21, 2005 - 1:56 pm UTC

Reviewer: mhthomas from USA

The original question was to maintain DDL for indexes, but on version 8.x. My question is for current reference.

How does dbms_metadata compare to datapump for index stats?
Sorry, if its an apples-oranges (bad) comparison.

I've seen datapump generate dbms_stats commands for indexes. I'm curious. I don't know if DBMS_METADATA (or another feature) has/will have a similar index stats capability?

PS: I like DBMS_METADATA. Sometimes, its a struggle to work out all the syntax for DBMS_METADATA because we don't have enough examples in the docs/web/etc. For example, I couldn't find enough ROLE generation examples using DBMS_METADATA in the docs, and on google.

Tom Kyte

Followup  

February 21, 2005 - 3:43 pm UTC

dbms_metadata can be run by anyone, using sqlplus with no setup whatsoever.

not so for datapump.

dbms_stats exports statistics, that is all datapump is calling

if you want the DDL, dbms_metadata
if you want the stats, dbms_stats

but unless you take the DATA with you -- meaning you'd be using exp or expdp -- the stats are not "so useful", for you'll have different data and therefore different stats.

dbms_metadata,

February 21, 2005 - 6:05 pm UTC

Reviewer: sns from austin,tx

Is there a limit in size the dbms_metadata can extract the DDL of an object?

I had set long to 10000000
then tried to extract the DDL of a table that has 800 partitions (pretty big DDL)
I did not got anything on my SQLPLUS screen.

Thanks,

Tom Kyte

Followup  

February 22, 2005 - 8:06 am UTC

not that I am aware of, if it reproduces - please file a tar with support.

ddl for all packages in the schema

March 04, 2005 - 9:02 am UTC

Reviewer: Ik from rennie_sreekumar@yahoo.com

Tom,

Iam trying to extract DDLs of all packages ownerd by that user and build a creation script. Is there a way by which i can write each package into a separate file?

FOR eg:- pkg_a should be in pkg_a.sql, pkg_b should be in pkg_b.sql and so on.

I wanted a solution without using utl_file. Using sqlplus in combination with unix would be desirable.

set long 99999999
column pkgtxt format a5000
set echo off
set head off
set feedback off
set linesize 8000
set trimspool on
set verify off
set termout off
set embedded on
set wrap on
spool pkg_script.sql
select replace(dbms_metadata.get_ddl('PACKAGE',object_name),'"'||user||'".', ' ')||'/'||CHR(10) pkgtxt from user_objects
where object_type = 'PACKAGE';
spool off
ed pkg_script.sql

Thanks,

Tom Kyte

Followup  

March 04, 2005 - 9:36 am UTC

set termout off
set heading off
set feedback off
set linesize 50
spool xtmpx.sql
select '@getcode ' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
spool getallcode_INSTALL
select '@' || object_name
from user_objects
where object_type in ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
/
spool off
set heading on
set feedback on
set linesize 130
set termout on
@xtmpx.sql




is what I use. creates a script that calls the getcode script one by one to get the code out where getcode is:


set feedback off
set heading off
set termout off
set linesize 1000
set trimspool on
set verify off
spool &1..sql
prompt set define off
select decode( type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1', '/'||chr(10),
null) ||
decode(line,1,'create or replace ', '' ) ||
text text
from user_source
where name = upper('&&1')
order by type, line;
prompt /
prompt set define on
spool off
set feedback on
set heading on
set termout on
set linesize 100


Great...Thanks

March 05, 2005 - 1:41 am UTC

Reviewer: Ik from BG


ORA-31603

March 15, 2005 - 9:05 am UTC

Reviewer: A reader

Tom,
After using same set of statements (from your original answer), i am getting ORA-31603
Here is the full error message:
ERROR:
ORA-31603: object "emp_test" of type TABLE not found in schema "MYSCHEMA"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 2857
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3192
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4078
ORA-06512: at "SYS.DBMS_METADATA", line 326
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

EMP_TEST Table exist in MYSCHEMA. I am running this from Sql*Plus. Oracle version:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

Thanks,

Tom Kyte

Followup  

March 15, 2005 - 9:18 am UTC

given that my original answer didn't use dbms_metadata....

how about you clue us in on the exact set of steps -- eg, the call you made (but did not show), who you are logged in as, whether you have privs (not via a role) to see the table, etc.......

For the poster looking for ROLE DDL examples (mhthomas).

March 28, 2005 - 5:50 pm UTC

Reviewer: Kashif from Reston, VA

Hello -

I've been using this script to generate the DDL for roles from my database:

SQL> get get_role_ddl
  1  exec dbms_metadata.set_transform_param( -
  2          dbms_metadata.session_transform, 'SQLTERMINATOR', true);
  3  spool role_ddl.sql
  4  set pages 2000
  5  set feedback off
  6  set heading off
  7  SELECT DBMS_METADATA.GET_DDL('ROLE', upper ('&1'))
  8  from dual
  9  /
 10  SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', upper ('&1'))
 11  from dual
 12  /
 13  SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', upper ('&1'))
 14  from dual
 15  /
 16  SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', upper ('&1'))
 17  from dual
 18  /
 19  set feedback on
 20  set heading on
 21* spool off

Just execute this script using the following syntax:

SQL> get_role_ddl.sql <role_name>

Hope it helps...

Kashif 

Tom Kyte

Followup  

March 28, 2005 - 6:35 pm UTC

very nice, thanks for sharing it.

Anytime!

March 29, 2005 - 9:11 am UTC

Reviewer: Kashif from Reston, VA

I should also mention that I have the "set verify off" and "set long 10000" calls in my login script and so anyone using this script will need to add those calls to the script if they do not have them in their login.sql scripts.

Secondly, the generated script might need some cleaning up in case you are a clean script freak like I am. If your role does not have any one or more of the grants, i.e. OBJECT_GRANT, SYSTEM_GRANT or ROLE_GRANT, you will hit an error when your script is being generated, and that error message will be in your spooled file (generated script), and you will need to clean it out. Though the script will run fine without any cleaning up as well, it'll just throw some error messages out when it comes across those errors and continue.

Kashif

auto segment storage management ?

April 14, 2005 - 8:24 am UTC

Reviewer: Parag J Patankar from India

Hi Tom,

I am confused with storage parameters in segment space management auto clause of a tablespace. Can you pl tell me what are the parameter are valid under segment storage management auto cluase ?

regards & thanks
pjp

Tom Kyte

Followup  

April 14, 2005 - 8:57 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses9a.htm#78017

Ok, in storage there are a couple of things.  extent wise there are:
<b>
initial, next, minextents, maxextents, pctincrease.
</b>
Since ASSM (Automatic segment SPACE management) requires locally managed tablespaces, these values will be used at object creations time only and not after that.  That is, Oracle will take:

initial + function(next,pctincrease,minexents)

to figure out how much space would have been allocated in a dictionary managed tablespace initiall and allocate at least that much space for the segment.  I would call these settings "obsolete", either do not use them or use:

initial 1k next 1k minextents 1 pctincrease 0

to get "just allocate the first extent when you create the segment" behaviour.  Note that maxextents is going to be UNLIMITED regardless (except for rollback segments which cap out at 32k extents.....



Then there is
<b>
freelists, freelists groups
</b>

these do not apply to ASSM, ASSM obsoletes freelist management of space and uses bitmaps in the segment itself.

<b>
optimal
</b>
only applies to rbs's, doesn't count here....

<b>
buffer_pool
</b>

still applies.


So basically, you are left with -- buffer_pool.


Now, many people associate physical storage characterstics here as well:
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses8a.htm#77585 <code>

pctfree

is used with ASSM, but

pctused

is not.

Initrans, maxtrans

are definitely used.


so,

buffer_pool, pctfree, initrans, maxtrans


are what you are left with.

ASSM

April 14, 2005 - 9:13 am UTC

Reviewer: Parag J Patankar from India

Hi Tom,

Thanks for giving vey good answer ( as usual ) to my question in this thread. 

But

  1  select TABLESPACE_NAME, SEGMENT_SPACE_MANAGEMENT
  2* from user_tablespaces
19:03:14 SQL> /

TABLESPACE_NAME                SEGMEN
------------------------------ ------
ATLAS_D01                      AUTO
ATLAS_I01                      AUTO

  1* create table t ( a number, b varchar2(10))
18:07:06 SQL> /

Table created.
18:07:06 SQL> set long 500000
18:07:16 SQL> select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "ATLAS"."T"
   (    "A" NUMBER,
        "B" VARCHAR2(10)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "ATLAS_D01"

So my question is why dbms_metadata is showing pctused, freelist, freelist groups storage parameters showned when these parameter not applied to LMT automatic storage parameters ?

regards & thanks
pjp 

Tom Kyte

Followup  

April 14, 2005 - 9:17 am UTC

it is just giving you ddl, those settings are used (or not) as described above. in ASSM

initial, next, minextents, maxextents, pctincrease -- are used only at create time to figure out HOW MUCH SPACE to allocate.

freelists, freelist groups have no meaning in ASSM.

buffer_pool does.

Pctfree is used, but pctused it not. initrans, maxtrans, nocompress, logging ARE used.


They are all valid, they just have different meanings when applied to different tablespace types.

Use of dbms_metadata

May 05, 2005 - 11:09 am UTC

Reviewer: andrade from italy

Sorry for disturbing. I'd like to know how can i use dbms_metadata.get_ddl( 'TABLE', 'RPBGUIDACNS',) not extracting the owner of the table.
Which is the right syntax?
I've tryed using set filter from sqlplus, but it didn't work.

Tom Kyte

Followup  

May 05, 2005 - 11:23 am UTC

if the resulting ddl is 32k or less, you can use replace( ddl, '"SCHEMA".','') in plsql

there is no filter to remove the schema that I'm aware of

Thanks Kashif

May 05, 2005 - 6:27 pm UTC

Reviewer: Eric from Chicago, IL

This has been a helpful thread. I have implemented a script a la kashif where I extract all the grants on a given schema. However I get an error when i try to get ROLE_GRANTS

ERROR:
ORA-31608: specified object of type ROLE_GRANT not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 631
ORA-06512: at "SYS.DBMS_METADATA", line 1339
ORA-06512: at line 1

using the following:

select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',upper('&1')) from dual;

I understand that this means that there are no role grants, however i would like to use this as a general script that can be run on any schema. That ugly error message messes up my spool file for some of my schemas. Is there any way to suppress error messages in SQLPlus spooling? Alternatively is there a way to test if role grants exist before trying to get DDL for them?

Thanks, great site.


Tom Kyte

Followup  

May 05, 2005 - 6:51 pm UTC

ops$tkyte@ORA9IR2> variable x refcursor
ops$tkyte@ORA9IR2> declare
  2          l_clob clob;
  3          no_rgs exception;
  4          pragma exception_init( no_rgs, -31608 );
  5  begin
  6    select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'T' )
  7      into l_clob
  8      from dual;
  9          open :x for
 10           select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'noone' )
 11                     from dual;
 12  exception
 13    when no_rgs
 14    then
 15          open :x for select ' ' from dual;
 16  end;
 17  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> print x
 
 
 
ops$tkyte@ORA9IR2> grant select on t to connect;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> declare
  2          l_clob clob;
  3          no_rgs exception;
  4          pragma exception_init( no_rgs, -31608 );
  5  begin
  6    select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', user )
  7      into l_clob
  8      from dual;
  9          open :x for
 10           select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', user )
 11                     from dual;
 12  exception
 13    when no_rgs
 14    then
 15          open :x for select ' ' from dual;
 16  end;
 17  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> print x
 
 
   GRANT "CONNECT" TO "OPS$TKYTE"
 
   GRANT "DBA" TO "OPS$TKYTE"

is one way.
 

DDL Window wizard Software

May 23, 2005 - 2:47 am UTC

Reviewer: A reader

Hi Tom & All,
My Senior found one good free software to generate DDL from export DMP file.(Export has to be taken as ROWS=N)

It is really fantastic and very helpful for collecting DDL from production envirounment. Have a look on this site. Its totally free!!!

</code> http://www.ddlwizard.com/dt_dispcode.htm <code>

Thanks,





Tom Kyte

Followup  

May 23, 2005 - 10:24 am UTC

looks very nice

I know I am pushing it rather too far !!

June 28, 2005 - 1:57 pm UTC

Reviewer: A reader from NJ, USA

Is there a way to get "CREATE USER" ddl along with its granted privs ? I get a lot of requests to create a user "like" a certain user as in a certain database. Over a period of time, the request which was specified with all the specifications is not found. Just wanted to know, if there was a quick/dirty/easy way of getting the script without jumping thro' hoops !

Tom Kyte

Followup  

June 28, 2005 - 2:29 pm UTC

enterprise manager has such a utility in it.


but...

ops$tkyte@ORA10G> select dbms_metadata.get_ddl( 'USER', 'SCOTT' ) from dual;
 
DBMS_METADATA.GET_DDL('USER','SCOTT')
-------------------------------------------------------------------------------
 
   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'F894844C34402B67'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
 
 
 
ops$tkyte@ORA10G> select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SCOTT' ) from dual;
 
DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
-------------------------------------------------------------------------------
 
  GRANT UNLIMITED TABLESPACE TO "SCOTT"
 
 
 
ops$tkyte@ORA10G> select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SCOTT' ) from dual;
 
DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT')
-------------------------------------------------------------------------------
 
   GRANT "CONNECT" TO "SCOTT"
 
   GRANT "RESOURCE" TO "SCOTT"
 
   GRANT "DBA" TO "SCOTT"
 
 

ops$tkyte@ORA10G> select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SCOTT' ) from dual;
 
DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')
-------------------------------------------------------------------------------
 
  GRANT SELECT ON "OPS$TKYTE"."T" TO "SCOTT"


(the last three raise exceptions if there are no grants of that type) 

Worked like a breeze !!!

June 29, 2005 - 10:27 am UTC

Reviewer: A reader from NJ, USA

The above followup along with a couple of cool formatting tips from this same thread worked like a breeze and I have now create a generic script to generate create user script for any user entered as an input variable.

Also, used

exec dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE );

to have the script put semicolons automatically.

Thankyou for making our jobs easier !!!



Recreate Constraints

August 03, 2005 - 2:39 am UTC

Reviewer: Rory from Philippines

Hi Tom,

What is the best of recreating all my foreign key constraints in ONE schema. Thet schema has a lot of table and a lot of foreign key constraints. And if I use dbms_metadata.get_ddl, I'd be manually editing everything. The reason for this is because I'll be including the "ON DELETE CASCADE" in the re-creation of the foreign key constraints.
Thanks a lot Tom.

Tom Kyte

Followup  

August 03, 2005 - 10:40 am UTC

well, you can use dbms_metadata in a cursor for loop, get the constraint, and use REPLACE on it to add that and then drop it and add it again (PLSQL can be a pretty good scripting language too)


Here is an old script I had to create fkeys given a parent table name:

column fkey format a80 word_wrapped
select
'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ');' fkey
from
( select a.table_name child_tname, a.constraint_name child_cons_name,
b.r_constraint_name parent_cons_name,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name, b.r_constraint_name ) child,
( select a.constraint_name parent_cons_name, a.table_name parent_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = upper('&1')
/
undefine 1


You could run that (modify it to add the on delete cascade) for each parent - spool it and then you'd have the create.

Extract DDL

August 05, 2005 - 1:07 am UTC

Reviewer: Rory from Philippines

Thanks a lot for the script Tom. I'll try it out.

dbms_metadata, materialized views based on remote tables and ORA-31603

August 11, 2005 - 10:29 am UTC

Reviewer: A reader

Hi

I am having a problem with some MV based on remote tables, when ever I run dbms_metadata against these MVs I get ORA-31603

select dbms_metadata.get_ddl('MATERIALIZED_VIEW', mview_name) kk
from user_mviews
where mview_name in (
'FP_MV_ACTIVIDAD', 'FP_MV_MAESTRO_PRODUCTOS', 'FP_MV_TRANSACC_CABECERA_MES',
'TRANS_MV_TRANSACC_CABECERA'
)

All 4 exists but FP_MV_TRANSACC_CABECERA_MES and TRANS_MV_TRANSACC_CABECERA which are based on remote tables fails with dbms_metadata

select mview_name, master_link
from user_mviews
where mview_name in (
'FP_MV_ACTIVIDAD', 'FP_MV_MAESTRO_PRODUCTOS', 'FP_MV_TRANSACC_CABECERA_MES',
'TRANS_MV_TRANSACC_CABECERA'
)


MVIEW_NAME MASTER_LINK
------------------------------ --------------------
FP_MV_ACTIVIDAD
FP_MV_MAESTRO_PRODUCTOS
FP_MV_TRANSACC_CABECERA_MES @OP
TRANS_MV_TRANSACC_CABECERA @OP

Is this a bug??

Tom Kyte

Followup  

August 11, 2005 - 6:21 pm UTC

Not sure, you'd want to work that with support.

Sorry, I may be asking on a wrong thead, ORA-0603 ORACLE server session terminated by fatal error

August 12, 2005 - 9:17 am UTC

Reviewer: Suvendu from Bangalore , INDIA

Hi Tom,
For following issue, Oracle Support suggeting for patches 9.2.0.3 / 9.2.0.4, but I already applied PATCHES 9.2.0.6, still in this error while creating a composite index on partition table with 82 million record on 6 partions.

The statement was like so:
create index idx_pos_fact_curr_comp on Partion_tab_temp
(GEOG_ID, TIME_ID, PROD_ID, CHANNEL_ID)
nologging
compress 3
parallel 4
tablespace idx02

and trace file contents:
Ioctl ASYNC_CONFIG error, errno = 1
*** SESSION ID:(35.1427) 2005-08-12 05:48:13.735
*** 2005-08-12 05:48:13.735
ksedmp: internal or fatal error
ORA-01114: IO error writing block to file 203 (block # 138354)
ORA-27072: skgfdisp: I/O error
Additional information: 138354
ORA-01114: IO error writing block to file 203 (block # 138354)
...
....
...Dump of memory from 0x6000000000488500 to 0x6000000000488508
6000000000488500 00000000 [....]
struct _xsoqpga * xsoqpga_ [6000000000488508, 6000000000488510) = 00000000 ...
Dump of memory from 0x6000000000488508 to 0x6000000000488510
6000000000488500 00000000 [....]
word ksmpgl_ [60000000004886C8, 60000000004886CC) = 00000000
----- Dump of the Fixed SGA -----
***** SGA not mapped *****
----- Dump of the Fixed UGA -----
***** no UGA *****
KSTDUMP: SGA no longer mapped

Where Individual BITMAP index creation is possible on each column.


Can you help regarding this.

Regards,
Suvendu


Tom Kyte

Followup  

August 12, 2005 - 9:30 am UTC

support -- use it, that is what they are there for.

Use Toad

August 12, 2005 - 10:29 am UTC

Reviewer: A reader

Does all reverse DDL for you.

Tom Kyte

Followup  

August 13, 2005 - 8:53 am UTC

or just select dbms_metadata.get_dll( ) from dual of course.

check this out

August 12, 2005 - 11:00 am UTC

Reviewer: A reader

I wrote it for you. but be careful, read all the setup info...


</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7038453003289#33182999921302 <code>


hope this helps..


run ddl without error messages.

September 06, 2005 - 5:55 pm UTC

Reviewer: Sean from NJ, USA

Hi Tom,

If I run 'create table t1(c1 number)' script while table exists already, I get the error message. Is there any way to use the script to check whether table exists or not? It the table exists, drop it and create new one. If not, don't run drop table script and simply run create table script.

I understand that one can always run drop table script and ignore the error if the table does not exist, but our client does not like error message even though it is harmless.


Thanks so much for your help.


Tom Kyte

Followup  

September 06, 2005 - 9:19 pm UTC

yes, you can use plsql to execute immediate sql, so you can catch exceptions and ignore those that you "expect"

Caution on TOAD and other tools

September 07, 2005 - 10:06 am UTC

Reviewer: Duke Ganote from THE FAR EAST (side of Cincinnati)

TOAD and other such tools typically do a QND (quick and dirty) job of generating DDL. If you're fastidious (for example like named constraints) these tools are frustratingly inadequate; see examples at </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:14688016228501#19679633683960 <code>
Tom's right: DBMS_METADATA is far better.

Moving longs to another tablespace

September 07, 2005 - 3:04 pm UTC

Reviewer: Mohini from Germany

9i (Release 2)
Tom,
I am in the peocess of creating a script to move a table that has a long column to another tablespace..
Export/Import is one method..But I just wanted to create a script..since we have bunch of these in lots of off-the-shelf apps....in bunch of databases...
This is becoming a "very long" task for me...
Do you have something up your sleeves..or is there a better way to accomplish this..

Thanks


Tom Kyte

Followup  

September 07, 2005 - 3:43 pm UTC

how long are the longest longs?

</code> http://asktom.oracle.com/Misc/MoveLongs.html <code>
(might work, might not, sort of deprecated -- VALIDATE the data after moving)

Moving longs to another tablespace

September 07, 2005 - 5:08 pm UTC

Reviewer: mohini from Germany

"How long are Longs"
It is just longs..not long Raws..
I am trying to move the table (with a long columns) from one tablespace to another..
will this copy command (from your link) do this?

Thanks...

Tom Kyte

Followup  

September 08, 2005 - 7:45 am UTC

do you have an idea how LONG (length) these longs are. are they storing 32k and less? if so, we can use plsql. if not, you have to use

o sqlplus copy command as demonstrated in the link, but TEST and VERIFY
o export/import
o custom written program.

32k long

September 08, 2005 - 11:41 am UTC

Reviewer: mohini from germany

9i (release 2)
We have both (some more than 32 k..some less)..
So as I understand..for anything less than 32k...there can be a script...Do you have a script handy for this..
and for the ones more than 32k:
Copy command
or
exp/imp
or
custom code

I was looking at the copy command syntax..it does not have any tablespace clause...how does it move the tablespace of the table:

COPY Command Syntax

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

where database has the following syntax:

username[/password]@connect_identifier

Copies data from a query to a table in a local or remote database. COPY supports the following datatypes:

CHAR
DATE
LONG
NUMBER
VARCHAR2

Thanks.

Tom Kyte

Followup  

September 08, 2005 - 4:03 pm UTC

the script is simply:

for x in ( select * from t )
loop
insert into another_t values ( ..... );
end loop;

plsql can deal with 32k of long data.


You can pre-create the table anywhere you like, you don't have to use create, use insert.

How about trigers?

October 03, 2005 - 11:15 am UTC

Reviewer: Steve from NYC, USA


I am using the way yuo suggested

exp userid=/ owner=some_schema
imp userid=/ indexfile=foo.sql

But the indexfile doesn't contain the definition of triggers? How can I get that?

Thanks!


Tom Kyte

Followup  

October 03, 2005 - 8:15 pm UTC

code does not come out with that. just gets "segments"


show=yes would get that but there are wrapping problems.

October 27, 2005 - 4:00 pm UTC

Reviewer: mg

Hi tom,

I have problem with index usage by table.

After I import dump file, query doesn't use my indexes.
But indexes are existing with Valid state.

I have Done:
------------
(1). Rebuild the indexes, then execute the query. Still it is not using.

(2).recreate the indexes, still query is not using the indexes.

(3). So finally I drop and recreate the tables, then query use the indexes perfectly.

For testing:
-----------
I have again import the dump, still got the same problem.

Do you whats wrong with my system. I have oracle 9.2.0.1 version on Windows 200 ( advanced server)

thank you




Tom Kyte

Followup  

October 28, 2005 - 1:40 am UTC

sounds like you are missing statistics perhaps.

what is an autotrace traceonly explain showing you after the IMPORT - are the card= values even close to reality.

How to get dbms_metadata.get_ddl from a jdbc?

December 13, 2005 - 7:18 pm UTC

Reviewer: Yong Wu from USA

I tried to get ddl from a jdbc code below
try {
Connection conn0 = dbts.getConnection();
Connection conn = getDatabaseConnection(conn0,sid);
conn0.close();
String sql="select dbms_metadata.get_ddl(?,?,?) from dual";
PreparedStatement stmt=conn.prepareStatement(sql);
stmt.setString(1,objectType);
stmt.setString(2,objectName);
stmt.setString(3,userName);
ResultSet rset = stmt.executeQuery ();
while (rset.next()) {
CLOB clob = ((OracleResultSet)rset).getCLOB(1);
out.println(dumpClob (conn, clob));
}
rset.close();
conn.close();
} catch (SQLException e) { out.println("Error :" + e ); }

static String dumpClob(Connection conn, CLOB clob) throws Exception
{
Reader instream = clob.getCharacterStream();
char[] buffer = new char[10];
int length = 0;
StringBuffer s=null;
while ((length = instream.read(buffer)) != -1)
{
for (int i=0; i<length; i++)
s.append(buffer[i]);
}
instream.close();
return s.toString();
}

I got this error

Error :java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error ORA-31605: the following was returned from LpxXSLResetAllVars in routine kuxslResetParams: LPX-1: NULL pointer ORA-22921: length of input buffer is smaller than amount requested ORA-06512: at "SYS.UTL_XML", line 0 ORA-06512: at "SYS.DBMS_METADATA_INT", line 3320 ORA-06512: at "SYS.DBMS_METADATA_INT", line 4148 ORA-06512: at "SYS.DBMS_METADATA", line 458 ORA-06512: at "SYS.DBMS_METADATA", line 615 ORA-06512: at "SYS.DBMS_METADATA", line 1221 ORA-06512: at line 1

I actually tried many different ways and alwaysg get this error. Does any one have done some thing like this?

Tom Kyte

Followup  

December 13, 2005 - 7:32 pm UTC

first - does it work for you in sqlplus (simplify)

second - do you have a complete example (eg: with main() calling this, using scott.emp for example?

Privileges needed for successful DBMS_METADATA.GET_DDL

December 14, 2005 - 7:28 am UTC

Reviewer: Mike from Cleveland, OH USA

What privileges are needed to get the DDL? You mention previously that it will not work for 'tables you are not allowed to see'. 
SQL> 
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> --
SQL> -- privs available
SQL> --
SQL> select * from sys.user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
ABCDEFGHI                      UNLIMITED TABLESPACE                     NO
ABCDEFGHI                      SELECT ANY DICTIONARY                    NO
ABCDEFGHI                      SELECT ANY TABLE                         NO

SQL> select * from sys.user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ABCDEFGHI                      CONNECT                        NO  YES NO
ABCDEFGHI                      RESOURCE                       NO  YES NO

SQL> select * from sys.dba_sys_privs
  2    where grantee in (select granted_role from sys.user_role_privs);

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO

9 rows selected.

SQL> select * from sys.user_tab_privs 
  2    where owner = 'MTRSPROD' 
  3    and   table_name = 'WHL_BRWR_TB';

GRANTEE                        OWNER                          TABLE_NAME                     GRANTOR            
------------------------------ ------------------------------ ------------------------------ -------
ABCDEFGHI                      MTRSPROD                       WHL_BRWR_TB                    MTRSPROD           

SQL> --
SQL> -- Verify the object type
SQL> --
SQL> select owner, object_type, object_name
  2      from sys.dba_objects
  3      where object_name = 'WHL_BRWR_TB'
  4      and owner = 'MTRSPROD';

OWNER                          OBJECT_TYPE         OBJECT_NAME
------------------------------ ------------------- -------------------------------------------------
MTRSPROD                       TABLE               WHL_BRWR_TB

SQL> --
SQL> -- Try to extract the DDL 
SQL> --
SQL> select DBMS_METADATA.GET_DDL ('TABLE', 'WHL_BRWR_TB', 'MTRSPROD') from dual;
ERROR:
ORA-31603: object "WHL_BRWR_TB" of type TABLE not found in schema "MTRSPROD"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



no rows selected

SQL> 

This same call is successful if I login as the schema owner. 
I get similar results in a 9.2 database.  

Tom Kyte

Followup  

December 14, 2005 - 8:45 am UTC

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#sthref4208 <code>

Privileges

December 14, 2005 - 9:57 am UTC

Reviewer: Mike from Cleveland, OH USA

Thanks, this is exactly what I needed.

December 14, 2005 - 1:49 pm UTC

Reviewer: Yong Wu

it seems dbms_metadata has the problem not the java code.

SQL> select dbms_metadata.get_ddl('TABLE','USERS','CORPDBA') from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-22921: length of input buffer is smaller than amount requested
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3320
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4148
ORA-06512: at "SYS.DBMS_METADATA", line 458
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

I connected to sys and recompile dbms_metadata package without error. But I still got the same error. How to fix the package issue? 

Tom Kyte

Followup  

December 14, 2005 - 2:23 pm UTC

database character set and version is (down to four digits please)

December 15, 2005 - 1:37 pm UTC

Reviewer: Yong Wu from USA

Charater set is UTF8, database version is 9.2.0.5.

I found many of our databases have the same issue. Most of our databases were created with some scripts. Not sure whether the scripts are missing something or parameters set up causes the problem. I searched on metalink and didn't find the solution.

Tom Kyte

Followup  

December 15, 2005 - 2:46 pm UTC

contact support, they'll be able to look that on up easily - likely "9206 please" to fix it.

December 15, 2005 - 6:17 pm UTC

Reviewer: Yong Wu from USA

It is a bug and need a patch. thanks for all your help.

January 12, 2006 - 3:12 pm UTC

Reviewer: Su Baba from Ca, USA

What privilege(s) is needed for dbms_metadata.get_ddl to work?

SQL> connect pv/pv@C12XD1
Connected.
SQL> 
SQL> CREATE TABLE tab1 (col1 NUMBER);

Table created.

SQL> 
SQL> CREATE INDEX tab1_ind ON tab1 (col1);

Index created.

SQL> 
SQL> GRANT select, update, delete, index, alter, references, debug,
  2        flashback, on commit refresh, query rewrite, insert
  3  ON    tab1 TO apps;

Grant succeeded.

SQL> 
SQL> 
SQL> connect apps/apps@c12xd1
Connected.
SQL> SELECT dbms_metadata.get_ddl('INDEX', 'TAB1_IND', 'PV')
  2  FROM   dual;
ERROR:
ORA-31603: object "TAB1_IND" of type INDEX not found in schema "PV"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



no rows selected

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

Tom Kyte

Followup  

January 13, 2006 - 10:22 am UTC

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1016867 <code>


it explains how this all works.

January 13, 2006 - 12:19 pm UTC

Reviewer: Su Baba from CA, USA

Even though apps user has SELECT_CATALOG ROLE privilege and access rights to tab1 table, dbms_metadata.get_ddl still returned an error. I went through the Security Model section you mentioned above several times but still can't get it to work.


SQL> SELECT granted_role, default_role
  2  FROM   dba_role_privs
  3  WHERE  grantee = 'APPS'
  4  ORDER  BY 1;

GRANTED_ROLE                   DEF
------------------------------ ---
AQ_ADMINISTRATOR_ROLE          YES
CONNECT                        YES
CTXAPP                         YES
HR_REPORTING_USER              YES
HR_SECURE_USER                 YES
JAVASYSPRIV                    YES
OWS_STANDARD_ROLE              YES
RESOURCE                       YES
SELECT_CATALOG_ROLE            YES
WEBDB_DEVELOPER                YES

10 rows selected.

SQL> SELECT grantee, owner, table_name, privilege, grantor
  2  FROM   dba_tab_privs
  3  WHERE  table_name IN ('TAB1')
  4  ORDER  BY table_name, privilege;

GRANTEE    OWN TABLE_NAME                PRIVILEGE            GRANTOR
---------- --- ------------------------- -------------------- ----------
APPS       PV  TAB1                      ALTER                PV
APPS       PV  TAB1                      DEBUG                PV
APPS       PV  TAB1                      DELETE               PV
APPS       PV  TAB1                      FLASHBACK            PV
APPS       PV  TAB1                      INDEX                PV
APPS       PV  TAB1                      INSERT               PV
APPS       PV  TAB1                      ON COMMIT REFRESH    PV
APPS       PV  TAB1                      QUERY REWRITE        PV
APPS       PV  TAB1                      REFERENCES           PV
APPS       PV  TAB1                      SELECT               PV
APPS       PV  TAB1                      UPDATE               PV

11 rows selected.

SQL> SELECT dbms_metadata.get_ddl('TABLE', 'TAB1', 'PV')
  2  FROM   dual;
ERROR:
ORA-31603: object "TAB1" of type TABLE not found in schema "PV"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1



no rows selected

SQL> show user
USER is "APPS"
SQL>  

Tom Kyte

Followup  

January 13, 2006 - 12:27 pm UTC

I cannot reproduce, if you do the following script, what happens for you?


ops$tkyte@ORA10GR2> drop user a cascade;

User dropped.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> grant create session to a identified by a;

Grant succeeded.

ops$tkyte@ORA10GR2> grant select_catalog_role to a;

Grant succeeded.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> drop table t;

Table dropped.

ops$tkyte@ORA10GR2> create table t ( x int );

Table created.

ops$tkyte@ORA10GR2> create index t_idx on t(x);

Index created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> @connect a/a
ops$tkyte@ORA10GR2> set termout off
a@ORA10GR2> @login
a@ORA10GR2> set termout off
a@ORA10GR2>
a@ORA10GR2> set termout on
a@ORA10GR2>
a@ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T', 'OPS$TKYTE' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T','OPS$TKYTE')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0)
   ) 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 "MANUAL"



a@ORA10GR2> select dbms_metadata.get_ddl( 'INDEX', 'T_IDX', 'OPS$TKYTE' ) from dual;

DBMS_METADATA.GET_DDL('INDEX','T_IDX','OPS$TKYTE')
-------------------------------------------------------------------------------

  CREATE INDEX "OPS$TKYTE"."T_IDX" ON "OPS$TKYTE"."T" ("X")
  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 "MANUAL"



a@ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

a@ORA10GR2> select granted_role from dba_role_privs where grantee = user;

GRANTED_ROLE
------------------------------
SELECT_CATALOG_ROLE

a@ORA10GR2> select * from dba_tab_privs where table_name = 'T' and owner = 'OPS$TKYTE';

no rows selected

a@ORA10GR2> select granted_role from dba_role_privs where grantee = 'PUBLIC';

no rows selected

 

dbms_metadata and quota-clause

February 22, 2006 - 10:07 am UTC

Reviewer: Sokrates from Kaiserslautern, Germany

Hallo Tom,

why doesn't dbms_metadata here drop a clause in
the statement ? 

SQL> create user quota identified by values 'quota'   
  2  default tablespace users
  3  temporary tablespace temp
  4  quota 2048M on users;

User created.

SQL> select dbms_metadata.get_ddl('USER', 'QUOTA')
  2  from dual;

DBMS_METADATA.GET_DDL('USER','QUOTA')
--------------------------------------------------------------------------------

   CREATE USER "QUOTA" IDENTIFIED BY VALUES 'quota'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


ups - "quota 2048M on users" missing - why ?
 

Tom Kyte

Followup  

February 22, 2006 - 10:49 am UTC

create user quota identified by values 'quota'
default tablespace users
temporary tablespace tempnew
quota 2048M on users;

select dbms_metadata.get_ddl('USER', 'QUOTA')
from dual;
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', 'QUOTA')
from dual;



it is considered "granted" things.

"why doesn't"

February 22, 2006 - 10:08 am UTC

Reviewer: A reader

should of course read
"why does"

OK

March 06, 2006 - 8:14 am UTC

Reviewer: Raj from Pune,India

Hi,
How to get the DDL of "Create Database Statement" ??

??

March 09, 2006 - 2:40 am UTC

Reviewer: Raj

Hi Tom,

Are you not there??

How to get the DDL of "Create database" sql Statement???

Tom Kyte

Followup  

March 09, 2006 - 1:13 pm UTC

umm, you are right, I'm not "here"

create database;

does it (literally!). Use DBCA, that'll create create database scripts from cradle to grave.

March 29, 2006 - 6:46 am UTC

Reviewer: Parag Jayant Patankar from India

Hi Tom

I am trying to use get_dependent_ddl in 9.2 version, when I am using it I am getting following error

16:19:52 SQL> select dbms_metadata.GET_DEPENDENT_DDL('TABLE', 'TDF31') from dual;
ERROR:
ORA-31600: invalid input value BASE_OBJECT_NAME for parameter NAME in function
SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 2050
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4067
ORA-06512: at "SYS.DBMS_METADATA", line 670
ORA-06512: at "SYS.DBMS_METADATA", line 583
ORA-06512: at "SYS.DBMS_METADATA", line 1282
ORA-06512: at line 1



no rows selected

16:24:00 SQL> select dbms_metadata.GET_ddl('TABLE', 'TDF31') from dual;

DBMS_METADATA.GET_DDL('TABLE','TDF31')
--------------------------------------------------------------------------------

  CREATE TABLE "INFOCD"."TDF31"
   (    "A0020" NUMBER(9,0) NOT NULL ENABLE,
        "A

..
..

Kindly guide me where I have gone wrong. 

thanks & regards
pjp 

Tom Kyte

Followup  

March 29, 2006 - 7:22 am UTC

a 'TABLE' is not dependent ddl,

</code> http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#sthref4278 <code>

has a table of object types - ones demarked with 'D' for depenent ddl types would be valid here. REF_CONSTRAINT for example.

a table is the top level DDL, referential integrity constraints would be dependent ddl (dependent on the table(s) )

Extracting all DDLS of a schema

March 31, 2006 - 1:13 am UTC

Reviewer: Parag Jayant Patankar from Idnai

Hi Tom,

I am extacting all DDLs in Oracle 9.2 database by

select dbms_metadata.get_ddl(OBJECT_TYPE, OBJECT_NAME)
from user_objects
/
...
....
i.e extracting required objects DDL using data dictionary.

Which is little time consuming. Just curious,to know any other "faster" way to extract DDL details. for e.g. dbms_dependend_ddl can be faster ? may be use "indexfile" for tables and indexes and get_ddl for other than tables and views. Kindly let me know which way you will prefer to go.

pl let me know.

thanks & regards
pjp



Tom Kyte

Followup  

March 31, 2006 - 12:08 pm UTC

what you see it what you get.

seems strange to have to constantly do something for what you should already have (eg: the constant need to extract the schema ddl indicates to me that, well, you have no source code control for your project).

Need more information - what about partitioned indexes?

June 22, 2006 - 3:29 pm UTC

Reviewer: Srinivas Narashimalu from Atlanta,GA

Hi Tom,

Thanks a million on behalf of the Oracle community for the excellent HELP and knowledge sharing!!!

I want to get the ddl used for creating indexes that are range partitioned+list partitioned. I tried dbms_metadata.get_ddl and indexfile (exp/imp) methods but these methods are not giving the complete ddl that would have been used. 

Please have a look at the script that I am able to generate using either of the methods -

CREATE  bitmap INDEX "WHSE"."INV_YR_MON_BMNDX" ON
  "WHSE"."PART_ORDER_LINE_FACT"
    ("INVOICE_YEAR", "INVOICE_MONTH")
 LOCAL
 (
  PARTITION "P1998"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging,
  PARTITION "P1999"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging,
  PARTITION "P2000"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging,
  PARTITION "P2001"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging,
  PARTITION "P2002"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging,
  PARTITION "P2003"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging,
  PARTITION "P2004"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging,
  PARTITION "P2005"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging,
  PARTITION "P2006"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging,
  PARTITION "PMAX"
  STORAGE (
             FREELISTS 1
             FREELIST GROUPS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  TABLESPACE "INDX_08M"
  nologging
)
  STORAGE (
             FREELISTS 1
          )
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  NOCOMPRESS
  nologging
  TABLESPACE "INDX_08M"
  NOPARALLEL;

There is no information about the kind of partitioning and nothing about the sub-partitions.

SQL> select INDEX_NAME,PARTITIONING_TYPE,SUBPARTITIONING_TYPE,PARTITION_COUNT,DEF_SUBPARTITION_COUNT from dba_part_indexes;

INDEX_NAME                     PARTITI SUBPART PARTITION_COUNT DEF_SUBPARTITION_COUNT
------------------------------ ------- ------- --------------- ----------------------
LOGMNRC_GSII_PK                RANGE   NONE                  1                      0
LOGMNRC_GTCS_PK                RANGE   NONE                  1                      0
LOGMNRC_GTLO_PK                RANGE   NONE                  1                      0
LOGMNRC_I3GTLO                 RANGE   NONE                  1                      0
LOGMNR_I1ATTRCOL$              RANGE   NONE                  1                      0
LOGMNR_I1CCOL$                 RANGE   NONE                  1                      0
LOGMNR_I1CDEF$                 RANGE   NONE                  1                      0
LOGMNR_I1COL$                  RANGE   NONE                  1                      0
LOGMNR_I1COLTYPE$              RANGE   NONE                  1                      0
LOGMNR_I1ICOL$                 RANGE   NONE                  1                      0
LOGMNR_I1IND$                  RANGE   NONE                  1                      0
LOGMNR_I1INDCOMPART$           RANGE   NONE                  1                      0
LOGMNR_I1INDPART$              RANGE   NONE                  1                      0
LOGMNR_I1INDSUBPART$           RANGE   NONE                  1                      0
LOGMNR_I1LOB$                  RANGE   NONE                  1                      0
LOGMNR_I1LOBFRAG$              RANGE   NONE                  1                      0
LOGMNR_I1OBJ$                  RANGE   NONE                  1                      0
LOGMNR_I1TAB$                  RANGE   NONE                  1                      0
LOGMNR_I1TABCOMPART$           RANGE   NONE                  1                      0
LOGMNR_I1TABPART$              RANGE   NONE                  1                      0
LOGMNR_I1TABSUBPART$           RANGE   NONE                  1                      0
LOGMNR_I1TS$                   RANGE   NONE                  1                      0
LOGMNR_I1TYPE$                 RANGE   NONE                  1                      0
LOGMNR_I1USER$                 RANGE   NONE                  1                      0
LOGMNR_I2COL$                  RANGE   NONE                  1                      0
INVDATEKEY_BMNDX               RANGE   LIST                 10                      1
INV_YR_MON_BMNDX               RANGE   LIST                 10                      1
SUPPLIER_BMNDX                 RANGE   LIST                 10                      1

28 rows selected.

This is a 9.2.0.7 database on AIX 5.2. Please let me know if there is any way I can get the ddl script. (These scripts for some reason is neither available with the previous DBAs nor with the developers!)

Thanks,
Srinivas 

Tom Kyte

Followup  

June 22, 2006 - 4:20 pm UTC

please give us a simple example, like this


ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  PARTITION BY RANGE (dt)
  8  subpartition by hash(x) subpartitions 2
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  /

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create bitmap index bm_idx on t(x) local;

Index created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select dbms_metadata.get_ddl( 'INDEX', 'BM_IDX') from dual;

DBMS_METADATA.GET_DDL('INDEX','BM_IDX')
-------------------------------------------------------------------------------

  CREATE BITMAP INDEX "OPS$TKYTE"."BM_IDX" ON "OPS$TKYTE"."T" ("X")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)LOCAL
 (PARTITION "PART1"
PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
 ( SUBPARTITION "SYS_SUBP49"  TABLESPACE "USERS",
  SUBPARTITION "SYS_SUBP50"  TABLESPACE "USERS") ,
 PARTITION "PART2"
PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
 ( SUBPARTITION "SYS_SUBP51"  TABLESPACE "USERS",
  SUBPARTITION "SYS_SUBP52"  TABLESPACE "USERS") ,
 PARTITION "JUNK"
PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT)
 ( SUBPARTITION "SYS_SUBP53"  TABLESPACE "USERS",
  SUBPARTITION "SYS_SUBP54"  TABLESPACE "USERS") )

 

Just one more clarification, can this script be used to create b-tree index?

June 22, 2006 - 11:12 pm UTC

Reviewer: Srinivas Narashimalu from Atlanta,GA

Thanks very much Tom! I greatly appreciate it!

But can we remove (or add) the "bitmap" keyword and create a b-tree (or bitmap)index keeping the other values as it is in the script?
The reason I am asking this is I am working on a troublesome report now and I feel some of indexes are wronly created, so I want to check for the peformance by changing the index types. And what is your opinion on percentage and histogram number for dbms_stats in general for Datawarehousing (and oltp)?

Thanks much!

Srinivas


Tom Kyte

Followup  

June 23, 2006 - 10:12 am UTC

you can change the statement to make it be a valid statement in any way you like.


if you don't want bitmap, you want btree, modify it appropriately, sure.


histograms make sense on skewed data (mostly low cardinality - 254 or less values - skewed data). You need not get them on all columns.

10% or 100% would be "rules of thumb", but we all know ROT isn't always 100% appropriate.

Thanks much!

June 23, 2006 - 8:41 pm UTC

Reviewer: Srinivas Narashimalu from Atlanta, GA

Thanks very much! The information is very useful!

Thanks,
Srinivas

_bag_

June 26, 2006 - 8:24 am UTC

Reviewer: A reader from Russia

And how can I get comments for a view using dbms_metadata?
For a table it works fine:
dbms_metadata.get_dependent_ddl( 'COMMENT', upper(:p_table), upper(:p_schema));
But for a view the same code failes with
ORA-31608: specified object of type COMMENT not found.

Tom Kyte

Followup  

June 26, 2006 - 8:48 am UTC

example???


ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> create or replace view vw as select * from t;
 
View created.
 
ops$tkyte@ORA9IR2> comment on table t is 'hello';
 
Comment created.
 
ops$tkyte@ORA9IR2> comment on table vw is 'world';
 
Comment created.
 
ops$tkyte@ORA9IR2> select dbms_metadata.get_dependent_ddl( 'COMMENT', 'T' ) from dual;
 
DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT','T')
-------------------------------------------------------------------------------
 
   COMMENT ON TABLE "OPS$TKYTE"."T"  IS 'hello'
 
 
 
ops$tkyte@ORA9IR2> select dbms_metadata.get_dependent_ddl( 'COMMENT', 'VW' ) from dual;
 
DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT','VW')
-------------------------------------------------------------------------------
 
   COMMENT ON TABLE "OPS$TKYTE"."VW"  IS 'world'
 
 

Adding SQL Terminator

September 01, 2006 - 10:24 am UTC

Reviewer: Rod West from Bristol, UK

Hi Tom,

Why is it that some DDL statements are not terminated when using the SQLTERMINATOR transform parameter? For example,

SQL> set long 10000
SQL> create or replace synonym RWTEST for RWTEST1;

Synonym created.

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

PL/SQL procedure successfully completed.

SQL> SELECT dbms_metadata.get_ddl('SYNONYM', 'RWTEST') ddl from dual;

DDL                                                                             
--------------------------------------------------------------------------------
                                                                                
  CREATE SYNONYM "APPS"."RWTEST" FOR "APPS"."RWTEST1"                            

Tom Kyte

Followup  

September 01, 2006 - 10:43 am UTC

ops$tkyte%ORA9IR2> set long 10000
ops$tkyte%ORA9IR2> create or replace synonym RWTEST for RWTEST1;

Synonym created.

ops$tkyte%ORA9IR2> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> SELECT dbms_metadata.get_ddl('SYNONYM', 'RWTEST') ddl from dual;

DDL
-------------------------------------------------------------------------------

  CREATE SYNONYM "OPS$TKYTE"."RWTEST" FOR "OPS$TKYTE"."RWTEST1";



9.2.0.8, 10.2.0.2

worked in both for me. 

Lots of lines in one statement in foo.sql via imported

September 20, 2006 - 12:22 am UTC

Reviewer: Ed from USA

When I export the ddl into foo.sql ,I found there are serveral lines in one SQL sentence, not convenient for the text parsing. Is there any method to combine these lines to one line for each SQL statement in the foo.sql?

And when I use "grep -a "CREATE" export.dmp" , it's OK and the SQL statement is contained in the one line. But I have to use the grep.

LOG:
exp userid=/
imp userid=/ index=foo.sql

TEXT IN THE FOO.SQL:
>vi foo.sql
CREATE TABLE "CLS_COCO_USER"."ANNURL" ("ANNID" NUMBER(10, 0) NOT NULL
ENABLE, "URL1" VARCHAR2(200), "URL2" VARCHAR2(200), "URL3"
VARCHAR2(200), "URL4" VARCHAR2(200), "URL5" VARCHAR2(200), "URL6"
VARCHAR2(200), "URL7" VARCHAR2(200), "URL8" VARCHAR2(200), "URL9"
VARCHAR2(200), "URL10" VARCHAR2(200)) PCTFREE 10 PCTUSED 40 INITRANS
1 MAXTRANS 255 STORAGE(INITIAL 57344 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "CLS_COCO_DATA_TS" LOGGING NOCOMPRESS ;

GREP RESULT:
> grep -a "CREATE TABLE" expdat.dmp | grep ANNURL
CREATE TABLE "ANNURL" ("ANNID" NUMBER(10, 0) NOT NULL ENABLE, "URL1" VARCHAR2(200), "URL2" VARCHAR2(200), "URL3" VARCHAR2(200), "URL4" VARCHAR2(200), "URL5" VARCHAR2(200), "URL6" VARCHAR2(200), "URL7" VARCHAR2(200), "URL8" VARCHAR2(200), "URL9" VARCHAR2(200), "URL10" VARCHAR2(200)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 57344 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "CLS_COCO_DATA_TS" LOGGING NOCOMPRESS


Tom Kyte

Followup  

September 20, 2006 - 2:57 pm UTC

not using exp, no - you would have to write your own sort of filter program with awk or something.

Need your help - dbms_metadata.set_filter

October 19, 2006 - 10:55 am UTC

Reviewer: Nikhil

Hello Tom,
I tried to generate the DDL scripts for the schema using the below plsql scrip. It works fine. But one of the table has 423 columns and Iam getting "ORA-06502: PL/SQL: numeric or value error".

I know you generally would not like anyone sending the whole of code but I would be grateful if you tell how to solve it

Thanks

Here is the code for your reference


SET SERVEROUTPUT ON SIZE 1000000

declare
h_table number;
h_index number;
hx_table number;
hx_index number;


schema varchar2(30);
object_name varchar2(30);
verb varchar2(30);
object_type varchar2(30);

table_ddls sys.ku$_ddls; -- DDL lists for each table
table_ddl sys.ku$_ddl; -- single DDL statement
parsed_items sys.ku$_parsed_items;

---- output files
f_tab utl_file.file_type;
f_ind utl_file.file_type;
f_con utl_file.file_type;
f_fk utl_file.file_type;
f utl_file.file_type;
d number;
begin
f := utl_file.fopen('MYDIR_TMP','tab1.SQL','w',32760); f_tab := f;
f := utl_file.fopen('MYDIR_TMP','ind1.SQL','w',32760); f_ind := f;
f := utl_file.fopen('MYDIR_TMP','con1.SQL','w',32760); f_con := f;
f := utl_file.fopen('MYDIR_TMP','fk1.SQL','w',32760); f_fk := f;

h_table := dbms_metadata.open('TABLE');

-- set items we want to identify in each DDL
dbms_metadata.set_parse_item(h_table,'SCHEMA');
dbms_metadata.set_parse_item(h_table,'NAME');
dbms_metadata.set_parse_item(h_table,'VERB');
dbms_metadata.set_parse_item(h_table,'OBJECT_TYPE');

-- request information to be returned as SQL DDL
hx_table := dbms_metadata.add_transform(h_table,'DDL');
-- 1) suppress tablespace and storage attributes
-- 2) add SQL*Plus terminator to SQL
-- 3) return constraints as separate ALTER TABLE statements
dbms_metadata.set_transform_param(hx_table,'SEGMENT_ATTRIBUTES',FALSE);
dbms_metadata.set_transform_param(hx_table,'SQLTERMINATOR',TRUE);
dbms_metadata.set_transform_param(hx_table,'CONSTRAINTS_AS_ALTER',TRUE);

--For testing (This table has 432 columns)
--dbms_metadata.set_filter(h_table, 'NAME','TITSUBREV_SEP');

loop
table_ddls := dbms_metadata.fetch_ddl(h_table);
exit when table_ddls is null;


--' for each DDL in a single table…
for d in 1..table_ddls.count loop
table_ddl := table_ddls(d);
parsed_items := table_ddl.parseditems;
for i in 1..4 loop -- get parsed items out of DDL
if parsed_items(i).item = 'SCHEMA' then
schema := parsed_items(i).value;
elsif parsed_items(i).item = 'NAME' then
object_name := parsed_items(i).value;
elsif parsed_items(i).item = 'VERB' then
verb := parsed_items(i).value;
elsif parsed_items(i).item = 'OBJECT_TYPE' then
object_type := parsed_items(i).value;
end if;
end loop;


-- table_ddl.ddltext contains DDL.
-- now work out the DDL type, and put in the correct file
if verb='CREATE' and object_type = 'TABLE' then
utl_file.put_line(f_tab,table_ddl.ddltext);
utl_file.fflush(f_tab);
elsif verb='CREATE' and object_type='INDEX' then
utl_file.put_line(f_ind,table_ddl.ddltext);
utl_file.fflush(f_ind);
elsif verb='ALTER' and instr(table_ddl.ddltext,' FOREIGN KEY ') > 0 then
null;
--utl_file.put_line(f_fk,table_ddl.ddltext);
--utl_file.fflush(f_fk);
elsif verb='ALTER' and instr(table_ddl.ddltext,' ADD CONSTRAINT ') > 0 then
null;
--utl_file.put_line(f_con,table_ddl.ddltext);
--utl_file.fflush(f_con);
else
dbms_output.put_line('UNKNOWN SQL:'||table_ddl.ddltext);
end if;
end loop;
end loop;
-- close files
utl_file.fclose(f_tab);
utl_file.fclose(f_ind);
utl_file.fclose(f_fk);
utl_file.fclose(f_con);
end;
/



Tom Kyte

Followup  

October 19, 2006 - 2:09 pm UTC

care to share the line numbers of the code AND the line number you are receiving the error on?

that might be very "telling"

DBMS_METADATA.SET_FILTER

October 19, 2006 - 10:13 pm UTC

Reviewer: Nikhil

Hello Tom,

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 75

Thanks

Tom Kyte

Followup  

October 20, 2006 - 4:10 am UTC

"sorry"?

there was sort of an "AND" involved here:

...
care to share the line numbers of the code
......


what is line 75 of your code.

line 75

June 01, 2007 - 7:49 am UTC

Reviewer: Sokrates

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 75


Elapsed: 00:00:07.76
SQL> l 75
75* utl_file.put_line(f_tab,table_ddl.ddltext);
Tom Kyte

Followup  

June 01, 2007 - 1:00 pm UTC

so, debug it?

Good !

June 04, 2007 - 3:22 pm UTC

Reviewer: A reader

Excellent commentaries

no need to debug

June 05, 2007 - 4:41 am UTC

Reviewer: Sokrates

since it's clear what is wrong
utl_file is a bad idea to use in conjunction with CLOB's
for I/O, better use plain java and enjoy not fighting with PL/SQL-limitations


Tom Kyte

Followup  

June 06, 2007 - 12:56 pm UTC

worse - use java

better - use plsql

32k linesize limits for text files is almost always better than good enough. Java - hard, plsql easy.

Is there a magic????

September 05, 2007 - 9:08 am UTC

Reviewer: karthick from India, Chennai

In a process I have the need to drop index of certain table and do update on that table and again create the index. I have to drop the index because while the update is in progress the index field may have duplicate values but at the end of the update we take care that the values are unique, and the index is a unique index. So once the update is done I need to recreate the index. With 9i I am fine as I have dbms_metadata.get_ddl. With this I get my create index script and I store it then drop the index and create it later. But one of our clients is in 8i so I need to do some thing. So I have the following question.

1. Is it possible to do the update without dropping the index?
2. Or, Is there any thing like disabling an index for a while and enabling it afterwards.
3. Or, Is there an alternative for dbms_metadata which can be used in the middle of a pl/sql.

Note: Avoiding duplicate values in the process of update is not possible at this point, so we need to find a way to make the unique index disappear for a wile and then get it back.

Is there a magic????

Regards,

Karthick.

Tom Kyte

Followup  

September 05, 2007 - 4:20 pm UTC

if the index is currently a unique index - no.

so, when you drop it, do not ADD IT again, instead - add a DEFERRABLE UNIQUE constraint, that'll use a non-unique index and in the future you can set the unique constraint deferred, do your processing and then either commit (and it'll verify) or you can make the constraint immediate to verify.

so, in the future, there is magic - the deferrable constraint.

data dictionary

September 06, 2007 - 12:11 am UTC

Reviewer: martina from vienna, austria

karthick,

you can always get that kind of thing (3) out of the data dictionary ...

regards, martina

Need some super magic

September 06, 2007 - 4:03 am UTC

Reviewer: karthick from india

DEFERRABLE UNIQUE is really cool...

But my problem is...

Our application runs over the copy of people soft production data base which will be send out for testing by the client. Our application masks the sensitive data. So once our application is done with what ever it has to do to the data the structure of the people soft database must be the same as the original.

Hence I can¿t go with that option.

I was searching for some thing like disabling the Index. But I found oracle allows DISABLING only function based index. Tom I can¿t understand to a great extend what the clause UNUSABLE does. Because once I made the index unusable I was not able to perform an insert on that table. I issued the following statement

ALTER INDEX temp_table_idx UNUSABLE;

I was thinking by making the Index UNUSABLE the index will not be considered. And afterwards I can REBUILD the index.

Martina,

I can get lots of procedures from the net and I also can build my own. But the problem is none of these are a certified product of oracle like what they have in 9i dbms_metadata. Hence it¿s difficult for me to trust these procedures.

Regards,

Karthick.



Tom Kyte

Followup  

September 11, 2007 - 7:48 am UTC

you will not get what you are looking for.

you can use deferrable constraints

you can drop the unique index (a unique index cannot be "skipped" like a non-unique can be by setting skip unusable indexes)

that is all.

Removing DDLs

November 21, 2007 - 1:22 am UTC

Reviewer: Vishal Taneja from india

Hi Tom,
My Application is running on oracle 10g Relese-2 .and this
is migration project.and This is Project is written in Pl/sql Server Pages.
and we have to rewrite in .Net and Oracle-10g.
so my question is ->
There is some procedures which are creating some run time tables and indexes.
and based on the input parameter this procedure is creating some fly columns.
so now as per standard we are not allowed for DDLs.
so my question is how can i remove these DDLs from procedures.


Here i am pasting Code ,which 'll defenetly help you to understand my question.


create or replace PROCEDURE prepareTable(projid in number,tsk in pkg_maintenance_dcc.tpars,tmpName out varchar2,maxlevel out number, tbld in pkg_maintenance_dcc.tpars, bombuild in varchar2) IS
stmtCreate varchar2(10000);
stmtCreate2 varchar2(10000);
stmtRepeat varchar2(32000);
stmtIns varchar2(4000);
sqid number;
rw number;
col number;
BEGIN
select report_seq.nextval into sqid from dual;
stmtCreate2 := 'create table srctmpFinal_'||sqid||' (NIVEL number, SKU varchar2(50), PARENT_PART varchar2(30), COMPONENT_PART varchar2(30), DESCRIPTION varchar2(256), CHAIN VARCHAR2(512), CREATIONDATE date, SUBSYSTEM varchar2(50), COMMENTS varchar2(256), PROCURED_FOR_BUILD VARCHAR2(2000), FAB_PUR_FLG varchar2(1),PART_TYPE varchar2(1), TYPE_FLG varchar2(1),COST varchar2(3),qty_extra_1 number,qty_extra_2 number,qty_extra_3 number,qty_extra_4 number,qty_extra_5 number,sw_repeat number';
stmtCreate := 'create table srctmp_'||sqid||' (NIVEL number, SKU varchar2(50), PARENT_PART varchar2(30), COMPONENT_PART varchar2(30), DESCRIPTION varchar2(256), CHAIN VARCHAR2(512), QTY_PER NUMBER, TOTAL_QTY_PER NUMBER, CREATIONDATE date, SUBSYSTEM varchar2(50), COMMENTS varchar2(256), PROCURED_FOR_BUILD VARCHAR2(2000), FAB_PUR_FLG varchar2(1),PART_TYPE varchar2(1), TYPE_FLG varchar2(1), COST varchar2(3), ORD NUMBER,qty_extra_1 number,qty_extra_2 number,qty_extra_3 number,qty_extra_4 number,qty_extra_5 number,sw_repeat number)';
execute immediate 'create sequence tmp_seq2_'||sqid;
execute immediate stmtCreate;
stmtIns := 'insert into srctmp_'||sqid;
rw:=1;
col:=1;
for s in 0..tsk.count-1 loop
---creating dynmic column here ---------------------------------------------------
stmtCreate2 := stmtCreate2 || ',indQTY_'||replace(replace(replace(tsk(s),' ',''),'-',''),'/','')||' number, totQTY_'||replace(replace(replace(tsk(s),' ',''),'-',''),'/','')||' number';
ArrQ(0):=1;
ArrS(0):='MSK';
stmtIns := 'insert into srctmp_'||sqid||'
select LEVEL as NIVEL, SKU, PARENT_PART, COMPONENT_PART, DESCRIPTION, qtypkg.getChain(LEVEL,decode(PARENT_PART,sku,''MSK'',PARENT_PART),COMPONENT_PART) as CHAIN, QTY_PER, qtypkg.getqty(LEVEL,qty_per) as TOTAL_QTY_PER, CREATIONDATE, SUBSYSTEM, COMMENTS, PROCURED_FOR_BUILD, FAB_PUR_FLG,PART_TYPE, TYPE_FLG, COST, tmp_seq2_'||sqid||'.nextval as ORD,qty_extra_1 ,qty_extra_2 ,qty_extra_3 ,qty_extra_4 ,qty_extra_5,0
from (select /*+ FIRST_ROWS */ sku,parent_part,component_part,description,qty_per,creationdate,subsystem,comments,procured_for_build,fab_pur_flg,PART_TYPE,type_flg, cost,qty_extra_1,qty_extra_2,qty_extra_3,qty_extra_4,qty_extra_5
from $$TABLE$$
where projectid='||projid||'
and sku='''||upper(tsk(s))||''' $$BUILDFILTER$$ order by component_part )
start with parent_part='''||upper(tsk(s))||'''
connect by prior component_part=parent_part';
IF bombuild<>'$ACTIVE' THEN
stmtIns:=replace(stmtIns,'$$TABLE$$','STRUCTURE_LOCAL_BUILD');
stmtIns:=replace(stmtIns,'$$BUILDFILTER$$',' and build = '''||upper(bombuild)||''' ');
ELSE
stmtIns:=replace(stmtIns,'$$TABLE$$','STRUCTURE_LOCAL1');
stmtIns:=replace(stmtIns,'$$BUILDFILTER$$',' ');
END IF;
execute immediate stmtIns;
for b in 0..tbld.count-1 loop
---creating dynmic column here ---------------------------------------------------
stmtCreate2 := stmtCreate2 || ',PURQT_'||replace(replace(replace(tsk(s),' ',''),'-',''),'/','')||'_'||replace(replace(replace(replace(tbld(b),' ',''),'-',''),'/',''),'|','')||' NUMBER';
end loop;
end loop;
stmtCreate2 := stmtCreate2 || ',ORD NUMBER)';
execute immediate stmtCreate2;
fillTable(sqid,'srctmp_'||sqid,'srctmpFinal_'||sqid,tsk,maxlevel,tbld);
-- Update the counter of repeats parts
stmtRepeat := 'Update srctmpFinal_'||sqid || ' s set sw_repeat = (select count(*) from srctmpFinal_'||sqid || ' s2 where s2.component_part = s.component_part ) ';
execute immediate stmtRepeat ;
execute immediate 'drop sequence tmp_seq2_'||sqid;
tmpName:='srctmpFinal_'||sqid;
END;

create or replace PROCEDURE fillTable(sqid in number, t1 in varchar2,t2 in varchar2,tsk in pkg_maintenance_dcc.tpars, maxlevel out number, tbld in pkg_maintenance_dcc.tpars) IS
type trec is record (NIVEL number,
SKU varchar2(50),
PARENT_PART varchar2(30),
COMPONENT_PART varchar2(30),
DESCRIPTION varchar2(256),
CHAIN VARCHAR2(512),
QTY_PER NUMBER,
TOTAL_QTY_PER NUMBER,
CREATIONDATE date,
SUBSYSTEM varchar2(50),
COMMENTS varchar2(256),
PROCURED_FOR_BUILD varchar2(2000),
FAB_PUR_FLG varchar2(1),
PART_TYPE varchar2(1),
TYPE_FLG varchar2(1),
COST VARCHAR2(3),
ORD NUMBER
);
type tcur is ref cursor;
cur tcur;
vrec trec;
stmt varchar2(32000);
stmtUPD varchar2(32000);
parent varchar2(50);
st varchar2(2000);
stmtIndx varchar2(1000);
BEGIN
stmt := 'insert into '||t2||' (NIVEL,SKU,SUBSYSTEM,PARENT_PART,COMPONENT_PART,DESCRIPTION,CHAIN,CREATIONDATE,TYPE_FLG,FAB_PUR_FLG,COMMENTS,PROCURED_FOR_BUILD,PART_TYPE, COST,ORD,qty_extra_1,qty_extra_2,qty_extra_3,qty_extra_4,qty_extra_5)
select nivel,''MSK'',subsystem,decode(parent_part,sku,''MSK'',parent_part) parent_part,component_part,description,CHAIN,creationdate,type_flg,fab_pur_flg,comments, procured_for_build,PART_TYPE,COST, min(ord),qty_extra_1,qty_extra_2,qty_extra_3,qty_extra_4,qty_extra_5
from '||t1||'
group by nivel,subsystem,decode(parent_part,sku,''MSK'',parent_part),component_part,CHAIN,description,creationdate,type_flg,fab_pur_flg,comments,procured_for_build,PART_TYPE,COST,qty_extra_1,qty_extra_2,qty_extra_3,qty_extra_4,qty_extra_5
order by min(ord)';
execute immediate stmt;
--next statement would not work if user HOLY_SHROUD had insufficient privileges (CREATE ANY INDEX system privilege is needed here).
stmtIndx:='create index '||t2||'_indx1 on '||t2||'(nivel,subsystem,parent_part,component_part,chain)';
execute immediate stmtIndx;
execute immediate 'analyze index '||t2||'_indx1 compute statistics';
stmt := 'select * from '||t1;
maxlevel := 1;
open cur for stmt;
fetch cur into vrec;
while cur%found loop
stmtUPD := ' update '||t2||' set ';
stmtUPD := stmtUPD || 'indQTY_'||replace(vrec.sku,'-','')||' = '||vrec.qty_per||',';
stmtUPD := stmtUPD || 'totQTY_'||replace(vrec.sku,'-','')||' = '||vrec.total_qty_per;
if vrec.parent_part=vrec.sku then
parent:='MSK';
else
parent:=vrec.parent_part;
end if;
if vrec.nivel > maxlevel then
maxlevel := vrec.nivel;
end if;
stmtUPD := stmtUPD || ' where nivel='||vrec.nivel;
IF vrec.subsystem is null THEN
stmtUPD := stmtUPD || ' and subsystem is null ';
ELSE
stmtUPD := stmtUPD || 'and subsystem='''||vrec.subsystem||'''';
END IF;
stmtUPD := stmtUPD || 'and parent_part='''||parent||'''
and component_part='''||vrec.component_part||'''
and chain = '''||vrec.chain||'''';
BEGIN
execute immediate stmtUPD;
EXCEPTION WHEN OTHERS THEN
err('cyclic update: '||stmtUPD);
COMMIT;
END;
stmtUPD := ' update '||t2||' set';
for b in 0..tbld.count-1 loop
stmtUPD := stmtUPD || ' PURQT_'||replace(replace(replace(vrec.sku,' ',''),'-',''),'/','')||'_'||replace(replace(replace(replace(tbld(b),' ',''),'-',''),'/',''),'|','')|| ' = ' || to_char(getPurQua(t2,'totQTY_'||replace(replace(replace(vrec.sku,' ',''),'-',''),'/',''),replace(replace(replace(replace(tbld(b),' ',''),'-',''),'/',''),'|',''),vrec.chain))||',';
end loop;
stmtUPD := substr(stmtUPD,1,length(stmtUPD)-1) || ' where nivel='||vrec.nivel;
IF vrec.subsystem is null THEN
stmtUPD := stmtUPD || 'and subsystem is null ';
ELSE
stmtUPD := stmtUPD || 'and subsystem='''||vrec.subsystem||'''';
END IF;
stmtUPD := stmtUPD || 'and parent_part='''||parent||'''
and component_part='''||vrec.component_part||'''
and chain = '''||vrec.chain||'''';
begin
execute immediate stmtUPD;
exception when others then
err('erroneus stmt: '||stmtUPD);
err('error msg: '||sqlerrm);
end;
fetch cur into vrec;
end loop;
for s in 0..tsk.count-1 loop
execute immediate 'update '||t2||' set indQTY_'||replace(replace(replace(tsk(s),' ',''),'-',''),'/','')||'=0 where indQTY_'||replace(replace(replace(tsk(s),' ',''),'-',''),'/','')||' is null';
execute immediate 'update '||t2||' set totQTY_'||replace(replace(replace(tsk(s),' ',''),'-',''),'/','')||'=0 where totQTY_'||replace(replace(replace(tsk(s),' ',''),'-',''),'/','')||' is null';
for b in 0..tbld.count-1 loop
st := 'update ' ||t2||' set purqt_'||replace(replace(replace(tsk(s),' ',''),'-',''),'/','')||'_'||replace(replace(replace(replace(tbld(b),' ',''),'-',''),'/',''),'|','')||' = 0 where purqt_'||replace(replace(replace(tsk(s),' ',''),'-',''),'/','')||'_'||replace(replace(replace(replace(tbld(b),' ',''),'-',''),'/',''),'|','')||' is null';
execute immediate st;
end loop;
end loop;
commit;
END;



Thanks And Regards
Vishal Taneja

Tom Kyte

Followup  

November 21, 2007 - 12:14 pm UTC

... Here i am pasting Code ,which 'll defenetly help you to understand my question. ...

that was a joke right?

that is some nasty looking stuff there.

and if you post anything that contains this:

       BEGIN
           execute immediate stmtUPD;
        EXCEPTION WHEN OTHERS THEN
           err('cyclic update: '||stmtUPD);
           COMMIT;
         END;



just expect - anticipate - that I'll make so much fun of you.


Because that is obviously, definitely, absolutely a bug in your code. You can just delete EVERYTHING between the BEGIN and the EXCEPTION statement. Your transactional logic is somewhat "botched" there.


Do you see the flow of logic here - and how broken it is? This code doesn't need to be reworked to not use DDL, this code needs to be

a) restated as textual requirements
b) designed from scratch
c) implemented as if this bit didn't exist

because there is just too much wrong with the existing code. Google:

sql injection

and start binding, using dbms_assert when you cannot bind.

NEVER code a when others, that is not followed by RAISE.


DO NOT commit (or rollback) in your stored procedures - not if you do not understand transaction processing. Look at this little bit of code of yours:



     open cur for stmt;
     fetch cur into vrec;
     while cur%found 
     loop
        stmtUPD := ' update '||t2||' set '; ....
        BEGIN
           execute immediate stmtUPD;
        EXCEPTION WHEN OTHERS THEN
           err('cyclic update: '||stmtUPD);
           COMMIT;
        END;
        stmtUPD := ' update '||t2||' set';
        for b in 0..tbld.count-1 
        loop
           ...
        end loop;
        stmtUPD := ...;
        begin
             execute immediate stmtUPD;
        exception when others then
             err('erroneus stmt: '||stmtUPD);
             err('error msg: '||sqlerrm);
        end;
        fetch cur into vrec;
     end loop;



You are saying basically "update may or may not work, we don't care. sometimes it will work, sometimes it won't and sometimes when it doesn't - we commit (no reason why we commit, we just do"

Since the updates can fail silently, just erase them. It is that easy.

do this search:
http://www.google.com/search?q=site%3Atkyte.blogspot.com+when+others

read some of those articles and please - please - revisit all of your companies error handling, it is just wrong.



Removing DDls

November 22, 2007 - 4:12 am UTC

Reviewer: Vishal Taneja from India

Thanks Tom for answring my question.
but one thing i want to tell ,this code is not written by us,as i mentioned it is a re-written project.this
project is written in year of 2000 by some people in pl/sql server pages .so we are re-writting this code in .net and
oracle 10g.
this procedures they were using for reporting purpose just because column are generating
on runtime.
as you can see.

for s in 0..tsk.count-1 loop
---creating dynmic column here
---------------------------------------------------
stmtCreate2 := stmtCreate2 || ',indQTY_'||replace(replace(replace(tsk(s),'
',''),'-',''),'/','')||' number, totQTY_'||replace(replace(replace(tsk(s),'
',''),'-',''),'/','')||' number';

so as per company standard ,we are not allowed for DDLs.

so my question is there any approch through which we can remove these DDLs .
we can not use temp tables because column are generating on run time.

Vishal Taneja

Tom Kyte

Followup  

November 26, 2007 - 10:36 am UTC

you do not need temporary tables AT ALL.

Just build a single query.


suggestion:

erase the existing code from your memory.

develop SPECIFICATIONS FROM SCRATCH.

implement good code.


Looking at this stuff (I don't care who wrote it or when it was written - it contains just about EVERY ANTI-PATTERN ever discussed on this site) will only confuse you, lead you down the wrong path.



You don't need temporary tables to write SQL. You have with subqueries, you have inline views. You don't need to write data into tables, update it and then select it out (that is soooooo sqlserver)

But what about non-table/views

May 15, 2008 - 1:31 pm UTC

Reviewer: Evan Ehrenhalt from Atlanta, Ga USA

Tom,

The code I see here is great for tables, views and indexes. What about "soft objects" such as procedures, packages and functions?

IMP/EXP doesn't show these and metadata has a word break problem.
Thanks,
Evan
Tom Kyte

Followup  

May 19, 2008 - 12:16 pm UTC

metadata has no word break problem? what do you mean.

If you

a) set linesize reasonable (say 80)
b) column whatever format a100 word_wrapped

you'll get nicely wrapped output that doesn't break in a word

May 20, 2008 - 2:12 am UTC

Reviewer: shukla

Hi Tom,

Can you help me with the syntax on using dbms_metadata.get_ddl for Jobs?

SQL> SELECT DBMS_METADATA.GET_DDL('JOB','DQ_UPDATION','DOTSOFT') FROM DUAL;
ERROR:
ORA-31604: invalid NAME parameter "NAME" for object type JOB in function SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4676
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8552
ORA-06512: at "SYS.DBMS_METADATA", line 2881
ORA-06512: at "SYS.DBMS_METADATA", line 2747
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

Tom Kyte

Followup  

May 20, 2008 - 11:08 am UTC


is your job really a dbms_scheduler thing or a dbms_job thing.

Package definition

May 20, 2008 - 5:39 pm UTC

Reviewer: A reader

Hi Tom,
Is there any way to look at the package(s) defintions only from the full user based export?
Thanks.
Tom Kyte

Followup  

May 20, 2008 - 9:27 pm UTC

not with imp, you'd have to use a newer pair of tools - the data pump tools.

Thanks a lot!

May 20, 2008 - 9:44 pm UTC

Reviewer: A reader


May 21, 2008 - 12:24 am UTC

Reviewer: shukla

Hi Tom,
It is dbms_job only.

Thanks
--Shukla
Tom Kyte

Followup  

May 21, 2008 - 8:35 am UTC

jobs don't have names, they are not "named objects"


it is pretty easy to select out what you need from user_jobs however.

Suggestions for get_ddl('PACKAGE')

June 19, 2008 - 1:07 pm UTC

Reviewer: Janel from Iowa

Tom,

Hello again!

A few posts back, someone was trying to write the results of the dbms_metadata.get_ddl to a utl_file.
One of your comments was:
32k linesize limits for text files is almost always better than good enough. Java - hard, plsql easy.

I am trying to do the same, as a way to automate the backup of our code for each schema...as far as I know, plsql is the only option that I have available to me.

Currently, I build the utl_file by querying the all_source table for each object (i.e. each line in package). I would really like to switch this over to the get_ddl function, but I know we will run into issues when trying to do packages, as it will be >32K...Wondering if you had any other suggestions?

I am working with tables right now and have coded it this way...for every table in schema I create a file and output to it using:

sys.utl_file.put_line(v_output_file, dbms_metadata.get_ddl('TABLE', v_tbl));

Thank you,
Janel
Tom Kyte

Followup  

June 19, 2008 - 1:58 pm UTC

To automate the backup of code one uses

SOURCE CODE CONTROL SYSTEMS

you are doing it *wrong* if you need to backup stuff out of the database for your source code, you are doing it way way wrong.

You need source code control.

short of that, just exp with rows=n, you get it all.

or expdp if you are using 10g and up.

June 19, 2008 - 2:50 pm UTC

Reviewer: Janel from Iowa

Well...I am going to risk it and ask for some clarification. I am thinking you mean having, say, an outside application for source code versioning (i.e. SourceSafe), or are you referring to something from Oracle? Either way, we do not use anything like that...is that grounds for "running away as fast as I can?" :)

Problem with extracted DDL

July 23, 2008 - 4:58 pm UTC

Reviewer: Kevin from Stevens Point, WI

Tom:

We're trying to move some views, which exist on SYSTEM A, to SYSTEM B.

When we extract the DDL for the VIEW from SYSTEM A, it extracts fine, but is very large 2000+ lines w/as many as 200+ characters on a line.

The views work fine on SYSTEM A, and have no errors what-so-ever. Select statement works fine, and produces output.

However, try to compile the DDL on SYSTEM B, it refuses to compile. We get strange errors like:


Error starting at line 1 in command:
CREATE OR REPLACE FORCE VIEW "REPORTS"."V_RETENTION_BY_AGENT" ("YEAR", "MONTH", "PRODUCER_NUMBER", "PRODUCER_NAME", "POL_TYPE", "IN_FORCE_LY", "IN_FORCE", "SOLD", "CANCEL", "MTD_RATE", "YTD_RATE", "MAT_RATE") AS
select a.year, a.month, a.producer_number, p.name, a.pol_type, --sum(r1.in_force), sum(r2.in_force), sum(r2.sold), sum(r2.cancel),

then continues on with...
( 12

Error at Command Line:76 Column:7
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:

Error starting at line 77 in command:
sum
Error report:
Unknown Command

We thought maybe it was the method, so we tried SQL Developer, DBMS_METADATA, the EXP/IMP trick to no avail...

(Note: I'd list the whole DDL here, but frankly i'm not sure it would fit.)

This is *old* DDL, as this database was continuously updated from 8i->9i->10g. Is it possible that the DDL in the View's SQL text column is no longer valid?

We're not sure what to do here. The original DB works fine, but it doesn't seem that we have any way of moving this view to another system....

Tom Kyte

Followup  

July 24, 2008 - 10:47 am UTC

only thing I can say here would be to extract the view text to a file, a script

run the script

isolate the issue

sqlplus will tell you the line number that it fails on, you would break that line until you isolate the problem

Re: Problem with extracted DDL

July 25, 2008 - 2:47 am UTC

Reviewer: A reader

I notice the single line comment before the first sum() in the section of view text above:

"select a.year, a.month, a.producer_number, p.name, a.pol_type, --sum(r1.in_force), "

If for whatever reason the util doing the DDL extraction squeezes more than originally indented onto the end of a line that has been commented out with a -- then that might well be the cause of the missing parenthesis errors.

Problem with extracted DDL (SOLVED)

August 04, 2008 - 3:48 pm UTC

Reviewer: Kevin from Stevens Point, WI

FYI:

Turns out we ended up figuring out what the problem was. The developers used a 'third party' tool which used OCI to drop SQL into the system. This behaves somewhat differently than standard input methods, as it seems to ignore the "/" character on a single line.

The SQL code was formatted so that the mathematical equations were split across multiple lines (lord knows why) like:

1
+
1
=
0

In our case, we had:

3
/
4

SQL*Plus, and just about every other program that properly utilizes SQL input into Oracle, interpreted the "/" on a line by itself as a 'end of command' delimiter, and thus ignored everything afterwords.

Once we reformatted the SQL text so that the "/" characters were not on their own line, it compiled successfully.

Still not sure how this tool (specifically: PLEdit by Bethnic Software) managed to get code to compile that all others didn't... but I digress...

Thanks for your help!

Tom Kyte

Followup  

August 04, 2008 - 8:32 pm UTC

ah, it is a sqlplus-ism


select a-
b
from t;

in plus would be:

select ab
from t;

(minus is the continuation character, that would be another issue)

if you don't use a "command line interpreter" - something that reads your input and looks for stuff - you can execute this fine.

Export with rows=n

August 15, 2008 - 3:25 am UTC

Reviewer: Leif from Germany

Hi Tom,

you recommend using exp with rows=n to save my database source code. But I am having a problem with importing this, because the table objects are created with the original initial extent, which is in some cases very big and takes a lot of time and diskspace while creating. My goal is to import non data objects before importing the data objects using transportable tablespace. I did not find anything avoiding this extent creation, so think using dbms_metadata.get_dll is my only chance.
Oracle Version 9.5.2
Tom Kyte

Followup  

August 20, 2008 - 8:27 am UTC

in the olden days (remember this was version 8.0 here)... I wrote when back when:


dbms_metadata doesn't exist in 8i, and the data dictionary format dbms_metadata would be expecting
in 9i differes radically from what was n 8i.

In 8i -- you would use

exp ...... rows=n
imp ...... indexfile=foo.sql


and I've always recommended and still do to use proper SOURCE CODE CONTROL.

9.5.2 does not exist, it is not a version.

do you not have your original DDL scripts?!?!?!?!?

if you use the indexfile, you get a script, you can edit that.

Problems with extracting DDL

August 20, 2008 - 10:24 am UTC

Reviewer: Rod West from Bristol, UK

We use another third party tool (in this case TOAD) which uses OCI to create procedures and packages in the system. The tool always adds a space to the end of the code when it creates a procedure or package. This is annoying because the dbms_metadata package then does not put the "/" on a new line when there is a space at the end of the package. Then we find that the scripts that are generated by the dbms_metadata package do not work. SQLPlus always trims the space off the end unless execute immediate is used to create the procedure. Any thoughts on how we could trim the spaces off the end off the packages so that the dbms_metadata ddl extract will work?

SQL> begin
  2  execute immediate 'create or replace procedure rw_metatest as begin null; end; ';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);


PL/SQL procedure successfully completed.

SQL> SELECT dbms_metadata.get_ddl('PROCEDURE','RW_METATEST', 'APPS') CMD FROM DUAL;

CMD
--------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "APPS"."RW_METATEST" as begin null; end; /

Tom Kyte

Followup  

August 20, 2008 - 10:43 am UTC

please contact support and reference bug 1463127, that was fixed in 9i - it was against views, but the same exact issue arises with procedures apparently.


GET_DEPENDENT_DDL for INDEX ORGANIZED table

February 03, 2009 - 11:34 pm UTC

Reviewer: Harry Who from Brisbane, QLD

Using GET_DDL for an IOT provides DDL that must, for obvious reasons, include the PRIMARY KEY constraint.

We have a performance index on the same IOT and when I use GET_DEPENDENT_DDL it generates 2 CREATE INDEX statements: one for the primary key (SYS_...) and one for the performance index.

The SET_FILTER (SYSTEM_GENERATED, FALSE) doesn't work in this situation.

Is there a way of avoiding the index for the primary key constraint being generated by GET_DEPENDENT_DDL ?

Thanks
Tom Kyte

Followup  

February 04, 2009 - 11:22 am UTC

set_filter worked for me?

ops$tkyte%ORA10GR2> create table t
  2  ( x int primary key,
  3    y int
  4  ) organization index;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(y);

Index created.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0),
        "Y" NUMBER(*,0),
         PRIMARY KEY ("X") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 PCTTHRESHOLD 50



ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2          l_mdHandle    number;
  3          l_transHandle number;
  4          l_ddl         clob;
  5  begin
  6          l_mdHandle := dbms_metadata.open('INDEX');
  7          dbms_metadata.set_filter(l_mdHandle,'SYSTEM_GENERATED', FALSE);
  8          dbms_metadata.set_filter(l_mdHandle,'BASE_OBJECT_SCHEMA', user);
  9          dbms_metadata.set_filter(l_mdHandle,'BASE_OBJECT_NAME','T');
 10          l_transHandle := dbms_metadata.add_transform(l_mdHandle, 'DDL');
 11          dbms_metadata.set_transform_param(l_transHandle, 'SQLTERMINATOR', TRUE);
 12          loop
 13                  l_ddl := dbms_metadata.fetch_clob(l_mdHandle);
 14                  exit when l_ddl is null;
 15                  dbms_output.put_line( l_ddl );
 16          end loop;
 17          dbms_metadata.close(l_mdHandle);
 18  end;
 19  /

  CREATE INDEX "OPS$TKYTE"."T_IDX" ON "OPS$TKYTE"."T" ("Y")
  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" ;


PL/SQL procedure successfully completed.

error in DBMS_METADATA

May 01, 2009 - 11:21 am UTC

Reviewer: Mahendra from UK

when i run the following query

select dbms_metadata.get_ddl(OBJECT_TYPE, OBJECT_NAME) from user_objects order by OBJECT_TYPE;

i get the following error

ERROR:
ORA-31600: invalid input value TABLE PARTITION for parameter OBJECT_TYPE in function GET_DDL
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1573
ORA-06512: at "SYS.DBMS_METADATA_INT", line 1969
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4008
ORA-06512: at "SYS.DBMS_METADATA", line 504
ORA-06512: at "SYS.DBMS_METADATA", line 560
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

may i know why this is coming?
thanks in advance
Tom Kyte

Followup  

May 11, 2009 - 8:33 am UTC

because you cannot get the ddl for a table partition, it doesn't make sense.

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#sthref3561

cannot extract ddl for procedures

May 05, 2009 - 7:45 am UTC

Reviewer: Mahendra

SQL> SELECT DBMS_METADATA.GET_DDL('PROCEDURE',u.procedure_name) FROM USER_procedures u ;
SELECT DBMS_METADATA.GET_DDL('PROCEDURE',u.procedure_name) FROM USER_procedures u
       *
ERROR at line 1:
ORA-31600: invalid input value NULL for parameter VALUE in function SET_FILTER
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 2021
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4067
ORA-06512: at "SYS.DBMS_METADATA", line 670
ORA-06512: at "SYS.DBMS_METADATA", line 573
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

Tom Kyte

Followup  

May 11, 2009 - 8:33 am UTC

user_procedures has procedure names of packaged procedures too - it would not be the right view to use.

ops$tkyte%ORA10GR2> SELECT DBMS_METADATA.GET_DDL('PROCEDURE',u.object_name) FROM user_objects u where u.object_type = 'PROCEDURE';

DBMS_METADATA.GET_DDL('PROCEDURE',U.OBJECT_NAME)
-------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "OPS$TKYTE"."PP" ( p_contains in varchar2 )
as
begin
        for x in ( select t1.str, 10 s from t1 where contains(str,p_contains) > 0  )
        loop
                dbms_output.put_line( x.str );
        end loop;
        for x in ( select t2.str, 10 s from t2@ora10gr2@loopback where contains@ora10g
r2@loopback(str,p_contains) > 0 )
        loop
                dbms_output.put_line( x.str );
        end loop;
        return;
end;



  CREATE OR REPLACE PROCEDURE "OPS$TKYTE"."STAT_REFCURSOR1"
as
  type ref_cur is ref cursor;
  cur_ref    ref_cur;
  vn_sal    pls_integer := 1100;
  vd_hiredate date := TO_DATE ('1/1/1982','MM/DD/YYYY');
 BEGIN
    open cur_ref for
      SELECT * FROM EMP WHERE SAL > vn_sal AND HIREDATE < vd_hiredate;
    close cur_ref;
 END;



  CREATE OR REPLACE PROCEDURE "OPS$TKYTE"."DYN_REFCURSOR_1"
as
  type ref_cur is ref cursor;
  cur_ref    ref_cur;
  vn_sal    pls_integer := 1100;
  vd_hiredate date := TO_DATE ('01-jan-1982','dd-mon-yyyy');
 BEGIN
    open cur_ref for
      'SELECT * FROM EMP WHERE SAL > :vn_sal AND HIREDATE < :vd_hiredate'
      USING vn_sal,vd_hiredate;
    close cur_ref;
 END;



Structure Import

June 15, 2009 - 11:14 pm UTC

Reviewer: Manoj Bajaj from singapore

Many times we need to export and import only structure from existing huge Database to new DB. Why does oracle not come up with export import option that allows us to export and import object definitions excluding storage clauses. I just need plain DDL exp/imp.

Getting indexfile formattting etc is very time consuming.Especially when we need to do the same over and over again.
Tom Kyte

Followup  

June 16, 2009 - 2:15 pm UTC

we did, it is called data pump and/or dbms_metadata.get_ddl


and you know what, if this is your ddl and you do not have the ability to check this out from your repository - you have done it wrong.

Data Migration--10g

June 24, 2009 - 10:32 am UTC

Reviewer: Pointers from US

Hi Tom,

Thanks for your great site.

I would request your help on the below case.

I use 10g release 2.

I have 2 packages in one Data base. These packages refers tables, indexes, sequences, views, some other stored procedures.
Now, I want to move these two packages to someother Data base. In the new DB i have to create all these referenced objects which are used by my 2 packages.

I have queried manually the user_dependencies data dictionary view to get dependencies, then I took the DDL for all the referenced objects then manually created all the objects and 2 packages.

Could you please share me, the best way to do these kind of things.
It would be great if you can share an example.

Regards,
Pointers.

Tom Kyte

Followup  

June 26, 2009 - 9:52 am UTC

you would have source code to install in my world - meaning, you would never read a database dictionary table like that.

I cannot imagine the use case whereby I could point at a package and have that decide what tables should be created, I manage code in applications - you would just check out an application from your source code control system.

Package referred DDL

June 30, 2009 - 1:35 pm UTC

Reviewer: Pointers from US

Hi tom,

Thanks for your reply to my earlier post.

We dont use any source code control system. We simply load the package from server through plsql developer and work on it.

Is there any workaround to do the task.

My aim is to get DDl in the form of script for all the package refered objects and if the object is table then the foreign key referred tables as well.

Say, if package refers 'emp' table then I need the 'emp'table script and dept script as well, as emp.deptno refers dept.deptno.

Let me expatiate more, my package is "pkg_pointers"
"pkg_pointers" refers the objects like "test_table"(table), "test_sequence"(sequence),"test_index"(index)
then i need the script for "test_sequence", "test_index", "test_index".
and also the DDL for the tables refered by "test_index" table

If this is not possible to get, how will in general we do the creation package from one DB to another DB.
I did like, I made a list to get all the refered objects in the package by going through the package code, then DDL for them, then created all these referred objects in another DB and finally package code.

Have I done the right process. Please share your idea.

Regards,
Pointers.


Tom Kyte

Followup  

July 06, 2009 - 6:49 pm UTC

I'm going to constantly go back to "you need source code control"

You don't like my answer, but it will not change.

Thanks for your reply tom

July 07, 2009 - 5:17 am UTC

Reviewer: A reader

Thanks for your reply tom.

July 09, 2009 - 11:32 pm UTC

Reviewer: Scofield

Sir, After your enourmous contributions to Oracle community,
Oracle company should build your statue to every Oracle head office in every country..

Extract DDL using datapump - impdp sqlfile

July 15, 2009 - 12:10 pm UTC

Reviewer: A reader

Hi - We are trying to come up with a way to generate ddl from our production system so that we could apply the generated ddl to our test environments and dont have to worry about any issues like synonyms, views etc getting out of sync when the test environment is refreshed. The two methods that we considered were
1. Use dbms_metadata.get_ddl
2. Generating a metadata export dump file "expdp content=metadata_only" from production, copy the dump file to test environment and then use "impdp sqlfile=" to generate a sql file to be run in the test environment.

We decided to go with the second option. Works great except that when I tried to run the sql file one of the views did not create and got the following error

SP2-0027: Input is too long (> 2499 characters) - line ignored

How can this be overcome ?

When you compare dbms_metadata and data pump, why do you prefer dbms_metadata ? Datapump looks to be so simple. Are there any limitations using datapump ?
Tom Kyte

Followup  

July 15, 2009 - 12:32 pm UTC

what???

shouldn't test actually be running AHEAD of production? This is backwards. How did you *test* these DDL changes??? Where is your source code control?? Why would you not apply the upgrade script to test AND THEN to production?


data pump uses dbms_metadata.

where do you see me writing "i prefer using dbms_metadata to data pump".

But, if someone asked me, I probably would say "dbms_metadata", so you have control over the output, which is what you are asking for (the line size thing)

July 15, 2009 - 12:33 pm UTC

Reviewer: A reader

So using datapump I cannot overcome the input too long error ?
Tom Kyte

Followup  

July 15, 2009 - 12:44 pm UTC

you don't really have control over the output. The resulting dmp file is a binary beast. It isn't a "sql script" or anything like that.

why don't you address my questions? I'm always very scared when people describe wanting to do what you are wanting to do. It is completely *backwards*

July 15, 2009 - 2:55 pm UTC

Reviewer: A reader

We want to do these kinds of refreshes if we want to baseline an environment from production so that we can do our installs in that environment and determine what production will behave like ? Any such release installs always require refreshing from production as the first step. How else would we achieve that requirement ?
Tom Kyte

Followup  

July 15, 2009 - 3:04 pm UTC

... Any such release installs always
require refreshing from production as the first step. ....

that is backwards, as TEST is always greater than or equal to production.

Answer me this: how could something exist in production which did not exist in test? Do you do things in production without change management, without testing it first?


And if you wanted to have "production to test with", we call that a "restore from backup" or "duplicate database"

July 16, 2009 - 10:03 am UTC

Reviewer: A reader

In our environment we have multiple testing environments like 10 development, 8 QA etc. Some of these are greater than or equal to production where we have tested the code before deploying to production. But there are others that are identified to use for other projects but they might have been from some time back. So we want to start with a clean baseline by refreshing from production.
Tom Kyte

Followup  

July 16, 2009 - 11:33 am UTC

so, like I said, restore from backup. quite easy, very fast, and - most importantly - you actually prove you know how to restore from backup and you verify regularly that your backups work.

Or use the rman duplicate database to clone the test environment that is representative of production.

system tablespace??

July 16, 2009 - 4:09 pm UTC

Reviewer: Bill B from Liverpool, NY, USA

Trying not to nit pick, but you have always yelled at people (rightfully so) to not use the SYSTEM tablespace for user files and I notice that all your examples that you created in this thread were created in the system tablespace....tsk tsk. LOL.

July 29, 2009 - 3:26 pm UTC

Reviewer: A reader

When using dbms_metadata.get_ddl for extracting DDL for the user views, I am getting the following error

The piece of code I am using to generate the ddl is

declare
cursor get_views is
select owner, object_name from all_objects where owner = 'xxxxx' and object_type = 'VIEW';
file_handle utl_file.file_type;
ddl_stmt clob;
begin
file_handle := utl_file.fopen('DIROBJECT', 'ddl.sql', 'w', 32767);
for v in get_views
loop
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);
ddl_stmt := dbms_metadata.get_ddl ('VIEW',v.object_name,v.owner) ;
utl_file.put (file_handle,ddl_stmt);
end loop;
utl_file.fclose(file_handle);
end;
/

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error

Could you help me with this ?
Tom Kyte

Followup  

August 03, 2009 - 5:06 pm UTC

works dandy for me

ops$tkyte%ORA10GR2> declare
  2  cursor get_views is
  3  select owner, object_name from all_objects where owner = 'xxxxx' and
  4  object_type = 'VIEW';
  5  file_handle utl_file.file_type;
  6  ddl_stmt clob;
  7  begin
  8  file_handle := utl_file.fopen('DIROBJECT', 'ddl.sql', 'w', 32767);
  9  for v in get_views
 10  loop
 11  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,
 12  'SQLTERMINATOR', TRUE);
 13  ddl_stmt := dbms_metadata.get_ddl ('VIEW',v.object_name,v.owner) ;
 14  utl_file.put (file_handle,ddl_stmt);
 15  end loop;
 16  utl_file.fclose(file_handle);
 17  end;
 18  /

PL/SQL procedure successfully completed.




Your error is strange, since it is on line 1. So, we'd have to see a complete test case cut and pasted from sqlplus - no edits.



My guess would be....

You are doing a text file
The max linesize is 32k
You are using utl_file.put (not put_line)
You have a really really long single line perhaps

and that would raise an exception - but not one line 1.

dbms_metadata with scheduler

November 24, 2009 - 11:21 am UTC

Reviewer: A reader from uk

If it's a DBMS_SCHEDULER job, what's the input to DBMS_METADATA.GET_DDL to get the definition?
Tom Kyte

Followup  

November 24, 2009 - 12:14 pm UTC

dbms_metadata does not currently support scheduler jobs.

Extract Insert (DML ) Statements from Exp

February 26, 2010 - 11:46 am UTC

Reviewer: guest

Pardon me, if my question doesn't relates to original Topic.
we have an exp Dmp of whole schema with data, around 350 tables, from that 100 tables are static Tables, that has rows, From this exp how can i get the Insert statements of those 100 tables , so that i can store them as .sql files.
I have the list of those 100 tables.


Oracle Version is 11g.
Tom Kyte

Followup  

March 01, 2010 - 11:26 am UTC

export/import doesn't do that, it doesn't have insert statements in it, it has data. so, there is not any way to get export/import to print out sql inserts.

dbms_metadata over dblink

June 07, 2010 - 7:45 am UTC

Reviewer: Jaroslav from Czech rep

Hi,
maybe little bit out off topic, but I found sometimes usefull way, how to get object's DDL over database link.

declare
v_ddl clob;
v_ddl_part varchar2(4000);
v_len number
begin
select dbms_lob.get_length@dblink(dbms_metadata.get_ddl@dblink('TABLE','MY_TABLE')) into v_len from dual@dblink;
for i in 0..trunc(v_len/4000) loop
select dbms_lob.substr@dblink(dbms_metadata.get_ddl@dblink('TABLE','MY_TABLE'),4000,4000*i+1) into v_ddl_part from dual@dblink;
v_ddl:=v_ddl||v_ddl_part;
end loop;
end;

COMMENT

June 17, 2010 - 12:23 pm UTC

Reviewer: A reader

greetings thimas,

and thanks like always,

create table test1 as select * from all_users;

comment on table test1 is 'users tables';

comment on table test1.username is 'database username';

create table test2 as select * from test1;

table test2 will be created like table test1 but without table test1 comments, how i can move the comments from test1 to test2??????
Tom Kyte

Followup  

June 22, 2010 - 12:36 pm UTC

you would have to do that manually.

June 28, 2010 - 7:06 am UTC

Reviewer: David Aldridge from In an office

>> dbms_metadata does not currently support scheduler jobs.

Just as an update, you can use this syntax to extract DBMS_Scheduler configuration:

select dbms_metadata.get_ddl('PROCOBJ',chain_name,user) from USER_SCHEDULER_CHAINS ;
select dbms_metadata.get_ddl('PROCOBJ',step_name,user) from USER_SCHEDULER_CHAIN_STEPS ;
select dbms_metadata.get_ddl('PROCOBJ',job_name,user) from USER_SCHEDULER_JOBS ;
select dbms_metadata.get_ddl('PROCOBJ',,user) from USER_SCHEDULER_JOB_ARGS ;
select dbms_metadata.get_ddl('PROCOBJ',program_name,user) from USER_SCHEDULER_PROGRAMS ;
select dbms_metadata.get_ddl('PROCOBJ',,user) from USER_SCHEDULER_PROGRAM_ARGS ;
select dbms_metadata.get_ddl('PROCOBJ',schedule_name,user) from USER_SCHEDULER_SCHEDULES ;


Rules seem to fall between the gaps. I use:

select 'DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => '''||chain_name||''',
condition => '''||condition||''',
action => '''||action||''',
rule_name => '''||rule_name||''',
comments => '''||comments||''');'
from USER_SCHEDULER_CHAIN_RULES
/

Using CLOB datatype fin executive immediate

July 06, 2010 - 5:47 pm UTC

Reviewer: Thiru from NJ Usa

Hi Tom,

Sorry was trying to get a chance to ask for a long time. Please do respond.

this is what I have :

declare
l_clob CLOB ;


begin
l_clob := 'alter table xyz set SUBPARTITION TEMPLATE (subpartition sub1 values(100),sub2 values(101).....)';
The above template will be around 40k long.
execute immediate to_char(l_clob);
end;

Is the above usage the proper way to deal with clob datatype variables?

Thanks
Tom Kyte

Followup  

July 06, 2010 - 6:40 pm UTC

in 10g and before, no. You cannot exceed 32k with native dynamic sql. You would use dbms_sql and the parse interface that allows you to send a plsql table of strings that will be concatenated together.

In 11g and above, yes, that'll work just dandy.

July 06, 2010 - 7:44 pm UTC

Reviewer: Thiru from NJ Usa

Thanks so much for the quick response. I will explore the dbms_sql to overcome the 32K limitation.

July 22, 2010 - 8:34 am UTC

Reviewer: Milind from India

I am trying to understand programmatic use of DBMS_METADATA package. Following is the code I typed to retrieve creation DDL for HR schema.
-- Oracle Release 10.2.0.1.0

conn hr/hr

DECLARE
v_handle NUMBER;
v_handle1 NUMBER;
v_handle2 NUMBER;
v_doc sys.ku$_ddls;
BEGIN
v_handle := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
DBMS_METADATA.SET_FILTER(v_handle,'SCHEMA', 'HR');
v_handle1 := DBMS_METADATA.ADD_TRANSFORM (v_handle, 'MODIFY', NULL, 'TABLE');
DBMS_METADATA.SET_REMAP_PARAM(v_handle1, 'REMAP_TABLESPACE', 'EXAMPLE', 'TBS1');
v_handle2 := DBMS_METADATA.ADD_TRANSFORM (v_handle, 'DDL');
DBMS_METADATA.SET_TRANSFORM_PARAM(v_handle2, 'SQLTERMINATOR', TRUE);
DBMS_METADATA.SET_TRANSFORM_PARAM(v_handle2, 'REF_CONSTRAINTS', FALSE, 'TABLE');
LOOP
v_doc := DBMS_METADATA.FETCH_DDL(v_handle);
EXIT WHEN v_doc IS NULL;
END LOOP;
DBMS_METADATA.CLOSE(v_handle);
END;
/

DECLARE
*
ERROR at line 1:
ORA-31602: parameter OBJECT_TYPE value "TABLE" in function ADD_TRANSFORM
inconsistent with HANDLE
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8887
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8137
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8883
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8137
ORA-06512: at "SYS.DBMS_METADATA_INT", line 8883
ORA-06512: at "SYS.DBMS_METADATA", line 3290
ORA-06512: at line 9

Can you please tell me what is wrong in the code?

Tom Kyte

Followup  

July 23, 2010 - 9:29 am UTC

see support bug 4010816

Get rid of "alter tablespace" in dbms_metadata

March 03, 2011 - 10:31 am UTC

Reviewer: ZB from Atlanta Ga

It looks like dbms_metadata.get_ddl('TABLESPACE','MYTABLESPACE') will genereate the base tablespace create. It also generates "alter tablespace" to resize the files to their current level.

If I wanted to generate the "base tablespace create" WITHOUT any of the alters (to get a baseline), can I do this with dbms_metadata, or do I need to "sed" out the "ALTER" lines from the output of the get_ddl command/
Tom Kyte

Followup  

March 03, 2011 - 10:59 am UTC

I'd stick with the alter approach personally - for this reason:

1 create tablespace statement runs at a time
more than 1 alter can run simultaneously

You can get the work done much faster just letting it do its thing.


As far as I know, the create statement recreates the tablespace as it was initially created and that is that.

March 03, 2011 - 1:00 pm UTC

Reviewer: ZB from Atlanta Ga

Yeah, I get the alters are better for mass changes. The problem I'm solving is creating a dev from another "semi-prod" system.

Dev doesn't have the same space available, so I need the tablespaces (which are all "create tablespace xxx 'filexxx' size <some small size> autoextend on blah blah")

The file names + locations have to be the same, as do the attributes (assm, logging etc). The size? Way smaller.

Since I'm porting this to dev - I obviously don't want an "alter tablespace" coming in after the create and resizing everything to 32G (our max smallfile autoextend size), thus blowing out my mounts.

Looks like we'll just sed out the alter statements. Thanks!
Tom Kyte

Followup  

March 03, 2011 - 1:14 pm UTC

if you just want that


select 'create tablespace ' || tablespace_name || ' datafile size 1m autoextend on next 1m;'
from dba_tablespaces;


and set the db_create_file_dest on the development database.

how to generate the DDL for range partition table

March 08, 2011 - 8:15 am UTC

Reviewer: balajiyes from UK

Hi, I am in a process of creating a BRT environment from my production database schema. I used dbms_metadata.get_ddl to extract the DDLs for LIST and HASH partitioned table. But for RANGE partitioned tables, I only need to have only 1 partition on the CREATE DLL statement. For example, If my table has 100 partitions, I am only interested on the 1st partition created.

Please help.
Tom Kyte

Followup  

March 08, 2011 - 12:54 pm UTC

You'll have to edit the output if you want it to be massively structurally different - which you do.


how to generate the DDL for range partition table

March 09, 2011 - 8:04 am UTC

Reviewer: Balajiyes from UK

Many thanks. That was useful.

By any chance can I get to see the source code for the package "DBMS_METADATA". I dont have the sys privs. I was wondering, I can create a copy package to simulate my requirement?

thanks in advance.
Tom Kyte

Followup  

March 09, 2011 - 9:44 am UTC

No, it is wrapped code, it is not published.

SYS.DBMS_METADATA.GET_DDL

March 23, 2011 - 8:52 am UTC

Reviewer: Raghu from India

Hi Tom,
Thanks for all the knowledge you are sharing with us.
When I run this query it works fine
select DBMS_METADATA.GET_DDL('PROCEDURE', 'PROCEDURE_NAME', 'SCHEMA') from DUAL;
But same thing if I want to do programatically,as below
CREATE OR REPLACE PROCEDURE DDL_EXTRACT
IS
V_CLOB clob;
begin
select DBMS_METADATA.GET_DDL('PROCEDURE', 'PROCEDURE_NAME', 'MY_SCHEMA') into V_CLOB from DUAL;
end;
And when I exec DDL_EXTRACT
It gives me the error as
ORA-04067: not executed, package body "MY_SCHEMA.DBMS_METADATA" does not exist.

Most importantly it says MY_SCHEMA.DBMS_METADATA not sure why.
My DBA asks me to use SYS.DBMS_METADATA but it gives compilation error. Unable to figure out where the issue is.
Could you please help. Thanks a lot.
Tom Kyte

Followup  

March 23, 2011 - 9:32 am UTC

what is the output of

ops$tkyte%ORA11GR2> select owner, object_type, status from dba_objects where object_name = 'DBMS_METADATA';

OWNER                          OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
SYS                            PACKAGE             VALID
SYS                            PACKAGE BODY        VALID
PUBLIC                         SYNONYM             VALID


it should look like that and if it does not have the DBA fix it. then get back to me if it still isn't working.

re: SYS.DBMS_METADATA.GET_DDL

March 23, 2011 - 9:49 am UTC

Reviewer: Raghu from India

Tom,
Thanks for your reply.
I ran the query "select owner, object_type, status from dba_objects where object_name = 'DBMS_METADATA'"
Status is valid for all the three but there was another row for PACKAGE which belonged to MY_SCHEMA (no body), So I dropped that package and tried to re-create the procedure,now it does'nt even compile, it throws error at compilation time saying "5/5 PL/SQL: SQL Statement ignored". But all grants are provided to MY_SCHEMA and also still I am able to execute the select statement independently. Please help.

Tom Kyte

Followup  

March 23, 2011 - 11:06 am UTC

show cut and paste from sqlplus of the code:

  CREATE OR REPLACE PROCEDURE DDL_EXTRACT
IS
V_CLOB clob;
begin
select DBMS_METADATA.GET_DDL('PROCEDURE', 'PROCEDURE_NAME', 'MY_SCHEMA') into V_CLOB from DUAL;
end;


Just like this:

a%ORA11GR2> CREATE OR REPLACE PROCEDURE DDL_EXTRACT
  2  IS
  3  V_CLOB clob;
  4  begin
  5  select DBMS_METADATA.GET_DDL('PROCEDURE', 'PROCEDURE_NAME', 'MY_SCHEMA') into V_CLOB from DUAL;
  6  end;
  7  /

Procedure created.



include any and all error messages.

re: SYS.DBMS_METADATA.GET_DDL

March 23, 2011 - 11:25 am UTC

Reviewer: Raghu from India

Ok,Below it is. I just replaced original schema name, database and procedure names. Thanks

my_schema@DATABASE> CREATE OR REPLACE PROCEDURE DDL_EXTRACT
2 IS
3 V_CLOB clob;
4 begin
5 select DBMS_METADATA.GET_DDL('PROCEDURE', 'PROCEDURE_NAME', 'my_schema') into V_CLOB from DUA
L;
6 end;
7 /

Warning: Procedure created with compilation errors.

Elapsed: 00:00:01.65
my_schema@DATABASE> show errors;
Errors for PROCEDURE DDL_EXTRACT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
5/8 PL/SQL: ORA-00904: : invalid identifier
Tom Kyte

Followup  

March 23, 2011 - 1:18 pm UTC

do you have a space in DUA L? why is it broken up like that.


re: Raghu from India

March 23, 2011 - 12:15 pm UTC

Reviewer: Greg from Toronto

The code you pasted works fine for me, no errors, my guess is it's got something to do with what you're replacing:

"I just replaced original schema name, database and procedure names"

Try this: as is, no changes, just copy/paste ... see if it compiles .. don't change anything ...


CREATE OR REPLACE PROCEDURE DDL_EXTRACT
IS
V_CLOB clob;
v_type varchar2(100);
v_name varchar2(100);
v_user varchar2(100);
begin
v_type := 'PROCEDURE';
v_name := 'PROCEDURE_NAME';
v_user := 'MY_SCHEMA';
select DBMS_METADATA.GET_DDL(v_type, v_name, v_user) into V_CLOB from DUAL;
end;
/

Then try changing your procedure name and user back .. if there's an issue with a special character in it or such, this script should narrow down the source a bit easier ... :)

(Sorry Tom, hope you don't mind, just trying to help, I know how swamped you get with all of our "pesky" questions .. :) I know I've had my share!!)

SYS.DBMS_METADATA.GET_DDL

March 23, 2011 - 1:08 pm UTC

Reviewer: Raghu from India

Greg,
I will try this tomorrow when I get back to work.
Thanks for the help.I appreciate.

re: SYS.DBMS_METADATA.GET_DDL

March 24, 2011 - 6:40 am UTC

Reviewer: Raghu from India

Tom,
I noticed 2 things
1. The same program works fine in an anonymous block only when I create a procedure, it fails (compilation error).
2. My database version is 10.2.0.3.0.
In documentation I read
DBMS_METADATA provides the following retrieval interfaces:
1.For programmatic use: OPEN, SET_FILTER, SET_COUNT, GET_QUERY, SET_PARSE_ITEM, ADD_TRANSFORM, SET_TRANSFORM_PARAM,SET_REMAP_PARAM, FETCH_xxx, and CLOSE retrieve multiple objects.
2.For use in SQL queries and for browsing: GET_XML and GET_DDL return metadata for a single named object. etc

So I guess we cannot use GET_DDL in PLSQL.
Anyway, I tried using OPEN and FETCH_CLOB method,It works only in an anonymous block but fails when create a named procedure.
What do you think the problem might be. Thanks for the help.


Tom Kyte

Followup  

March 24, 2011 - 8:35 am UTC

You can use get_ddl in plsql.

You are not able to in a compiled stored procedure because you have not been granted direct access to the object. You have access to it via a role. try this:

SQL> set role none;
SQL> select dbms_metadata.get_ddl......

It will fail - proving you have access via a role.

read:

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551289900368934430

dbms_metadata

March 25, 2011 - 7:46 am UTC

Reviewer: Michel Cadot from France


By default EXECUTE privilege on DBMS_METADATA is granted to PUBLIC.

You can find an example of procedure to get object DDL using dbms_metadata at http://www.orafaq.com/forum/mv/msg/168998/500434/102589/#msg_500434

Regards
Michel

Tom Kyte

Followup  

March 25, 2011 - 11:20 am UTC

right, so someone must have revoked on it.

OR

their block of code is referencing DUA L with a space as mentioned above. given what we have to work with, that is all we can say

dbms_metadata.get_ddl for TABLESPACE

June 28, 2011 - 1:40 pm UTC

Reviewer: Ravi B from Bay Area,CA

Tom,

When i run the following command i get DDL for tablespace as well as resize commands with negative number.

select dbms_metadata.get_ddl('TABLESPACE', '<TABLESPACE_NAME>') DDL
from dual;

CREATE TABLESPACE "<TABLESPACE_NAME>" DATAFILE
'/u01/oradata/ora10g/file01.dbf' SIZE 3221225472,
'/u01/oradata/ora10g/file02.dbf' SIZE 2147483648
LOGGING OFFLINE PERMANENT BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

ALTER DATABASE DATAFILE '/u01/oradata/ora10g/file01.dbf' RESIZE -16384

ALTER DATABASE DATAFILE '/u01/oradata/ora10g/file02.dbf' RESIZE -16384

When i run create tablespace command, it runs fine.

But alter statement gives the following error:

ERROR at line 1:
ORA-02237: invalid file size

Any idea why?
Tom Kyte

Followup  

June 28, 2011 - 4:08 pm UTC

I'd say that is a bug - looks like a rollover (signed integer) issue. Please utilize support to file that one, thanks

How can i do schema refresh in 8i database

March 12, 2012 - 4:01 am UTC

Reviewer: rabindra patro from INDIA

Hi All,

I want to do a schema refresh for 8i database.My source and target database are 8i database.

Using "Exp" and system user/password i took the schema backup.
ran the below command

exp system/*********** owner=FGFGR file=hdhdhdjhd.dmp log=dhdhhdhd.log


1.Now please provide me the steps. How can i import that in my target database.

2. What actions i have to took before schema import in target databse.

Tom Kyte

Followup  

March 12, 2012 - 8:04 am UTC

1) use imp? just run it -

imp u/p file=.... full=y


2) make sure the user exists. make sure the necessary tablespaces exist. make sure the user has the right set of privs.

I use folowing function to retrieve DLLs - it might be usefull

March 15, 2012 - 6:23 am UTC

Reviewer: Jaroslav from Prague, Czech

CREATE OR REPLACE FUNCTION read_remote_ddl( p_type varchar2,p_name varchar2,p_schema varchar2,p_link varchar2 default null)
RETURN CLOB IS
c_ddl CLOB;
v_ddl varchar(4000);
v_link varchar2(30);
v_sql varchar(400);
v_len number;
BEGIN
c_ddl:='';
IF p_link is null
THEN
v_sql:= 'select dbms_metadata.get_ddl('''||p_type||''','''||p_name||''','''||p_schema||''') from dual';
execute immediate v_sql into c_ddl;
ELSE
v_sql:= 'select dbms_lob.getlength@'||p_link||'(dbms_metadata.get_ddl@'||p_link||'('''||p_type||''','''||p_name||''','''||p_schema||''')) from dual@'||p_link;
execute immediate v_sql into v_len;
for i in 0..trunc(v_len/4000) loop
v_sql:= 'select dbms_lob.substr@'||p_link||'(dbms_metadata.get_ddl@'||p_link||'('''||p_type||''','''||p_name||''','''||p_schema||'''),4000,'||to_char(i*4000+1)||') from dual@'||p_link;
execute immediate v_sql into v_ddl;
c_ddl:=c_ddl||v_ddl;
end loop;
END IF;
RETURN c_ddl;
END read_remote_ddl;
/

USAGE :
select read_remote_ddl(object_type,object_name,'SCOTT') from user_objects or
select read_remote_ddl(object_type,object_name,'SCOTT','DB_LINK') from user_objects@db_link

NOTE: for source based types (Procedures, functions, etc.) is better direct select to user_source. Function works there too, but is slower.

NOTE on NOTE : In case of wrapped source dba_source select doesn't work and must be user my function based on dbms_metadata package

TIP: to compare schemas in different databases use :
create table ddls_to_compare as
select 'source' as orig, object_type,object_name, read_remote_ddl(object_type,object_name,'SCOTT') as ddl from user_objects
union all
select 'TARGET' as orig, object_type,object_name,read_remote_ddl(object_type,object_name,'SCOTT','DB_LINK') as ddl from user_objects@db_link;

select object_type,object_name,DDL count(*) from ddls_to_compare group by object_type,object_name,DDL having count(*)<2;

Tom Kyte

Followup  

March 15, 2012 - 7:48 am UTC

please make something like this be AUTHID CURRENT_USER to try and avoid sql injection attacks.

also consider using dbms_assert to perform a sanity check on the inputs.

May 14, 2012 - 1:15 pm UTC

Reviewer: A reader

We are planning for a database upgrade from Oracle 10.2.0.4 to Oracle 11.2.0.2, also migrating from solaris to linux. I am using transportable tablespaces to move the data. This is a vendor provided database and some of the code came from them and we dont have those code in our source control. So I am thinking of bringing all the code objects manually. What is the best way to extract ddl from a database ? dbms_metadata.get_ddl or expdp content=metadata_only. What is included in these two and what are not ?
Tom Kyte

Followup  

May 14, 2012 - 11:42 pm UTC

I'd use expdp - you do not have to write code for that. If you use dbms_metadata, you'd be writing quite a bit.

expdp uses the equivalent of dbms_metadata to do its job.

August 16, 2013 - 4:07 pm UTC

Reviewer: Paul from Albuquerque, NM USA

With respect to the original question, exp (and expdp) produce a binary output file. How does one produce a flat plain-text file?
Tom Kyte

Followup  

August 28, 2013 - 5:17 pm UTC

see: http://asktom.oracle.com/Misc/httpasktomoraclecomtkyte.html for some approaches.


sqldeveloper has many ways to unload to text files (csv, xml, html, etc)

apex ( http://apex.oracle.com/ ) does as well.

How can I get the DDLS of schema

August 22, 2013 - 2:56 pm UTC

Reviewer: Thanny from Montréal, Canada

How to get DDL for each object in the schema using DBMS_METADATA? My requirement is create the object that I need using this DDL definitions.
Tom Kyte

Followup  

August 28, 2013 - 6:12 pm UTC

please do just read on dbms_metadata in the documentation.

that is exactly what it does - use the get_ddl subroutine on the objects you want.


or search for dbms_metadata.get_ddl on this site, tons of existing examples.

September 04, 2013 - 8:01 pm UTC

Reviewer: Amine Sekkai from Algeria

Hi Tom,
We want to change our DB character set to UTF8. So here is the strategy :
1. run exp system/aa full=y rows=n file=aa.dmp in the source db,
1.1. unload data through unloader.sql (produces a two files (at least) : user_tab.data and user_tab.ctl to inject them to sqlldr

2. run imp system/aa full=y file=aa.dmp in the target db (UTF8),
3. load data using sqlldr

Now to speed up things, we want to :
1. disable all constraints at each table load : easy, just loop over the table constraints and switch them to disable
2. disable also indexes on the table : ??? sqlldr does this with direct path only.
So drop them and re-create them again ?

Thanks
Amine

Tom Kyte

Followup  

September 09, 2013 - 10:53 am UTC

odds are you can just alter your database - without exp. you don't have to dump and load:
http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch2charset.htm#i1007203

they have the step by steps there.



presuming you are on 10g, you would be using datapump anyway - never exp again. that way , you can go database to database without hitting disk.



so, I wouldn't be doing any of your steps at all, just alter the database, or if you cannot for whatever reason, use datapump - in parallel - over the network (no files)

To extract DDL in a procedure

March 01, 2017 - 10:01 am UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

Hello Tom, I have been looking to find answer to this question for a long time. I am not a developer. I got some hints about it on some websites, but haven't been able to code it. Could you please tell me if its possible extract and store the index DDL of some tables in a PL/SQL procedure so that those indexes can be dropped before a huge insert and then re-created from the extracted ddl saved in a variable. I read somewhere that CLOBS can be used, and tried it too, but wasn't successful. I am disabling the other constraints and making the non-unique indexes unusable. The unique indexes don't allow me to insert data until I drop them. I am using 11.2.0.4.
Can you please help?
Thanks
Chris Saxon

Followup  

March 01, 2017 - 2:47 pm UTC

You can use dbms_metadata to get index definitions:

create table t (
  x int constraint u unique 
);

select dbms_metadata.get_ddl('INDEX', 'U')
from   dual;

DBMS_METADATA.GET_DDL('INDEX','U')                                                

  CREATE UNIQUE INDEX "CHRIS"."U" ON "CHRIS"."T" ("X") 
  PCTFREE 10 INITRANS 2  

March 02, 2017 - 11:18 pm UTC

Reviewer: A reader

Hi,
Thanks for your reply to my query..
I do plan to use dbms_metadata.get_ddl to extract the ddl of the index. But my question was regarding how I can store the output of dbms_metadata.get_ddl into some kind of a variable (CLOB maybe?) inside a procedure so that it automatically extracts that ddl during runtime and stores it in that variable, then drops the index, and does the table insert, and then it will use the value of that variable to re-create that index.
Just wanted to see how I can use a CLOB for this (if CLOB is the only option to store the ddl of index that may be few number of lines)
Connor McDonald

Followup  

March 03, 2017 - 5:28 am UTC

SQL> create table ddl ( c clob );

Table created.

SQL> create table t (
  2    x int constraint u unique
  3  );

Table created.

SQL>
SQL> insert into ddl
  2  select dbms_metadata.get_ddl('INDEX', 'U')
  3  from   dual;

1 row created.


To extract DDL in a procedure

March 04, 2017 - 1:23 am UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

Thanks very much !
So I'm thinking of now implementing this sequence of logic in my procedure:
- Extract the number of unique indexes in the table
- Based on that output, create the same number of temporary tables having CLOB column to store the index ddl
- Disable constraints, make indexes unusable, Truncate table, insert data, enable constraints, and rebuild indexes

Could you please help me understand how I can use the ddl stored in the table column to create the index? I'll have to pipe the output of "select c from ddl" to "execute immediate" in the procedure
Thanks again for your help on this !
Connor McDonald

Followup  

March 04, 2017 - 2:02 am UTC

Having read back your *requirement* I think you might be over-engineering things.

Your aim should be able to perform maintenance operations *without* needing to *drop* things. You just want to make things "temporarily" unavaialable.

So to reload a table, rather than drop/create, the process is more:

alter table T modify constraint CONSTRAINT1 disable;
alter table T modify constraint CONSTRAINT2 disable;
alter table T modify constraint CONSTRAINT3 disable;

truncate table T;

alter index T_IX1 unusable;
alter index T_IX2 unusable;
alter index T_IX3 unusable;

[load data into T]

alter index T_IX1 rebuild;
alter index T_IX2 rebuild;
alter index T_IX3 rebuild;

alter table T modify constraint CONSTRAINT1 enable novalidate;
alter table T modify constraint CONSTRAINT2 enable novalidate;
alter table T modify constraint CONSTRAINT3 enable novalidate;

alter table T modify constraint CONSTRAINT1 enable validate;
alter table T modify constraint CONSTRAINT2 enable validate;
alter table T modify constraint CONSTRAINT3 enable validate;


Then you dont need the full DDL, just the list of indexes and constraints

To extract DDL in a procedure

March 04, 2017 - 9:14 am UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

This doesn't seem to work when there is a unique index created on the table.
If I don't drop the unique index, it fails at the time of loading data, with this error:

ERROR at line 1:
ORA-01502: index 'SYSREAD.T_IND2' or partition of such index is in unusable
state

That is why I have to extract the ddl of the unique index, then drop the index, load the table, and then recreate it from that extracted ddl.
Connor McDonald

Followup  

March 05, 2017 - 12:32 am UTC

True - that is the exception. But if they are unique *constraints* then you'll be fine.


To extract DDL in a procedure

March 04, 2017 - 8:27 pm UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

Could you pls show me show I can use the ddl stored in the table column to create the index? Would I have to pipe the output of "select c from ddl" to "execute immediate" in the procedure?
I've tried looking in documentations and online sources, but couldn't find.
Connor McDonald

Followup  

March 05, 2017 - 12:37 am UTC

Let's you wanted to *execute* that ddl. You could do something like this:

SQL> create table ddl ( c clob );

Table created.

SQL> create table t (
  2     x int constraint u unique
  3  );

Table created.

SQL>
SQL> insert into ddl
  2  select dbms_metadata.get_ddl('TABLE', 'T')
  3  from   dual;

1 row created.

SQL>
SQL> select c from ddl;

C
--------------------------------------------------------------------

  CREATE TABLE "MCDONAC"."T"
   (    "X" NUMBER(*,0),
         CONSTRAINT "U" UNIQUE ("X")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"


1 row selected.

SQL>
SQL> drop table t;

Table dropped.

SQL>   declare
  2    my_ddl clob;
  3  begin
  4    select c into my_ddl from ddl;
  5    execute immediate my_ddl;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>   desc t
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 X                                      NUMBER(38)

SQL>



Of course, you could just spool the ddl out to the file etc as well.

To extract DDL in a procedure

March 05, 2017 - 4:32 am UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

Awesome !!
That is EXACTLY what I wanted to know ! Perfect !
Thank you very very much !!

To extract DDL in a procedure

March 05, 2017 - 4:38 am UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

Just to confirm, it should still work even in the cases where dbms_metadata.get_ddl displays output where sometimes table names get split into two separate lines, correct?
I haven't tested this on all my current tables yet.. only two of them. But just wanted to confirm from you.

To extract DDL in a procedure

March 05, 2017 - 7:02 am UTC

Reviewer: DBA_without_much_developer_knowledge from Portland, OR

It works fine even for the ddl that displays with broken lines.
Just tested that!
Thanks again !
Chris Saxon

Followup  

March 06, 2017 - 3:04 pm UTC

Glad to hear it.

GET_DDL for partition RANGE versus LIST

March 26, 2020 - 6:22 am UTC

Reviewer: henryfg42 from Brisbane, QLD, Australia

I have a table,using PARTITION BY RANGE, INTERVAL with many partitions.
The generated DDL, using DBMS_METADATA.GET_DDL is
  CREATE TABLE "HISTORY"."TSS_PAYROLL_VERIFICATION"
   (    "FORTNIGHT_ENDING" DATE,
  ...
   )
  TABLESPACE "HISTORY"
  PARTITION BY RANGE ("FORTNIGHT_ENDING") INTERVAL (NUMTODSINTERVAL(14,'DAY'))
 (PARTITION "PARTITION1"  VALUES LESS THAN (TO_DATE(' 2013-07-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
  TABLESPACE "HISTORY" ) ;

This is beautiful Works fine.
For another table, using PARTITION BY LIST , AUTOMATIC, also with many partitions,
the generated DDL is:
  CREATE TABLE "CITSS"."CI_TB_INCREM_ELIGIBILITY"
   (    "REPORT_INCREM" CHAR(1),
...
        "CAS_PREVINCELIGINCR_RULE_SQNBR" NUMBER DEFAULT 1
   ) 
  TABLESPACE "CRYSTAL01"
  PARTITION BY LIST ("INCREM_RUN_NUMBER") AUTOMATIC
 (PARTITION "P_3040"  VALUES (3040) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "CRYSTAL01" ,
 PARTITION "SYS_P6852"  VALUES (3125) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "CRYSTAL01" ,
 PARTITION "SYS_P6853"  VALUES (3133) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "CRYSTAL01" ,
 PARTITION "SYS_P6854"  ...


Obviously it generates the SYS_P* partitions. In principle GET_DDL should NEVER generate code with SYS_ names...
I really just want the 'base' partition, the one used to create the original table with one partition, in this case P_3040.
Why does it work for RANGE, but not for LIST?
Or is this a 'feature' for which I should raise an SR?
Chris Saxon

Followup  

March 26, 2020 - 11:49 am UTC

In principle GET_DDL should NEVER generate code with SYS_ names...

Why not? If the database has created the partitions, then they will have SYS names...

create table t (
  c1 int
) partition by list ( c1 ) automatic (
  partition p1 values ( 1 )
);

insert into t 
with rws as (
  select level x from dual
  connect by level <= 5
)
  select * from rws;
  
commit;

select partition_name 
from   user_tab_partitions
where  table_name = 'T';

PARTITION_NAME   
P1                
SYS_P3140         
SYS_P3141         
SYS_P3142         
SYS_P3143   

set long 10000
select dbms_metadata.get_ddl ( 'TABLE', 'T' ) 
from   dual;

DBMS_METADATA.GET_DDL('TABLE','T')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

  CREATE TABLE "CHRIS"."T" 
   ( "C1" NUMBER(*,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
  PARTITION BY LIST ("C1") AUTOMATIC 
 (PARTITION "P1"  VALUES (1) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" , 
 PARTITION "SYS_P3140"  VALUES (2) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" , ... ) 

GET_DDL PARTITION metadata

March 26, 2020 - 12:51 pm UTC

Reviewer: henryfg42 from Brisbane, QLD, Australia

Thanks for the response, but..
1. i don't want the SYS partitions in the DDL because I use the DDL to create the table in another database I don't want the SYS_P* numbers to potentially clash. I am presuming that each database has its own sequence for partition numbering.

2. Consistency. Why can't it just generate the 'base', like PARTITION RANGE can, as such: (and create other partitions on the fly, if and when required using the local partition sequence)
CREATE TABLE "CITSS"."CI_TB_INCREM_ELIGIBILITY"
   (    "REPORT_INCREM" CHAR(1),
...
        "CAS_PREVINCELIGINCR_RULE_SQNBR" NUMBER DEFAULT 1
   ) 
  TABLESPACE "CRYSTAL01"
  PARTITION BY LIST ("INCREM_RUN_NUMBER") AUTOMATIC
 (PARTITION "P_3040"  VALUES (3040) 
  TABLESPACE "CRYSTAL01" );


3. why does it generate two TABLESPACE clauses...

4. Now I have to write code for my destination database to remove unused partitions..

Chris Saxon

Followup  

March 26, 2020 - 4:12 pm UTC

1. You can use the same partition names in different tables. So there are no name clash issues:

create table t1 (
  c1 int
) partition by list ( c1 ) automatic (
  partition lp1 values ( 1 )
);

create table t2 (
  c1 int
) partition by list ( c1 ) automatic (
  partition lp1 values ( 1 )
);

select table_name from user_tab_partitions
where  partition_name = 'LP1';

TABLE_NAME   
T1            
T2   


2. You can include the system created interval partitions by setting the transform parameter EXPORT to true:

exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);


Auto-list partitions don't seem to respect this behaviour though... I'll raise a bug for it.

3. You mean at the table level and partition level?

Because you can define both of these!

Table level tablespace => default for new partitions
Partition level => where this partition specifically is stored

4. Well you don't have to write code to remove them. You could leave them there...

Plus if you have these values in one database, there's a reasonable chance at you'll store at least some of the same values in the other database too. Meaning you'll have partitions for these values at some point anyway.

GET_DDL for partition RANGE versus LIST

March 26, 2020 - 12:57 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Chris,

.....Why not? If the database has created the partitions, then they will have SYS names...

If that is right, then the dbms_metadata.get_ddl works different for Interval partitioned tables and Automatic list partitioned tables.

please see the below demo from 12.2 database.

dbms_metadata.get_ddl - on Interval partitioned tables get only those user defined partitioned, but not the system created partition.

dbms_metadata.get_ddl - on Automatic List partitioned tables get both user defined partitions and system created partitions.

so why the behaviour is different for two different partition schems?

demo@PDB1> create table t1
  2  partition by range( created )
  3  interval( numtoyminterval(1,'year') )
  4  ( partition p2010 values less than ( to_date('01-Jan-2011','dd-mon-yyyy') ) )
  5  as
  6  select owner,object_type,object_id,created
  7  from all_objects ;

Table created.

demo@PDB1>
demo@PDB1> create table t2
  2  partition by list( owner ) automatic
  3  ( partition P0 values ('SCOTT') )
  4  as
  5  select owner,object_type,object_id,created
  6  from all_objects;

Table created.

demo@PDB1> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'STORAGE',FALSE);

PL/SQL procedure successfully completed.

demo@PDB1> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);

PL/SQL procedure successfully completed.

demo@PDB1> select dbms_metadata.get_ddl('TABLE','T1') from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
--------------------------------------------------------------------------------

  CREATE TABLE "DEMO"."T1"
   (    "OWNER" VARCHAR2(128) NOT NULL ENABLE,
        "OBJECT_TYPE" VARCHAR2(23),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "CREATED" DATE NOT NULL ENABLE
   )
  PARTITION BY RANGE ("CREATED") INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
 (PARTITION "P2010"  VALUES LESS THAN (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM
-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) )


demo@PDB1> select dbms_metadata.get_ddl('TABLE','T2') from dual;

DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------

  CREATE TABLE "DEMO"."T2"
   (    "OWNER" VARCHAR2(128) NOT NULL ENABLE,
        "OBJECT_TYPE" VARCHAR2(23),
        "OBJECT_ID" NUMBER NOT NULL ENABLE,
        "CREATED" DATE NOT NULL ENABLE
   )
  PARTITION BY LIST ("OWNER") AUTOMATIC
 (PARTITION "P0"  VALUES ('SCOTT') ,
 PARTITION "SYS_P8988"  VALUES ('SYS') ,
 PARTITION "SYS_P8989"  VALUES ('PUBLIC') ,
 PARTITION "SYS_P8990"  VALUES ('OUTLN') ,
 PARTITION "SYS_P8991"  VALUES ('SYSTEM') ,
 PARTITION "SYS_P8992"  VALUES ('GSMADMIN_INTERNAL') ,
 PARTITION "SYS_P8993"  VALUES ('DBSFWUSER') ,
 PARTITION "SYS_P8994"  VALUES ('ORACLE_OCM') ,
 PARTITION "SYS_P8995"  VALUES ('AUDSYS') ,
 PARTITION "SYS_P8996"  VALUES ('DBSNMP') ,
 PARTITION "SYS_P8997"  VALUES ('APPQOSSYS') ,
 PARTITION "SYS_P8998"  VALUES ('REMOTE_SCHEDULER_AGENT') ,
 PARTITION "SYS_P8999"  VALUES ('XDB') ,
 PARTITION "SYS_P9000"  VALUES ('WMSYS') ,
 PARTITION "SYS_P9001"  VALUES ('OJVMSYS') ,
 PARTITION "SYS_P9002"  VALUES ('CTXSYS') ,
 PARTITION "SYS_P9003"  VALUES ('ORDSYS') ,
 PARTITION "SYS_P9004"  VALUES ('ORDDATA') ,
 PARTITION "SYS_P9005"  VALUES ('ORDPLUGINS') ,
 PARTITION "SYS_P9006"  VALUES ('SI_INFORMTN_SCHEMA') ,
 PARTITION "SYS_P9007"  VALUES ('MDSYS') ,
 PARTITION "SYS_P9008"  VALUES ('OLAPSYS') ,
 PARTITION "SYS_P9009"  VALUES ('LBACSYS') ,
 PARTITION "SYS_P9010"  VALUES ('DVF') ,
 PARTITION "SYS_P9011"  VALUES ('DVSYS') ,
 PARTITION "SYS_P9012"  VALUES ('HR') ,
 PARTITION "SYS_P9013"  VALUES ('DEMO') ,
 PARTITION "SYS_P9014"  VALUES ('RAJESH') ,
 PARTITION "SYS_P9015"  VALUES ('C##COMMON_USER') ,
 PARTITION "SYS_P9016"  VALUES ('C##RAJESH') ,
 PARTITION "SYS_P9017"  VALUES ('A') ,
 PARTITION "SYS_P9018"  VALUES ('B') ,
 PARTITION "SYS_P9019"  VALUES ('C##GLOBAL_USER') )


demo@PDB1> col partition_name format a10
demo@PDB1> col high_value format a40 trunc
demo@PDB1> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name ='T1';

PARTITION_ HIGH_VALUE
---------- ----------------------------------------
P2010      TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-M
SYS_P8968  TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-M
SYS_P8969  TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-M
SYS_P8970  TO_DATE(' 2021-01-01 00:00:00', 'SYYYY-M

demo@PDB1>

Chris Saxon

Followup  

March 26, 2020 - 4:35 pm UTC

I've raised a bug for this.

In the interim use

March 27, 2020 - 9:50 am UTC

Reviewer: A reader


For interval based partitions use:

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'EXPORT',true);

Cheers!

name=Export option in docs

March 27, 2020 - 9:53 am UTC

Reviewer: Rajeshwaran, Jeyabal

Thanks Chris.

...exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);...

where this option of name=EXPORT is listed?

checked the documentation, https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_METADATA.html#GUID-0ACD6E3B-C699-469F-8CC3-32F9DA20CA60

couldn't find, kinldy advice.
Chris Saxon

Followup  

March 27, 2020 - 10:40 am UTC

Turns out this is undocumented...

It's discussed in MOS note 1491820.1.