This content has been marked as final. Show 10 replies
Before Import, you can automatically truncate all your tables by running result of this command:
- - - - - - - - - - - - - - - - - - - - -
SELECT 'truncate table ' || table_name || ' /' FROM user_tables
Kamran Agayev A. (10g OCP)
Use import's DESTROY parameter. Set it to Y.
Edited by: Kecskemethy on Feb 23, 2009 2:59 AM
I would like to add in the import script itself..i dont want to do separately using truncate table <tabel_name>
DESTROY=y option did not help me out..
is there any other option..so it helps me to resolve tihis suituation
conventional imp utility doesn't have this feature. if you are using Oracle10g, you can use datapump for this type of exports and imports which gives the options to truncate table and load data.
DESTROY option in imp doesn't work as it is completely related to tablespace datafiles.
So, In 9i there is no option in built..if i want to truncate ..i need to do it manually am i correct ? or is theere any way
In conventional Import utility, there is no option to which will first truncate table and then reinsert into table. You need to do it manually before involing Import.
YOu can check this with Import help.
Note: Destroy=y is not for this purpose. It is basically for datafile reuse option.
You can let Import prompt you for parameters by entering the IMP command followed by your username/password: Example: IMP SCOTT/TIGER Or, you can control how Import runs by entering the IMP command followed by various arguments. To specify parameters, you use keywords: Format: IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN) Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N 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 import entire file (N) BUFFER size of data buffer FROMUSER list of owner usernames FILE input files (EXPDAT.DMP) TOUSER list of usernames SHOW just list file contents (N) TABLES list of table names IGNORE ignore create errors (N) RECORDLENGTH length of IO record GRANTS import grants (Y) INCTYPE incremental import type INDEXES import indexes (Y) COMMIT commit array insert (N) ROWS import data rows (Y) PARFILE parameter filename LOG log file of screen output CONSTRAINTS import constraints (Y) DESTROY overwrite tablespace data file (N) INDEXFILE write table/index info to specified file SKIP_UNUSABLE_INDEXES skip maintenance of unusable indexes (N) FEEDBACK display progress every x rows(0) TOID_NOVALIDATE skip validation of specified type ids FILESIZE maximum size of each dump file STATISTICS import precomputed statistics (always) 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 COMPILE compile procedures, packages, and functions (Y) STREAMS_CONFIGURATION import streams general metadata (Y) STREAMS_INSTANTIATION import streams instantiation metadata (N) The following keywords only apply to transportable tablespaces TRANSPORT_TABLESPACE import transportable tablespace metadata (N) TABLESPACES tablespaces to be transported into database DATAFILES datafiles to be transported into database TTS_OWNERS users that own data in the transportable tablespace set Import terminated successfully without warnings.
This feature(truncate the table) has been introduced in the Datapump import in Oracle 10g.
You can go through this.
The 'destroy' option is not meant for what your are trying to achieve.
It is do with datafile rather than tables.
While doing import, if import tries to create tablespace with a datafile which already exists, it will fail if destroy is set to 'N' (default). If you set it to 'Y', you are allowing the import to reuse the datafile and it will corrupt your data if that datafile is being used elsewhere.
Please use this with CARE.
The best option for you is to either drop or truncate the tables.
Thannk you so much for the useful info..