delete old partitions oracle 12c

[Roshan]

Oracle Database 12c
Red Hat Linux 6.7

Oracle Database 12c

Red Hat Linux 6

 

Hi,

I have a script to delete old partitions. I have executed the script, and old partitions are still present.

Please find attached.

deletescript.txt

Please advise.

Regards,
Roshan

[ErmanArslansOracleBlog]

Hi,

Send me the dbms_outputs that your script has produced.. (your script is written to output the commands before executing them)

open the dbms output, execute the script and send me the output..

The partition names that the script decides for dropping, can be wrong.

This may be related with the if statement there.. ( IF V_HIGH_VALUE <= TRUNC(SYSDATE,'MM'))

anyways, Send me the dbms_outputs that your script has produced..

2017-05-11 9:44 GMT+03:00 Roshan [via Erman Arslan's Oracle Forum] <[hidden email]>:

Oracle Database 12c
Red Hat Linux 6.7

Oracle Database 12c

Red Hat Linux 6

 

Hi,

I have a script to delete old partitions. I have executed the script, and old partitions are still present.

Please find attached.

deletescript.txt

Please advise.

Regards,
Roshan

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/delete-old-partitions-oracle-12c-tp2672.html

To start a new topic under Scripting Bash / Sql / Perl, email [hidden email]
To unsubscribe from Erman Arslan's Oracle Forum, click here.
NAML

--

[Roshan]

Hi,

thanks Erman

I can see only 'PLSQL procedure completed successfully'
scriptdbms.PNG

[ErmanArslansOracleBlog]

enable dbms output for the tool that you are using.
You should be able to see the dbms_outputs after that.

[Roshan]

scriptdbms2.PNG

[Roshan]

I see no output

[Roshan]

I noticed when I omit INTERVAL='YES', the output does not display. Else, it would display

SELECT  PARTITION_NAME,

                  HIGH_VALUE

            FROM  USER_TAB_PARTITIONS

            WHERE TABLE_NAME = 'MOBILE_TAP';

MOBILE_TAP_2016_08 TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MOBILE_TAP_2016_09 TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MOBILE_TAP_2016_10 TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MOBILE_TAP_2016_11 TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MOBILE_TAP_2016_12 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
MOBILE_TAP_2017_01 TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Regards,
Roshan

[ErmanArslansOracleBlog]

Aha...

Then the partitions that your scripts decide to drop, are not in interval type.
why do you se interval=yes?
--If your partitions are not in interval type, your script won't drop anything.

[ErmanArslansOracleBlog]

Info:the value stored in the INTERVAL column indicates whether the partition is in the interval section of an interval partitioned table (YES) or whether the partition is in the range section (NO)