Home » SQL & PL/SQL » SQL & PL/SQL » How can I dump all table ,storeprocedure,view's schema
|
|
| 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   |
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 |
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 .
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 00:10:48 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
