Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: drop tablespace including contents!!!!

    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

  2. #2
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Raf,

    I am not fully understanding, you said you tried to connect as user Art when Art is a tablespace name ?

    Are you simply trying to drop the tablespace Art that is the default tablespace for John etc. ?

    If so:

    SQL> drop user john cascade;

    etc for each user that has objects in the Art tablespace.

    then:

    SQL> drop tablespace Art;

    Then you should be able to create as before and make it the default tablespace for your users.

    Hope it helps,
    Breen.

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Sorry,

    Then do the import.

    Rgs,
    Breen.

  4. #4
    Join Date
    Jul 2002
    Posts
    227
    ok.....but (for users john.....) I create synonym to see TOM's table
    I don't want drop users john........
    if I don't drop synonym, John view the table of Tom after import???


    Thanks
    Raf

  5. #5
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Raf,

    I see what you mean and I think the synonyms will be valid after the import as the import grants the select on Tom's table to John and the synonym should be validated as soon as the grants are given.

    Rgs,
    Breen.

  6. #6
    Join Date
    Jul 2002
    Posts
    227
    ok..thank....Breen

    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_d p)),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?

    Raf

  7. #7
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Raf,

    You are trying to import objects from Sam to Tom. It would appear that some / all of the objects you are trying to import into Tom already exist.

    If you are going to populate Tom's schema exclusively from this import then you should:

    SQL> drop user Tom cascade;

    SQL> create user tom .....

    Then do your import.

    Should be fine now.

    If you don't want to drop all of Tom's objects then you will have to drop only those objects that will be imported from Sam.

    Rgs,
    Breen.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •