1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Drop tablespace based on date

Discussion in 'SQL PL/SQL' started by tspoon, Mar 28, 2017.

  1. tspoon

    tspoon Starter

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United Kingdom
    Hi,

    I have a number of tablespaces based over a 4-year period and would like a script which when executed drops the oldest tablespace and datafiles after a period of 30 days / 60 days / 90 days etc - which will be executed via cron once per month

    Does anyone have a script where they can upload and I can test

    My tablespaces are listed as below:-

    SQL> select file_name, tablespace_name from dba_data_files where tablespace_name like '%HISTORY_%';

    FILE_NAME TABLESPACE_NAME
    -------------------------------------------------------------------------------- ---------------
    /u02/app/oracle/oradata/datafile/HISTORY_JAN2017.dbf HISTORY_JAN2017
    /u02/app/oracle/oradata/datafile/HISTORY_FEB2017.dbf HISTORY_FEB2017
    /u02/app/oracle/oradata/datafile/HISTORY_MAR2017.dbf HISTORY_MAR2017
    /u02/app/oracle/oradata/datafile/HISTORY_APR2017.dbf HISTORY_APR2017
    /u02/app/oracle/oradata/datafile/HISTORY_MAY2017.dbf HISTORY_MAY2017
    /u02/app/oracle/oradata/datafile/HISTORY_JUN2017.dbf HISTORY_JUN2017
    /u02/app/oracle/oradata/datafile/HISTORY_JUL2017.dbf HISTORY_JUL2017
    /u02/app/oracle/oradata/datafile/HISTORY_AUG2017.dbf HISTORY_AUG2017
    /u02/app/oracle/oradata/datafile/HISTORY_SEP2017.dbf HISTORY_SEP2017
    /u02/app/oracle/oradata/datafile/HISTORY_OCT2017.dbf HISTORY_OCT2017
    /u02/app/oracle/oradata/datafile/HISTORY_NOV2017.dbf HISTORY_NOV2017
    /u02/app/oracle/oradata/datafile/HISTORY_DEC2017.dbf HISTORY_DEC2017

    12 rows selected.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What date are you referencing with this delete procedure? If it's the date in the tablespace name that could be misleading (unless the tablespace is named for the date when it is created, although I suspect with the naming convention you provided these tablespaces could be created well in advance of the date in the name and are designed to contain data in the given range).

    The task is fairly easy to do yet you ask others to do your work for you; remember we are volunteers here, not paid support personnel, and have our own jobs to do during the day. Also, the task you ask about isn't one that is common; in such cases partitioned tables are used to contain date-restricted data and the partitions are dropped when the data in them is no longer needed.

    What have you written to fulfill this requirement? Show us your code and we'll help you fix what may not be working. Do not ask us to write code for you.
     
  3. tspoon

    tspoon Starter

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    United Kingdom
    Apologies