Home » SQL & PL/SQL » SQL & PL/SQL » Can i do this???
icon2.gif  Can i do this??? [message #240385] Thu, 24 May 2007 13:31 Go to next message
chmlaeeque
Messages: 59
Registered: September 2006
Member
Hay Every1 is it possible that we can drop all the tables with an single query, or we can only exports table only as we have 50 tables in a user and we want to export the tables or drop the tables only.
M. Laeeque A.
Re: Can i do this??? [message #240391 is a reply to message #240385] Thu, 24 May 2007 13:41 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
yes

set heading off
spool /export/home/oracle/drop.sql
select ' drop table '||table_name||' cascade constraints ;' from dba_tables
where owner='ORACLE'

spool off
@/export/home/oracle/drop.sql

[Updated on: Thu, 24 May 2007 13:54]

Report message to a moderator

Re: Can i do this??? [message #240405 is a reply to message #240385] Thu, 24 May 2007 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the relation between drop and export?

Regards
Michel
Re: Can i do this??? [message #240782 is a reply to message #240405] Fri, 25 May 2007 15:13 Go to previous messageGo to next message
chmlaeeque
Messages: 59
Registered: September 2006
Member
These r 2 dif. question that how can we explain the sql to export only tables of required user.
and same statement for drop tables,
Re: Can i do this??? [message #240813 is a reply to message #240782] Sat, 26 May 2007 00:15 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
export tables of a user:
C:\>exp help=y

Export: Release 10.2.0.3.0 - Production on Sam. Mai 26 07:14:17 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.



You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export

Notice the TABLES keyword.

Drop all tables of a user: see DreamzZ post.

Regards
Michel
Previous Topic: UTL_FILE writing performance
Next Topic: regarding blob data
Goto Forum:
  


Current Time: Thu Dec 08 08:44:19 CST 2016

Total time taken to generate the page: 0.13574 seconds