- Controlling DEFERRED_SEGMENT_CREATION
show user;
show con_name;
/*
USER is "PDB10_ADMIN"
CON_NAME
------------------------------
PDB10
*/
show parameter DEFERRED_SEGMENT_CREATION
/*
NAME TYPE VALUE
------------------------- ------- -----
deferred_segment_creation boolean TRUE
*/
# 'True' means the segment will be created for the table
# 1 after you crate the table using DDL statement ( create table )
# 2 after you insert one record ( DML )
create table country (id number, name varchar2(100) );
/*
Table COUNTRY created.
*/
SELECT segment_name, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name= upper('country');
/*
no rows selected
*/
insert into country values (1,'Jordan');
/*
1 row inserted.
*/
SELECT segment_name, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name= upper('country');
/*
SEGMENT_NAME BYTES BLOCKS EXTENTS TABLESPACE_NAME
--------------- ---------- ---------- ---------- ------------------------------
COUNTRY 65536 8 1 USERS
*/
# now let change DEFERRED_SEGMENT_CREATION to be false
# you can do this by alter session / alter system
# this will create the segment immidiate
alter session set DEFERRED_SEGMENT_CREATION=false;
/*
Session altered.
*/
show parameter DEFERRED_SEGMENT_CREATION
/*
NAME TYPE VALUE
------------------------- ------- -----
deferred_segment_creation boolean FALSE
*/
create table jobs (id number, title varchar2(100) );
/*
Table JOBS created.
*/
SELECT segment_name, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name= upper('jobs');
/*
SEGMENT_NAME BYTES BLOCKS EXTENTS TABLESPACE_NAME
--------------- ---------- ---------- ---------- ------------------------------
JOBS 65536 8 1 USERS
*/
# note
# Deferred segment is the default for tables, indexes, and partitions
# let do this now
alter session set DEFERRED_SEGMENT_CREATION=true;
/*
Session altered.
*/
# but we can do this also
# "SEGMENT CREATION IMMEDIATE;" 옵션을 사용하면 segments 가 바로 생성된다.
create table test ( n number )
SEGMENT CREATION IMMEDIATE;
/*
Table TEST created.
*/
SELECT segment_name, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name= upper('test');
/*
SEGMENT_NAME BYTES BLOCKS EXTENTS TABLESPACE_NAME
--------------- ---------- ---------- ---------- ------------------------------
TEST 65536 8 1 USERS
*/
create table test10 ( n number )
SEGMENT CREATION DEFERRED;
/*
Table TEST10 created.
*/
SELECT segment_name, bytes, blocks, extents, tablespace_name FROM USER_SEGMENTS
where segment_name= upper('test10');
/*
no rows selected
*/