Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    54

    Unanswered: delete the entire tablespace to delete the tables

    I will be installing a new version of a program created by programmers from our mother company.
    Included in this procedure is setting-up of the oracle 9i environment.
    Basically i need to delete tables since the structure of the current tables where revised.
    The creation of the new tables are already included in the Dump File that they sent to me.
    I would like to ask which is a better method.
    1.) instead of deleting all 15 tables, i just delete the entire tablespace
    then re-create the tablespace.
    then create the new tables by importing the Dump File given
    2.) one-by-one delete all the current tables
    then create the new tables by importing the Dump File given

    also please provide some pros and cons why the the other method is much better than the other...
    thnx... i really need some help on this one...

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Regarding the fact that it is about dropping 15 (fifteen!) tables, I guess it is not worth even thinking about any other solution but issuing DROP TABLE table_name statement; you might, however, copy-paste the same line in an editor and change only the table name at the end of the line and execute this file.

  3. #3
    Join Date
    Oct 2004
    Posts
    54
    So, it is still much better for me to drop the tables rather than
    deleting the entire tablespace???


    Quote Originally Posted by Littlefoot
    Regarding the fact that it is about dropping 15 (fifteen!) tables, I guess it is not worth even thinking about any other solution but issuing DROP TABLE table_name statement; you might, however, copy-paste the same line in an editor and change only the table name at the end of the line and execute this file.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I forgot to mention ... if there are enabled referential integrity constraints, pure DROP TABLE might fail; in that case, using DROP TABLE table_name CASCADE CONSTRAINTS might be a better option; it would first delete all foreign keys that reference the table to be dropped, and then drop the table itself.

    Is dropping tables instead of deleting a tablespace much better solution? Well, I'd say that this is just another possibility. You could even, of course, delete the whole database and install it from scratch, but I guess this would be a much worse solution than previous ones.

  5. #5
    Join Date
    Oct 2004
    Posts
    54
    thanks for the help...

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I suppose dropping the tablespace is an option if you know that all of the relevant tables are contained within that tablespace, and it contains nothing else you might want to keep.

    You could also drop the user, with similar cautions.

    It is worth being very clear on the difference between "DROP" and "DELETE" in SQL.
    Last edited by WilliamR; 04-19-06 at 06:16.

Posting Permissions

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