Home » SQL & PL/SQL » SQL & PL/SQL » List of partitions (12c)
List of partitions [message #686074] Tue, 07 June 2022 13:09 Go to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
Hi

table is Range partitioned on a Date_column.12c version.
need a sql statement that i can run in expdp where clause to get list of partition names to be pass as parameter in exp par
for a given table, whose high value is less than sysdate- 3 Months,
lets say in June i am running so as of Feb-2022 it should list all the partition names.

Please advise on sql that i can pass in where clause of exp par file.

Thanks
Re: List of partitions [message #686075 is a reply to message #686074] Tue, 07 June 2022 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ What is your version number with 4 decimals as requested in your previous topic?

Michel Cadot wrote on Thu, 15 October 2020 21:53

Post the result of:
select * from v$version;

2/ What is your problem finding "list of partition names... whose high value is less than sysdate- 3 Months"?

[Updated on: Tue, 07 June 2022 13:40]

Report message to a moderator

Re: List of partitions [message #686076 is a reply to message #686075] Tue, 07 June 2022 14:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
DECLARE
    CURSOR V_CUR
      IS
        SELECT  TABLE_OWNER,
                TABLE_NAME,
                PARTITION_NAME,
                HIGH_VALUE
          FROM  DBA_TAB_PARTITIONS
          WHERE TABLE_OWNER = '&TABLE_OWNER'
            AND TABLE_NAME = '&TABLE_NAME';
    V_HIGH_VALUE DATE;
BEGIN
    FOR V_REC IN V_CUR LOOP
      EXECUTE IMMEDIATE 'SELECT ' || V_REC.HIGH_VALUE || ' FROM DUAL'
        INTO V_HIGH_VALUE;
      DBMS_OUTPUT.PUT_LINE(RPAD(V_REC.PARTITION_NAME,31) || TO_CHAR(V_HIGH_VALUE,'YYYY/MM/DD HH24:MI:SS'));
    END LOOP;
END;
/
This should be enough to get you going.

SY.
Re: List of partitions [message #686077 is a reply to message #686075] Tue, 07 June 2022 14:50 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
version 12.1.0.2.0

Partition Names is like " P_YYYYMM "
Re: List of partitions [message #686078 is a reply to message #686077] Tue, 07 June 2022 15:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Then it is as simple as:

SELECT  TABLE_OWNER,
        TABLE_NAME,
        PARTITION_NAME
  FROM  DBA_TAB_PARTITIONS
  WHERE TABLE_OWNER = '&TABLE_OWNER'
    AND TABLE_NAME = '&TABLE_NAME'
    AND PARTITION_NAME < TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'MM'),-3),'"P_"YYYYMM')
/
will return all monthly partitions older than last 3 full and incomplete current month.

SY.
Re: List of partitions [message #686080 is a reply to message #686076] Wed, 08 June 2022 10:18 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
Thanks All.
If i do a partition table export with only few lists of partitions will it create the table in target during import or do we need to pre-create the partition table before import.

and after a few days when we export the remaining partitions, we can import directly on an existing partition table correct .

thanks
Re: List of partitions [message #686081 is a reply to message #686080] Wed, 08 June 2022 11:20 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
No, it will not crete table during import. You do need to pre-create table.

SY.
Previous Topic: file transfer mechanism (FTP/SFTP/SSHFTP) using purely PL/SQL
Next Topic: Directory Name with UTL_FILE.FOPEN()
Goto Forum:
  


Current Time: Thu Mar 28 18:13:58 CDT 2024