drop tablespace including contents!!!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: drop tablespace including contents!!!!

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    Join Date
    Sep 2002
    Posts
    376
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    and the tablespace has to be present as the import file will reference it

  5. #5
    Join Date
    Jul 2002
    Posts
    228
    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

  6. #6
    Join Date
    Sep 2002
    Posts
    376
    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.

  7. #7
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    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...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  8. #8
    Join Date
    Jul 2002
    Posts
    228
    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

  9. #9
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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
    Cheers!
    OraKid.

  10. #10
    Join Date
    Aug 2003
    Location
    Dhahran
    Posts
    33
    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.

Posting Permissions

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



Click Here to Expand Forum to Full Width