Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!
Solved
Procedure to drop tables
Posted on 2013-01-03
I'm trying to make a package to drop tables, create tables, build tables with new data and schedule this process to happen nightly.
Can someone help me with my "drop tables" procedure and give me an idea how i'd want that to look?
The Proc
The PKG Body
Can someone help me with my "drop tables" procedure and give me an idea how i'd want that to look?
The Proc
PROCEDURE DROP_ARCHIVED_TABLES IS
BEGIN
NULL;
END;
The PKG Body
CREATE OR REPLACE PACKAGE BODY DROP_CREATE_TABLES_NIGHTLY IS
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* procedure 'W' is a wrapper for DBMS output. Placed at top of package to make globally available*/
PROCEDURE W(STR VARCHAR2) IS
BEGIN
/* setting variable to simpler to use STR variable for outputting */
DBMS_OUTPUT.PUT_LINE(STR);
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* procedure purpose: For dropping previously archived tables so that new ones can be created */
PROCEDURE DROP_ARCHIVED_TABLES IS
BEGIN
NULL;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE CREATE_NEW_ARCHIVED_TABLES IS
BEGIN
EXCEPTION
WHEN OTHERS THEN
/* will output an error message and indicate where errors exist */
W('EXCEPTION: ' || SQLCODE || ' IN ARCHIVE_ALL_TABLES. ERROR MESSAGE: ' || SQLERRM);
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE BUILD_ARCHIVED_TABLES IS
BEGIN
NULL;
END;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN
/*
package begin will be null for compiling purposes...
would use for cleaning up but unnecessary to touch otherwise
*/
NULL;
END DROP_CREATE_TABLES_NIGHTLY;
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
2-
2
- +2
8 Comments
Active today
To drop a table in a package, you need to use EXECUTE IMMEDIATE, so your procedure would look something like this:
If you are going to recreate the same table later within the process, why not just truncate the tables?
PROCEDURE DROP_ARCHIVED_TABLES IS
BEGIN
EXECUTE IMMEDIATE 'drop table tab1';
EXECUTE IMMEDIATE 'drop table tab2';
EXECUTE IMMEDIATE 'drop table tab3';
EXECUTE IMMEDIATE 'drop table tab4';
END;
If you are going to recreate the same table later within the process, why not just truncate the tables?
I suppose I could truncate in the same proc instead. Would you suggest that instead of drop? What benefit is there to trunc vs drop in this case?
Active 1 day ago
if you truncate tables, you will get rid of the data and then you can insert new data instead of dropping and re-creating the tables. It all depends what are you trying to accomplish
Active 1 day ago
To me, unless you want to remove table definitions, truncating would be a much better and faster solution
in addition to what experts have mentioned above..
it all depends on your requirement too..
dropping of the table will also drop its indexes and constraints,triggers,grant
it all depends on your requirement too..
dropping of the table will also drop its indexes and constraints,triggers,grant
Active today
How are your archived tables identified? For example, is there a specific naming convention that identifies them? Assuming something like tablenames ending in "ARCH", you could create a procedure such as -
drop_archive_tbls is
v_sql varchar2(255);
begin
for t in (select table_name from dba_tables where table_name like '%ARCH') loop
v_sql := 'drop table '||t.table_name||' purge';
execute immediate v_sql;
end loop;
end drop_archive_tbls;
Note - you may want to add a cascade constraints clause to your drop statement.
drop_archive_tbls is
v_sql varchar2(255);
begin
for t in (select table_name from dba_tables where table_name like '%ARCH') loop
v_sql := 'drop table '||t.table_name||' purge';
execute immediate v_sql;
end loop;
end drop_archive_tbls;
Note - you may want to add a cascade constraints clause to your drop statement.
Active today
Additionally, a truncate would be faster than a drop and create. If you keep the storage rather than release it, inserts could be faster as you would be doing less space allocation.
It all depends on the structure of the tables. Referential integrity constraints would get in the way of truncates. Drop and recreate you have to be aware of indexes, privileges, triggers, etc. If anything is added that references the tables, you have to be sure to update your packages to reflect that.
It all depends on the structure of the tables. Referential integrity constraints would get in the way of truncates. Drop and recreate you have to be aware of indexes, privileges, triggers, etc. If anything is added that references the tables, you have to be sure to update your packages to reflect that.
Featured Post
Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to Create User-Defined Aggregates in Oracle
Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime? I'm talking things like:
* Dropping columns
* Shrinking allocated space
* Removing chained blocks and restoring the PCTFREE
* Re-or…
This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month7 days, 23 hours left to enroll
715 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.