Oracle date or timestamp PARTITION columns



  • Is there a query I can issue to tell if a PARTITION column is a date or timestamp.

    For example this should retun timestamp

    
    CREATE TABLE ts (
    num_val NUMBER(6),
    ts_val TIMESTAMP)
      PARTITION BY RANGE (ts_val) 
      INTERVAL ( NUMTODSINTERVAL (1, 'DAY') ) ( 
        PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP '2021-01-01 00:00:00.000000')
      );
    

    This should RETURN DATE

    
    CREATE TABLE dts (
       dt   DATE
    )
    PARTITION BY RANGE (dt)
    INTERVAL (NUMTODSINTERVAL(7,'DAY'))
    (
       PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
    );
    



  • Use this one:

    SELECT TABLE_NAME, PARTITIONING_TYPE, COLUMN_NAME, DATA_TYPE
    FROM USER_PART_TABLES 
        JOIN USER_PART_KEY_COLUMNS ON NAME = TABLE_NAME
        JOIN USER_TAB_COLS USING (TABLE_NAME, COLUMN_NAME)
    where OBJECT_TYPE = 'TABLE';
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2