Home » SQL & PL/SQL » SQL & PL/SQL » How can I dump all table ,storeprocedure,view's schema
How can I dump all table ,storeprocedure,view's schema [message #36000] Mon, 29 October 2001 19:28 Go to next message
Jack Hu
Messages: 2
Registered: October 2001
Junior Member
Dear all:
How can I dump all table,storeprocedure,view
schema to a file
by plsql command?
thanks.


----------------------------------------------------------------------
Re: How can I dump all table ,storeprocedure,view's schema [message #36002 is a reply to message #36000] Mon, 29 October 2001 21:25 Go to previous messageGo to next message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
Getting the DDL -

Program >Command Prompt
C:>exp userid=test/test owner=test
C:>imp userid=test/test full=Y indexfile=c:/test.sql
--You will get only table structure here
--To get index,view and trigger structure
C:>imp userid=test/test full=Y show=Y

Extract any procedure,function or package

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 heading on
set termout on
set linesize 100

--Extract trigger structure
Select 'Create or Replace Trigger "'||trigger_name||'"'||chr(10)||decode(substr(1,1),'A','AFTER','B','BEFORE','I','INSTEAD OF')||CHR(10)||triggering_event||chr(10)||' ON"'||table_owner||'"."'||table_name||'"'||chr(10)||decode(instr(trigger_type,'EACH ROW'),0,null,'FOR EACH ROW')||chr(10),trigger_body
from user_triggers
where trigger_name=upper('&1')

----------------------------------------------------------------------
Re: How can I dump all table ,storeprocedure,view's schema [message #37485 is a reply to message #36000] Mon, 11 February 2002 18:18 Go to previous message
Satish Shrikhande
Messages: 167
Registered: October 2001
Senior Member
Mr. Shrikhande,

I am writing because I found a post that you left on the orafaq message
board, that concerns a problem I am having right now. I am trying to get a
dump of an Oracle Schema. Your message seems to go through this, but it is
kind of confusing to me. Please email me, and let me know if you can help a
little. Thanks.

here is the link to your post:

http://www.orafaq.net/msgboard/plsql/messages/5044.htm

Thanks again,

--
William A. Campbell, Associate Consultant
HBI Consulting 414.672.3346

My Reply to William

It's all about getting script of all the database objects , if you have created some table and you don't have the source code , where will you get that .
1. you can use third party tool like TOOD,Keep Tool , PL/SQL Developer .

Here test is the name of the schema
If you don't have that then use exp and imp to get the table structure , the first part is all about that .

Second one to Extract source code of any procedure,function or package

Third one is to extract source code of triggers .
Previous Topic: how can i dynamic create a table?urgent!
Next Topic: stored procedure
Goto Forum:
  


Current Time: Fri Aug 14 10:10:16 CDT 2020