In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE.
Examine this command:
SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);
Which segment or segments, if any, are created as a result of executing the command?
In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE.
Examine this command:
SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);
Which segment or segments, if any, are created as a result of executing the command?
When DEFERRED_SEGMENT_CREATION is set to TRUE, any segments for tables, indexes, or LOBs are not created until the first row is inserted into the table. Therefore, executing the command CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB) will not create any segments immediately upon table creation. No segments will be created for T1, the index segment for the primary key, or the LOB segment until data is inserted into the table.
Answer is B.
I agree DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table. Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time.
i agree
SQL> alter session set deferred_segment_creation=TRUE; Session altered. SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB); Table created. SQL> select segment_name,segment_type,bytes from user_segments; no rows selected Answer is B
100% B SQL> conn c##test/test Connected. SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB); Table created. OBJECT_NAME OBJECT_TYPE ---------------------------------------- ----------------------- T1 TABLE SYS_C007557 INDEX SYS_IL0000073470C00002$$ INDEX SYS_LOB0000073470C00002$$ LOB SQL> select SEGMENT_NAME,SEGMENT_TYPE from user_segments; no rows selected SQL> desc T1 Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NOT NULL NUMBER(38) C2 CLOB
DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table. https://oracle-base.com/articles/11g/segment-creation-on-demand-11gr2
SQL> alter session set DEFERRED_SEGMENT_CREATION=true; SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB); SQL> select * from user_segments where segment_name = upper('T1'); no row selected Answer is B.
sorry Answer is A. You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes, and LOBs, this column shows YES if the segment is created.
DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.
Exactly, khalilshahin01 you got it right the first time: as you saw from the user_segments, if nothing is there then no segment has been created.
B is correct answer https://oracle-base.com/articles/11g/segment-creation-on-demand-11gr2
B is correct
B (until rows are inserted in non- partitioned table, it doesn’t create any segments) when DEFERRED_SEGMENT_CREATION is true
ALTER SESSION SET DEFERRED_SEGMENT_CREATION=TRUE SHOW PARAMETERS DEF CREATE TABLE TEMP_DEF_SEQ_CHK (C1 INT PRIMARY KEY, LOB1 CLOB) SELECT * FROM USER_SEGMENTS; no rows selected SELECT SEGMENT_CREATED FROM USER_INDEXES; SEG --- NO NO
12c. SQL> show parameter def NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB); Table created. SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where table_name='T1'; CONSTRAINT_NAME C TABLE_NAME ---------------------- - --------------------------------- SYS_C0081022 P T1 SQL> select COLUMN_NAME,SEGMENT_NAME,INDEX_NAME from user_lobs where table_name='T1'; COLUMN_NAME SEGMENT_NAME INDEX_NAME ---------------------- -------------------------------------------- --------------------------------- C2 SYS_LOB0000686210C00002$$ SYS_IL0000686210C00002$$ SQL> not done any DML operations. A is correct.
no B is correct. SQL> select COLUMN_NAME,SEGMENT_NAME,INDEX_NAME,SEGMENT_CREATED from user_lobs where table_name='T1'; COLUMN_NAME SEGMENT_NAME INDEX_NAME SEGMENT_CREATED ---------------------- -------------------------------------------- --------------------------------- --------------------------------- C2 SYS_LOB0000686210C00002$$ SYS_IL0000686210C00002$$ NO see SEGMENT_CREATED is no that mean no segment created.