| Full Import - In other words . [message #73616] |
Wed, 02 June 2004 14:32  |
Troy
Messages: 10
Registered: October 2000
|
Junior Member |
|
|
With ref. to my previous question. .....What is the cleaner + easier way of doing a full import of a full export file into a database. What I usually do is drop all the database users/owners manually and then do the full import. But it takes time if there are too many users to delete.
Thanks
|
|
|
|
| Re: Full Import - In other words . [message #73618 is a reply to message #73616] |
Thu, 03 June 2004 01:41  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
If the Object already exists & parameter IGNORE is not set to Y then export will Terminate but if it is set to Y then it will log error & will try to append data into tables & the rows which do not exist in the Table will be inserted,but if there are any rows in the Import file which were updated , they will not be overwritten.
Best & faster way is to Drop the Objects & Users before doing FULL import.
As far as dropping is concerned you can automate it by making simple SQL's & spool them into file like:
Spool users1.lst
SELECT 'DROP USER '||USERNAME||' CASCADE;' FROM DBA_USERS
WHERE DEFAULT_TABLESPACE NOT IN('SYSTEM','TOOLS','USERS');
'DROPUSER'||USERNAME||'CASCADE;'
-------------------------------------------------
DROP USER SCOTT CASCADE;
DROP USER ALS CASCADE;
DROP USER RCTA CASCADE;
SPOOL OFF;
Now simply run the script file.
Note that by Using CASCADE keyword:
1.Oracle invalidates, but does not drop, the following objects in other schemas:
views or synonyms for objects in the dropped user's schema;
and stored procedures, functions, or packages that query objects in the dropped user's schema.
2.Oracle does not drop materialized views on tables or views in the dropped user's schema, but if you specify CASCADE, the materialized views can no longer be refreshed.
3.Oracle drops all triggers in the user's schema.
4.Oracle does not drop roles created by the user.
HTH
Regards
Himanshu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
