How find all the partition information for the oracle table

Question

I have a partitioned table t that is partitioned on a column using list partition,

I want to query out all the partition and also its corresponding column values.

It is the same as in PLSQL Developer: when I view sql for this table, it shows all the partitions like:

  partition DATA_20180522 values (20180522)
    tablespace DMSCMDAT
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    )

I google searched,but I don't find the answer.

Answer 1

To find information on partitions you can query the ALL_TAB_PARTITIONS view:

SELECT *
  FROM ALL_TAB_PARTITIONS

and for much of the other info on a table you can query ALL_TABLES:

SELECT *
  FROM ALL_TABLES

If you want to recreate the source code for a table you need to write code. A good place to start is the code in this question at asktom.oracle.com.

Best of luck.

Answer 2

As the other answer suggested, you could look at USER|ALL|DBA_TAB_PARTITIONS. But if your goal is to get the DDL, then the DBMS_METADATA.GET_DDL function is likely going to be loads easier. Use the SET_TRANSFORM_PARAM options to tailor the output to your liking.