Archive

Posts Tagged ‘CONTAINERS’

CONTAINERS clause and LONG columns


We were working on a monitoring script using HIGH_VLAUES in CDB_TAB_PARTITIONS and found the HIGH_VALUE column is missing in the CDB_TAB_PARTITIONS while the column is existing in DBA_TAB_PARTITIONS. Couldn’t find any reference in Oracle documentation (may be need a better search!) Why the column is missing in CDA view. Looking at the CDB_TAB_PARTITIONS view definition, oracle is using CONTAINERS clause to fetch all partition information, except the column HIGH_VALUE

SQL>; select TEXT from dba_views where lower(VIEW_NAME) ='cdb_tab_partitions';
TEXT
--------------------------------------------------------------------------------
SELECT "TABLE_OWNER","TABLE_NAME","COMPOSITE","PARTITION_NAME","SUBPARTITION_COU
NT","HIGH_VALUE_LENGTH","PARTITION_POSITION","TABLESPACE_NAME","PCT_FREE","PCT_U
SED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTENT","MAX_EX
TENT","MAX_SIZE","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","COMPRES
SION","COMPRESS_FOR","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT",
"AVG_ROW_LEN","SAMPLE_SIZE","LAST_ANALYZED","BUFFER_POOL","FLASH_CACHE","CELL_FL
ASH_CACHE","GLOBAL_STATS","USER_STATS","IS_NESTED","PARENT_TABLE_PARTITION","INT
ERVAL","SEGMENT_CREATED","INDEXING","INMEMORY","INMEMORY_PRIORITY","INMEMORY_DIS
TRIBUTE","INMEMORY_COMPRESSION","INMEMORY_DUPLICATE","CON_ID" FROM CONTAINERS("S
YS"."DBA_TAB_PARTITIONS")

I tried to read the column individually, but only HIGH_VALUE is not visible in the root.

SQL> conn / as sysdba
Connected.
SQL> select table_name from containers(dba_tab_partitions) where rownum   select high_value from containers(dba_tab_partitions) where rownum  alter session set container = PDBL0020_1 ;
Session altered.
SQL> create table test (C1 varchar2(25), C2 number, c3 long );
Table created.
SQL> insert into test values ('Thomas',3,'This is a test1');
1 row created.

It is mandatory that we must create the table in CDB$ROOT as well to access the table from the root container.

SQL> conn / as sysdba
Connected.
SQL> create table test (C1 varchar2(25), C2 number, c3 long );
Table created.

Now read the read the TEST table from the PDB

SQL> alter session set container = PDBL0020_1 ;
Session altered.
SQL> select * from containers(test) where con_id =3;
C1                                C2 C3                  CON_ID
------------------------- ---------- --------------- ----------
Thomas                             3 This is a test1          3

You can see all the 3 columns including C3, the LONG columns. Now connect to the root, and issue the same query.

SQL> conn / as sysdba
Connected.
SQL> select * from containers(test) where con_id =3;
C1                                C2     CON_ID
------------------------- ---------- ----------
Thomas                             3          3

Yes, column C3, which is LONG data type is missing! That means you can’t really access the LONG data type using CONTAINERS clause which is not a good thing. I haven’t seen any workaround or fix for this yet, please comment if you find any!
Oracle version

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

 

Advertisements
Categories: Oracle Tags: ,