 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / June 2005how to boost drop table statement
Hello I want to know if there is a way in Oracle 9i to boost the drop for tables. I need to do a purging task, where all tables in the database are drop. Actually I am doing this with a normal SQLStatement DROP TABLE This dropping takes a lot of time, so I was asking me if there is a way to accelerate this process. Thanks for helping me How about dropping the database or schema? John http://schemaspy.sourceforge.net Oracle != Postgres This is a reason that dropping the database is not the right way for purging a database. In fact when in oracle I am recreating a database it takes a lot of time, because after the creation there have to be executed some plsql script(catalog.sql, catprog.sql) which takes a long long time. So my intention is: do the purge via JDBC, but use the oracle feature to gain this process. My only problem is, I don't know, how I should do it. Markus Markusin wrote on 17.06.2005 22:24: > Hello I want to know if there is a way in Oracle 9i to boost the drop > for tables. I need to do a purging task, where all tables in the [quoted text clipped - 3 lines] > This dropping takes a lot of time, so I was asking me if there is a way > to accelerate this process. This is not related to Java, so I would post this question to an Oracle newsgroup. You could try to TRUNCATE the tables first, but then they must not have foreign key constraints. Is it possible that some other process is accesing the tables? Then I think Oracle will wait with dropping the table until the other process is finished. Thomas > You could try to TRUNCATE the tables first, but then they must not have > foreign key constraints. You just have to do them in the correct order (assuming no recursive constraints). <plug> SchemaSpy grew out of a tool that I wrote to determine that order. It'll generate insertion order and deletion order lists of tables that are intended to be used by load/purge scripts. </plug> John http://schemaspy.sourceforge.net John Currier wrote on 18.06.2005 20:22: >>You could try to TRUNCATE the tables first, but then they must not have >>foreign key constraints. [quoted text clipped - 7 lines] > are intended to be used by load/purge scripts. > </plug> No that won't help. There is a limitation on the usage of TRUNCATE with Oracle. Quote from the manual (9.2): "You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table." Even if the order is right, it won't help here. Thomas > John Currier wrote on 18.06.2005 20:22: > >>You could try to TRUNCATE the tables first, but then they must not have [quoted text clipped - 18 lines] > > Even if the order is right, it won't help here. Assuming no cross-referencing referential integrity, ordering would truncate the table containing the FK reference first, thus it would seem to work. Apparently, Oracle doesn't support the CASCADE option for DROP, which would eliminate the similar ordering problem when using a list of DROP TABLEs. It also solves the cross-referencing problem. Lack of standards support does have its consequences ;^)  SignatureLee Fesperman, FFE Software, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) Lee Fesperman wrote on 19.06.2005 22:23: > Assuming no cross-referencing referential integrity, ordering would truncate the table > containing the FK reference first, thus it would seem to work. No. As long as there is a FK referenced *defined* Oracle will refuse to truncate a table. > Apparently, Oracle doesn't support the CASCADE option for DROP, which would eliminate > the similar ordering problem when using a list of DROP TABLEs. It also solves the > cross-referencing problem. Lack of standards support does have its consequences ;^) Oracle does support the CASCADE option for DROP... But then we're back to the question on how to speed that up. Thomas > Lee Fesperman wrote on 19.06.2005 22:23: > > Assuming no cross-referencing referential integrity, ordering would truncate > > the table containing the FK reference first, thus it would seem to work. > No. As long as there is a FK referenced *defined* Oracle will refuse to > truncate a table. I see, that makes sense, though it wouldn't be too hard for Oracle to check. However, dropping and restoring the constraint isn't too onerous. > > Apparently, Oracle doesn't support the CASCADE option for DROP, which would > > eliminate the similar ordering problem when using a list of DROP TABLEs. It [quoted text clipped - 3 lines] > Oracle does support the CASCADE option for DROP... But then we're back to > the question on how to speed that up. Actually, I was piggy-backing on your response to comment on the SchemaSpy capability to produce an ordered list. With the CASCADE option for DROP, the ordered list isn't needed ... just put CASCADE on every DROP. SignatureLee Fesperman, FFE Software, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.