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: 28
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,442
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
tspoon
post Mar 27 2017, 03:44 PM
Post #3


Member
**

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



Hi,

none of the examples mentioned above seem suitable - Maybe I need to be a little clearer

I have this

CODE
col FILE_NAME for a50
col tablespace_name for a15
col creation_time for a15
select a.file_name, a.tablespace_name, b.creation_time from dba_data_files a, v$datafile b where a.file_name=b.name and tablespace_name like 'HISTORY_% order by tablespace_name';


Which returns this output

CODE
FILE_NAME                                          TABLESPACE_NAME CREATION_TIME
-------------------------------------------------- --------------- ---------------
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_APR2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_AUG2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_DEC2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_FEB2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_JAN2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_JUL2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_JUN2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_MAR2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_MAY2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_NOV2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_OCT2017 24-MAR-17
/u02/app/oracle/oradata/ikealive/IKEALIVE/datafile HISTORY_SEP2017 24-MAR-17

12 rows selected.

Granted these tablespaces are created on the same day, but eventually I will have these tablespaces only created on the 1st of the month - i.e. HISTORY_JAN2018 - 01-JAN-2018, HISTORY_FEB2018 - 01-FEB-2018 etc ...

Also this only gives the date for the creation of the tablespace, where in fact I may have a datafile added after the tablerspace creation date

So I wondered if there was a query or statement that could drop a particular tablespace where the datafile has not been touched for 90 days (3 months /9 months / 12 months etc)

Thanks again 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 28 2017, 11:41 AM
Post #4


Advanced Member
***

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



Hi,

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

This sounds like a strange design, since tablespaces are a logical structure.

I'm not aware of anything in the data dictionary that keeps track of the last time a tablespace was updated.

However, you might write a script that joins into dba_tables where tablespace_name = 'XXX' and query for all objects within the tablespace:

http://www.dba-oracle.com/t_table_last_modified.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: 28th June 2017 - 05:33 PM