Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Drop Tablespace after specific period, Script to drop tablespace after x number of days
tspoon
post Mar 14 2017, 07:50 AM
Post #1


Member
**

Group: Members
Posts: 22
Joined: 12-June 05
Member No.: 2,287



Hi,

I manually drop partitioned tablespaces once they have reached a specific timespan

How do I go about performing this on an automated process via a script

I have come up with a similar script, but need the script to drop the tablespaces after 90 days


BEGIN
FOR ts in (SELECT distinct TABLESPACE_NAME
FROM DBA_DATAFILES WHERE TABLESPACE_NAME CREATED < SYSDATE -90) LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP TABLESPACE ' || ts.TABLESPACE_NAME || ' INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS';
END;
END LOOP;
END;


The tablespace names are created in the form of "CREATE TABLESPACE HISTORY_JAN2016","CREATE TABLESPACE HISTORY_FEB2016 " etc...

My script will run once per month and remove the oldest tablespace (after 90 days) upon the condition stated above


Can anyone assist with my script

thanks in advance


--------------------
tspoon
Junior Oracle DBA
United Kingdom

Oracle is a never ending learning curve
learn to share and share to enhance one's knowledge

Ron Jeremy circa 1974
Go to the top of the page
 
+Quote Post
burleson
post Mar 15 2017, 03:30 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,335
Joined: 26-January 04
Member No.: 13



Hi,

>> need the script to drop the tablespaces after 90 days

You could use dbms_scheduler:

http://www.dba-oracle.com/job_scheduling/s..._attributes.htm

But I would do this using a crontab:

http://www.remote-dba.net/t_script_linux_crontab.htm

This book has working examples of shell scripts:

http://www.rampant-books.com/book_0701_shell_scripting.htm






--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 26th March 2017 - 05:59 AM