由於user_tab_partitions.high_value欄位是long,直接用sql視窗查不到該欄位資訊，用to_char.轉換不可以，那這種情況怎麼能實現user_tab_partitions和其他表關聯或顯示long欄位呢？

具體步驟依據如下：

① 追加到表檢視：

SQL> desc user_tab_partitions

Name Null? Type

----------------------------------------- -------- ----------------------------

TABLE_NAME VARCHAR2(30 )

COMPOSITE VARCHAR2(3 )

PARTITION_NAME VARCHAR2(30 )

SUBPARTITION_COUNT NUMBER

HIGH_VALUE LONG

...

SQL>

SQL> create table lottery (table_name VARCHAR2( 30),partition_name VARCHAR2 (30),aa CLOB);

SQL> insert into lottery select p.table_name,p.partition_name,to_lob(p.high_value) from user_tab_partitions p;

② 用迴圈列印/儲存表方式

例如：

DECLARE

CURSOR C_CONS IS

SELECT P.TABLE_NAME, P.PARTITION_NAME, P.HIGH_VALUE

FROM USER_TAB_PARTITIONS P;

P_HIGH_VALUE VARCHAR2(4000);

P_TABLE_NAME VARCHAR2(4000);

P_PARTITION_NAME VARCHAR2(4000);

BEGIN

EXECUTE IMMEDIATE ('CREATE TABLE LOTTERY (TABLE_NAME VARCHAR2(4000),PARTITION_NAME VARCHAR2(4000),HIGH_VALUE VARCHAR2(4000))');

FOR P_C_CONS IN C_CONS LOOP

P_HIGH_VALUE := P_C_CONS.HIGH_VALUE;

P_TABLE_NAME := P_C_CONS.TABLE_NAME;

P_PARTITION_NAME := P_C_CONS.PARTITION_NAME;

EXECUTE IMMEDIATE ('INSERT INTO LOTTERY VALUES(''' || P_TABLE_NAME ||

''',''' || P_PARTITION_NAME || ''',''' ||

P_HIGH_VALUE || ''')');

--DBMS_OUTPUT.PUT_LINE('INSERT INTO LOTTERY VALUES(''' || P_TABLE_NAME ||''',''' || P_PARTITION_NAME || ''',''' ||P_HIGH_VALUE || ''')');

END LOOP;

COMMIT;

END;

③ 用【plsql developer】command window...查詢

例如：

SELECT P.TABLE_NAME, P.PARTITION_NAME, P.HIGH_VALUE FROM USER_TAB_PARTITIONS P;

此條目發表在 Oracle 分類目錄。將固定連線加入收藏夾。

Oracle user_tab_partitions.high_value欄位 LONG型別 讀取的幾種方法

相關文章