possible to view script?

[sammybee]

Hi All,

I know in Toad you can view the creation script of a view is there any way of doing this in sql plus?

Cheers

Sam

 

[LKBrwnDBA]

Try:

select text from user_views
  where view_name='MY_VIEW';

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Turkbear]

Hi,
Before issuing that, enter
set long = 20000 ( or some other big number) so that all the text will be displayed..

 

[sammybee]

fantastic, only prob I now have is the view doesn't exist in user views or dbs_tables, is there anywhere else to check?

Cheers

Sam

 

[KenCunningham]

dba_views?

 

[Turkbear]

Hi,
Just 3 possible locations( mostly):
User_Views ( those created by the logged in user)
All_Views ( those the logged in user has access to)
Dba_Views ( If DBA loggen in, all views that exist)

 

[sammybee]

cheers everyone for your help I've found what I thought was a view under all tables;-). Is there anyway of getting the creation script from here?

Many thanks

Sam

 

[martincab]

-- pass as command line parammeters the owner and the view_name to the folowing script

create table st(lin number(5), txt varchar2(4000));
declare
ln number := 1;
lt varchar2(2000);
begin
For R in (select text from all_views
where owner = upper('&1')
and view_name = upper('&2'))
Loop
Loop
lt := getline(r.text, ln);
Exit when ln = 0;
if ln = 1 then
insert into st values (0,'create or replace view '||'&1'||'.'||
'&2'||' as ');
end if;
insert into st values (ln, lt);
ln := ln + 1;
end loop;
insert into st values (9999,'/');
End Loop;
end;
/
set heading off feedback off pagesize 0 verify off
select txt a from st order by lin;
drop table st;

Martin Cabrera
Oracle dba

 

[martincab]

-- Sorry, i forgot splitline code:
-- i forgot getline here is the code:
Create or replace function rhu.getline
( TxtL long,
NLin in out number,
LSep varchar2 default chr(10))
return varchar2 is

/* 20/01/2005: Returns line number NLin of TxtL, if the line doesn't exists
Returns null and NLin = 0. Uses LSep as line separator (by
default is CR (ascii 10)).
by martincab@hotmail.com */
txt varchar2(32767) := txtl;
ini number;
fin number;
begin
if NLin = 1 then
ini := 0;
Else
ini := instr(txt, LSep, 1, NLin-1);
end if;
fin := greatest(instr(txt, LSep, 1, NLin)-1,0);
if (ini = 0 and fin = 0 and NLin = 1 and length(txt) > 0) or
(ini > 0 and fin = 0)
then
fin := length(txt);
end if;
if fin > ini then
return (substr(txt, ini+1, fin-ini));
else
NLin := 0;
return (null);
end if;
end;
/


Martin Cabrera
Oracle dba

 

[sammybee]

Hi Martin,

Cheers for your help, the only thing is I am now trying to find the creation script of a table and not a view - do you know if this is stored anywhere?

Cheers

Sam

 

[KenCunningham]

Do you have an export file that would contain it?

 

[SantaMufasa]

Sammy,

To produce the create script for a table, you can use the following Oracle-supplied packaged procedure from the SQL*Plus prompt:

set long 50000
set pagesize 0
select dbms_metadata.get_ddl('<obj. type>','<name>','<schema>') from dual;

Here is are a sample invocation and results from my invoking "get_ddl" for the Oracle University "s_emp" table:

**********************************************************************************
set long 50000
set pagesize 0
select dbms_metadata.get_ddl('TABLE','S_EMP','TEST') from dual;

  CREATE TABLE "TEST"."S_EMP"
   (    "ID" NUMBER(7,0) CONSTRAINT "S_EMP_ID_NN" NOT NULL ENABLE,
        "LAST_NAME" VARCHAR2(25) CONSTRAINT "S_EMP_LAST_NAME_NN" NOT NULL ENABLE,
        "FIRST_NAME" VARCHAR2(25),
        "USERID" VARCHAR2(8),
        "START_DATE" DATE,
        "COMMENTS" VARCHAR2(255),
        "MANAGER_ID" NUMBER(7,0),
        "TITLE" VARCHAR2(25),
        "DEPT_ID" NUMBER(7,0),
        "SALARY" NUMBER(11,2),
        "COMMISSION_PCT" NUMBER(4,2),
         CONSTRAINT "S_EMP_COMMISSION_PCT_CK" CHECK (commission_pct IN
        (10, 12.5, 15, 17.5, 20)) ENABLE,
         CONSTRAINT "S_EMP_ID_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DATA1"  ENABLE,
         CONSTRAINT "S_EMP_USERID_UK" UNIQUE ("USERID")
  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 "DATA1"  ENABLE,
         CONSTRAINT "S_EMP_MANAGER_ID_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "TEST"."S_EMP" ("ID") ENABLE,
         CONSTRAINT "S_EMP_DEPT_ID_FK" FOREIGN KEY ("DEPT_ID")
          REFERENCES "TEST"."S_DEPT" ("ID") ENABLE,
         CONSTRAINT "S_EMP_TITLE_FK" FOREIGN KEY ("TITLE")
          REFERENCES "TEST"."S_TITLE" ("TITLE") 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 "DATA1"

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:25 (25Jan05) UTC (aka "GMT" and "Zulu"),
@ 12:25 (25Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[sammybee]

Hi Dave,

Looks like it would have done the trick, unfortunately getting an error on the dbms_metadata.get_ddl and I don't think I can access the disks!

Cheers for your help

Sam

 

[SantaMufasa]

Sam,

Sorry...I forgot to confirm version. The dbms_metadata.get_ddl package runs on Oracle 9i and not Oracle 8i.

Ken alluded to a good solution, which involves using the contents of an export dump file of your subject table to obtain the DDL you want. The steps you need to follow are:
1) Obtain an export dump file that contains your subject table.
2) From the dump file, create a text file of the DDL for the table and its index(es). Here is an example of the same "S_EMP" table as in my example above, but the DDL results from the "imp" command:

imp tables=s_emp file=DHUNT.TEST.dump indexfile=EMP_DDL.sql userid=TEST/TEST

Import: Release 9.2.0.4.0 - Production on Wed Jan 26 13:20:22 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. . skipping table "S_EMP"

Import terminated successfully without warnings.

d:> type emp_ddl.sql

REM  CREATE TABLE "TEST"."S_EMP" ("ID" NUMBER(7, 0) CONSTRAINT
REM  "S_EMP_ID_NN" NOT NULL ENABLE, "LAST_NAME" VARCHAR2(25) CONSTRAINT
REM  "S_EMP_LAST_NAME_NN" NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(25),
REM  "USERID" VARCHAR2(8), "START_DATE" DATE, "COMMENTS" VARCHAR2(255),
REM  "MANAGER_ID" NUMBER(7, 0), "TITLE" VARCHAR2(25), "DEPT_ID" NUMBER(7,
REM  0), "SALARY" NUMBER(11, 2), "COMMISSION_PCT" NUMBER(4, 2)) PCTFREE 10
REM  PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1
REM  FREELIST GROUPS 1) TABLESPACE "DATA1" LOGGING NOCOMPRESS ;
REM  ... 25 rows
CONNECT TEST;
CREATE UNIQUE INDEX "TEST"."S_EMP_ID_PK" ON "S_EMP" ("ID" ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
1) TABLESPACE "DATA1" LOGGING ;
CREATE UNIQUE INDEX "TEST"."S_EMP_USERID_UK" ON "S_EMP" ("USERID" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1
FREELIST GROUPS 1) TABLESPACE "DATA1" LOGGING ;
REM  ALTER TABLE "TEST"."S_EMP" ADD CONSTRAINT "S_EMP_COMMISSION_PCT_CK"
REM  CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)) ENABLE NOVALIDATE ;
REM  ALTER TABLE "TEST"."S_EMP" ADD CONSTRAINT "S_EMP_ID_PK" PRIMARY KEY
REM  ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL
REM  65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "DATA1" LOGGING
REM  ENABLE ;
REM  ALTER TABLE "TEST"."S_EMP" ADD CONSTRAINT "S_EMP_USERID_UK" UNIQUE
REM  ("USERID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
REM  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
REM  "DATA1" LOGGING ENABLE ;
REM  ALTER TABLE "TEST"."S_EMP" ADD CONSTRAINT "S_EMP_MANAGER_ID_FK"
REM  FOREIGN KEY ("MANAGER_ID") REFERENCES "S_EMP" ("ID") ENABLE
REM  NOVALIDATE ;
REM  ALTER TABLE "TEST"."S_EMP" ADD CONSTRAINT "S_EMP_DEPT_ID_FK" FOREIGN
REM  KEY ("DEPT_ID") REFERENCES "S_DEPT" ("ID") ENABLE NOVALIDATE ;
REM  ALTER TABLE "TEST"."S_EMP" ADD CONSTRAINT "S_EMP_TITLE_FK" FOREIGN
REM  KEY ("TITLE") REFERENCES "S_TITLE" ("TITLE") ENABLE NOVALIDATE ;
REM  ALTER TABLE "TEST"."S_EMP" ENABLE CONSTRAINT
REM  "S_EMP_COMMISSION_PCT_CK" ;
REM  ALTER TABLE "TEST"."S_EMP" ENABLE CONSTRAINT "S_EMP_MANAGER_ID_FK" ;
REM  ALTER TABLE "TEST"."S_EMP" ENABLE CONSTRAINT "S_EMP_DEPT_ID_FK" ;
REM  ALTER TABLE "TEST"."S_EMP" ENABLE CONSTRAINT "S_EMP_TITLE_FK" ;

Notice that the resulting DDL code has all the command lines remarked out except for the "CREATE INDEX..." command. You can edit the DDL code file to get rid of the "REM " strings and do any other editing you need.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 20:30 (26Jan05) UTC (aka "GMT" and "Zulu"),
@ 13:30 (26Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.