Toad World® Forums

org. index table in table list grid showed as compressed

Toad 12.12.0.6

For IOT table defined as (known hr schema):

CREATE TABLE COUNTRIES
(
COUNTRY_ID CHAR(2 CHAR) CONSTRAINT COUNTRY_ID_NN NOT NULL,
COUNTRY_NAME VARCHAR2(40 CHAR),
REGION_ID NUMBER,
CONSTRAINT COUNTRY_C_ID_PK
PRIMARY KEY
(COUNTRY_ID)
ENABLE VALIDATE
)
ORGANIZATION INDEX
PCTTHRESHOLD 50
TABLESPACE ADBA_DATA
RESULT_CACHE (MODE DEFAULT)
NOPARALLEL
NOMONITORING
/

COMMENT ON COLUMN COUNTRIES.COUNTRY_ID IS 'Primary key of countries table.'
/

COMMENT ON COLUMN COUNTRIES.COUNTRY_NAME IS 'Country name'
/

COMMENT ON COLUMN COUNTRIES.REGION_ID IS 'Region ID for the country. Foreign key to region_id column in the departments table.'
/

-- Index COUNTRY_C_ID_PK is created automatically by Oracle with index organized table COUNTRIES.

--
-- Foreign Key Constraints for Table COUNTRIES

Prompt Foreign Key Constraints on Table COUNTRIES;
ALTER TABLE COUNTRIES ADD (
CONSTRAINT COUNTR_REG_FK
FOREIGN KEY (REGION_ID)
REFERENCES REGIONS (REGION_ID)
ENABLE VALIDATE)
/

Toad shows in tables list compression, which IMHO, should not be here.

Brg
Damir

I agree that COMPRESS_FOR should be null when COMPRESSION=‘DISABLED’, but we are just showing what is in the data dictionary. I would say this is Oracle’s bug.

C:\Users\Admin>sqlplus

SQL*Plus: Release 12.2.0.1.0 on Fri Aug 4 12:12:58 2017

Copyright © 1982, 2015, Oracle. All rights reserved.

Enter user-name: jdorlon@azure_12c_plug
Enter password:
Last Successful login time: Fri Aug 04 2017 12:09:00 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select compression, compress_for
2 from dba_tables
3 where owner = ‘HR’
4 and table_name = ‘COUNTRIES’
5 order by table_name;

COMPRESS COMPRESS_FOR


DISABLED BASIC

SQL>

​Nice.

​After 14 years, Oracle can still surprise me in core parts of it architecture.
:slight_smile:

Damir (sent from OnePlus 3T)

On Aug 4, 2017 19:15, “John Dorlon” bounce-jdorlon@toadworld.com wrote:

RE: org. index table in table list grid showed as compressed

Reply by John Dorlon
I agree that COMPRESS_FOR should be null when COMPRESSION=‘DISABLED’, but we are just showing what is in the data dictionary. I would say this is Oracle’s bug.

C:\Users\Admin>sqlplus

SQL*Plus: Release 12.2.0.1.0 on Fri Aug 4 12:12:58 2017

Copyright © 1982, 2015, Oracle. All rights reserved.

Enter user-name: jdorlon@azure_12c_plug
Enter password:
Last Successful login time: Fri Aug 04 2017 12:09:00 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select compression, compress_for
2 from dba_tables
3 where owner = ‘HR’
4 and table_name = ‘COUNTRIES’
5 order by table_name;

COMPRESS

​​

COMPRESS_FOR


DISABLED
​​

BASIC

SQL>

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag this post as spam/abuse.