drop tablespace including contents!!!!

[raf]

Hi,
I created an instance Oracle with more tablespace.
I have a tablespace "art" with four users (CREATE USER john IDENTIFIED BY john DEFAULT TABLESPACE "art" TEMPORARY TABLESPACE TEMP)
owner is user TOM.
Now I must drop the tablespace "art" and import again the dmp.
I tried without drop the users but I get error and when I connect with a user of "art" I get ORA-00942.

DROP TABLESPACE "art" including contents;
CREATE TABLESPACE "art" DATAFILE 'c:\art\art01.dbf' SIZE 300M REUSE DEFAULT STORAGE
(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE 0) ONLINE;

imp system/manager@mydb frouser=sam touser=TOM file=c:\art.dmp log=c:\art.log

Must I drop also the users (TOM, john......)?????


Thanks
Raf

[TimHall]

Hi.

I'm a bit confused by your mail. When you "owner is TOM" I'm assuming you don't mean the owner of the tablespace, because this is not true.

If you recreate you tablespace, check the quotas on the tablespace and then start the import it should be fine.

Cheers

Tim...

[bang_dba]

Users belong to the whole database; but not to a particular tablespace.

If you want to drop the user along with his associated objects and foreign keys that depend on the tables owned by the user;

then u have to say

SQL>DROP USER SCOTT CASCADE;


If u say,
SQL>Drop tablespace TBS including contents;
This will drop all the objects in the tablespace, no matter who is owning the objects.

Well with the imp command, the object owned by SAM in the art.dmp will be imported into the TOM schema in the target database. Well the user TOM has to be present in advance for this.

[davey23uk]

and the tablespace has to be present as the import file will reference it

[raf]

ok.....but the users john...... are created and after I create synonym to see TOM's table
if I don't drop synonym John view the table of Tom after inport???


Thanks
Raf

[bang_dba]

Originally posted by davey23uk
and the tablespace has to be present as the import file will reference it

Not necessarily, unless otherwise it is a full import form a full exp dump.

[TimHall]

Hi.

If you don't drop the users then all synonyms and views will still be present as these aren't physical objects and so are not actually stored in a users default tablespace. They are stored in the data dictionary in the SYSTEM tablespace.

Once the import is complete they will work again so long as the correct privileges have been granted. If Tom has a view that uses a synonym to a table in John's schema, John must grant privilege to Tom before he can see the table.

If the export file contains the grants and you import with grants=y then this will be done by the import. If not you will need to do it manually.

Cheers

Tim...

[raf]

ok..thanks....TimHall

now I have this problem:
when I write: imp system/manager@mydb frouser=sam touser=TOM file=c:\art.dmp log=c:\art.log

I get this errors:
CREATE SEQUENCE "AFM_ACTIVITY_LOG_S" MINVALUE 1 MAXVALUE 999999999999999999"
"999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER NOCYCLE"
IMP-00015: following statement failed because the object already exists:
. . importing table "AM" 0 rows imported
. . importing table "AMP" 0 rows imported
. . importing table "ACTIVITY" 0 rows imported
. . importing table "ABC" 0 rows imported
. . importing table "ADV 0 rows imported
. . importing table "ATS" 96 rows imported

CREATE FORCE VIEW "AFM_ARSTUD"."GPC" ("GP_ID","FL_ID","
""BL_ID","SITE_ID","FLCOMGP","BLCOMGP","STCOMGP","FLCOMSRV","BLCOMSRV","STCO"
"MSRV") AS "
"SELECT gp.gp_id AS gp_id, fl.fl_id AS fl_id, bl.bl_id AS bl_id, site.site_i"
"d AS site_id, ( gp.area * fl.area_fl_comn_gp / DECODE(fl.area_gp_dp,0,99999"
"99999,fl.area_gp_dp)) AS flcomgp, ( gp.area * bl.area_bl_comn_gp / DECODE(b"
"l.area_gp_dp,0,9999999999,bl.area_gp_dp)) AS blcomgp, NVL( ( gp.area * site"
".area_st_comn_gp / DECODE(site.area_gp_dp,0,9999999999,site.area_gp_dp)),0)"
" AS stcomgp, ( gp.area * fl.area_fl_comn_serv / DECODE(fl.area_gp_dp,0,9999"
"999999,fl.area_gp_dp)) AS flcomsrv, ( gp.area * bl.area_bl_comn_serv / DECO"
"DE(bl.area_gp_dp,0,9999999999,bl.area_gp_dp)) AS blcomsrv, NVL( ( gp.area *"
" site.area_st_comn_serv / DECODE(site.area_gp_dp,0,9999999999,site.area_gp_"
"dp)),0) AS stcomsrv FROM gp, fl, bl, site WHERE gp.dp_id IS NOT NULL AND gp"
".fl_id = fl.fl_id AND gp.bl_id = bl.bl_id AND fl.bl_id = bl.bl_id AND site."
"site_id (+) = bl.site_id"
IMP-00015: following statement failed because the object already exists:
CREATE TRIGGER "AD"."WOAUTONUMBER" BEFORE INSERT ON "AFM"."WO" REFE"
"RENCING OLD AS OLD NEW AS NEW_RECORD FOR EACH ROW "
.................................................................................................... ...IMP-00015: following statement failed because the object already exists:


These error are important? and how can I avoide it?
Must I drop user TOM?
I don't want drop it.

Raf

[balajiyes]

just add IGNORE=Y to this...
imp system/manager@mydb frouser=sam touser=TOM file=c:\art.dmp log=c:\art.log IGNORE=Y

check this

http://storacle.princeton.edu:9001/o...ch02.htm#34966

[marek_wiechula]

Originally posted by balajiyes
just add IGNORE=Y to this...
imp system/manager@mydb frouser=sam touser=TOM file=c:\art.dmp log=c:\art.log IGNORE=Y

Using the IGNORE=Y option will cause the import to re-import whatever it can, even if the object already exists. The problem with this that a table, like ATS with 96 rows, will import the 96 rows again. If the table is not protected by a primary key you will end up with duplicated rows. Unnamed constraints will be duplicated.

If there is a difference between the pre-existing table and the import table, then you will get errors about the column mismatch when it tries to import the rows - but you will still have the old table structure.

It is better to look at the actual cases of this message and decide if it is really a problem. If it failed to create a table because the table already existed - then this is a problem only if the table that is already there is not the one you want and the one that you failed to import is the one you want. In that case it is better to drop the table and then import it.