Oracle – How to Find the Partition or Subpartition Name for an ID

oracleoracle-11goracle-11g-r2select

I know if I need to select from any partition I need to provide partition name

SELECT * FROM TABLE PARTITION(P1);

What if I need to know which partition a particular value belongs using a sql query then?

SELECT PARTITION_NAME FROM TABLE WHERE DOC_ID = 'S00102981655537O';

Error

ORA-00904: "PARTITION_NAME": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 25 Column: 8

Oracle version: 11g

Best Answer

First get rowid of the row and object id of the object using DBMS_ROWID.

SQL> insert into t3 values('S00102981655537O',sysdate,'IN-RJ');

1 row created.

SQL> select dbms_rowid.rowid_object(rowid) from t3 where doc_id='S00102981655537O';

DBMS_ROWID.ROWID_OBJECT(ROWID)
------------------------------
             93178

SQL> select subobject_name from user_objects where data_object_id=93178;

SUBOBJECT_
----------
SYS_SUBP81




SQL> select * from t3 subpartition(SYS_SUBP81);

DOC_ID           DOC_DATET STATE
-------------------- --------- ----------
S00102981655537O     31-MAR-16 IN-RJ

As we can see that row belongs to SYS_SUBP81 subpartition.

Related Solutions

Oracle ORA-00904 Error – Troubleshooting Query Issues

When ORA-00904: invalid identifier occurs, we must enter a valid column name as it is either missing or the one entered is invalid. This error most commonly happens when we are referencing an invalid alias in a select statement. Oracle's voice on ORA-00904 error:

ORA-00904 string: invalid identifier

Cause: The column name entered is either missing or invalid.

Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.

To avoid ORA-00904, column names must

begin with a letter.

consist only of alphanumeric and the special characters ($_#); other characters need double quotation marks around them.

be less than or equal to thirty characters.

Linux – ORA-00905: missing keyword

there's a space missing before to in 'to myuser'.

BEGIN
FOR X in (select * from all_tables where owner in ('TESTDTA', 'TESTCTL'))
LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON '
        ||X.owner||'.'||X.table_name||
        ' to TESTUSER';
END LOOP;
END;
/

Best Answer

Related Solutions

Oracle ORA-00904 Error – Troubleshooting Query Issues

ORA-00904 string: invalid identifier

Linux – ORA-00905: missing keyword

Related Question