CODEWITHSUNDEEP
sqloracle-databasepartition
dubey
dubey

Reputation: 351

How to get partition column name from partition table in oracle server

I am newbie in oracle and i want to get partition column name form partition name. same query working in MySQL:

SELECT PARTITION_EXPRESSION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? limit 1

this query will give me partition column name, same output I want in oracle

My oracle create table query is look like this

CREATE TABLE part_char_date  
(emp_no NUMBER(11)  , birth_date date  , first_name VARCHAR2(100),
last_name VARCHAR2(100)  , gender CHAR(1) , hire_date DATE  ) 
PARTITION BY RANGE  ( hire_date )  
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')), 
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),   
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')), 
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) )

now I want get partition column. any one help me

Upvotes: 6

Views: 30611

Answers (1)

Husqvik
Husqvik

Reputation: 5809

If you mean the partition key column then

SELECT
    OWNER,
    NAME,
    OBJECT_TYPE,
    COLUMN_NAME,
    COLUMN_POSITION
FROM
    ALL_PART_KEY_COLUMNS
WHERE
    NAME = 'PART_CHAR_DATE';

Upvotes: 10

Related Questions