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?
A.
T1, an index segment for the primary key, a LOB segment, and a lobindex segment
B.
no segments are created
C.
T1 only
D.
T1 and an index segment created for the primary key only
E.
T1, an index segment for the primary key, and a LOB segment only
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.
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
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
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.
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.
upvoted 5 times
...
...
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
Rivaldo11
Highly Voted 4 years agoEkos
3 years, 7 months agoama
3 years, 12 months agoChansi
Highly Voted 3 years, 11 months agokuvinod29
Most Recent 3 months, 3 weeks agoPKSOA
10 months, 1 week agoKuraudio
1 year, 7 months ago[Removed]
2 years, 9 months agoViki5099
3 years, 7 months agoMandar79
3 years, 10 months agokhalilshahin01
3 years, 12 months agokhalilshahin01
3 years, 12 months agoama
3 years, 11 months agoSimoneF
3 years, 6 months agoyou1234
3 years, 12 months agoMuhab
3 years, 12 months ago