prompt ====================================================================
prompt  Creating a user for the demonstration and granting it privileges:
prompt ====================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> CREATE USER seg_demo identified by seg_demo;

create user seg_demo identified by seg_demo;


prompt SQL> GRANT RESOURCE , CONNECT TO seg_demo;

grant resource , connect to seg_demo;

prompt SQL> GRANT SELECT ON V_$PARAMETER TO seg_demo;

grant SELECT on v_$parameter to seg_demo;

prompt SQL> CONNECT seg_demo/seg_demo

conn seg_demo/seg_demo

Accept return

prompt ====================================================================
prompt  Displaying relevant parameters
prompt ====================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> SHOW PARAMETER def
show parameter def
prompt 
prompt 

prompt SQL> SHOW PARAMETER compatible
show parameter compatible

Accept return

prompt =========================================================================================
prompt  Creating a table. The table will be created without segments for all its related objects
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> CREATE TABLE test_seg (id number CONSTRAINT id_pk primary key,
prompt 2				email VARCHAR2(50)  CONSTRAINT  email_uk unique,
prompt 3				name VARCHAR2(30),
prompt 4				data CLOB)
prompt 5			lob(data) STORE AS TEST_SEG_DATA_LOB 
prompt 6			(index test_seg_data_lob_ix);

CREATE TABLE test_seg (id number CONSTRAINT id_pk primary key,
						email VARCHAR2(50)  CONSTRAINT  email_uk unique,
						name VARCHAR2(30),
						data CLOB)
    		lob(data) STORE AS TEST_SEG_DATA_LOB 
		(index test_seg_data_lob_ix);


Accept return


prompt =========================================================================================
prompt  Displaying relevant information FROM the System views.
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return

col segment_name for a20
col segment_created for a15

prompt SQL> SELECT segment_name ,SEGMENT_TYPE
prompt 2	FROM user_segments 
prompt 3 	WHERE segment_name = 'TEST_SEG';

SELECT segment_name ,SEGMENT_TYPE
FROM user_segments 
WHERE segment_name = 'TEST_SEG';

prompt 
prompt SQL> SELECT segment_name, extent_id, bytes 
prompt 2 	FROM user_extents;

SELECT segment_name, extent_id, bytes 
FROM user_extents ;

prompt 
prompt SQL> SELECT table_name, SEGMENT_CREATED 
prompt 2 	FROM user_tables;

SELECT table_name, SEGMENT_CREATED 
FROM user_tables ;

prompt 
prompt SQL> SELECT table_name, index_name, SEGMENT_CREATED 
prompt 2 	FROM user_indexes ;

SELECT table_name, index_name, SEGMENT_CREATED 
FROM user_indexes ;

prompt 
prompt SQL> SELECT  TABLE_NAME ,segment_name, SEGMENT_CREATED 
prompt 2 	FROM user_lobs ;


SELECT  TABLE_NAME ,segment_name, SEGMENT_CREATED 
FROM user_lobs ;

Accept return

prompt =========================================================================================
prompt Inserting the first row to the table. 
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> INSERT INTO TEST_SEG VALUES (1,'Roni@dbsnaps.com','DBSNAPS','Deferred Segment Creation');

insert into TEST_SEG values (1,'Roni@dbsnaps.com','DBSNAPS','Deferred Segment Creation');

prompt 
prompt SQL> commit;

commit;

Accept return


prompt =========================================================================================
prompt All the relevant segments will be created.
prompt Displaying relevant information FROM the System views.
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return


prompt SQL> SELECT segment_name ,SEGMENT_TYPE
prompt 2 FROM user_segments 

SELECT segment_name ,SEGMENT_TYPE
FROM user_segments ;

prompt 
prompt SQL> SELECT segment_name, extent_id, bytes 
prompt 2 	FROM user_extents ;

SELECT segment_name, extent_id, bytes 
FROM user_extents ;


prompt 
prompt SQL> SELECT table_name, SEGMENT_CREATED 
prompt 2 	FROM user_tables ;

SELECT table_name, SEGMENT_CREATED 
FROM user_tables ;


prompt 
prompt SQL> SELECT table_name, index_name, SEGMENT_CREATED 
prompt 2 	FROM user_indexes ;

SELECT table_name, index_name, SEGMENT_CREATED 
FROM user_indexes ;

prompt 
prompt SQL> SELECT  TABLE_NAME ,segment_name, SEGMENT_CREATED 
prompt 2 	FROM user_lobs ;

SELECT  TABLE_NAME ,segment_name, SEGMENT_CREATED 
FROM user_lobs ;

Accept return

prompt =========================================================================================
prompt The parameter can be changed in the System/Session level.
prompt Changing the session settings to deferred_segment_creation=FALSE.
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt ALTER SESSION SET deferred_segment_creation=FALSE; 
ALTER SESSION SET deferred_segment_creation=FALSE; 

Accept return



prompt =========================================================================================
prompt While this parameter is set to FALSE, tables will be created with all their segments.
prompt =========================================================================================
prompt prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> CREATE TABLE test_seg2 (id number CONSTRAINT id2_pk primary key,    		        
prompt 2    			name VARCHAR2(30));

prompt 
prompt 
CREATE TABLE test_seg2 (id number CONSTRAINT id2_pk primary key,    		        
    			name VARCHAR2(30));
Accept return

prompt =========================================================================================
prompt Displaying relevant information FROM the System views.
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> SELECT segment_name ,SEGMENT_TYPE
prompt 2 	FROM user_segments 
prompt 3 	WHERE segment_name = 'TEST_SEG2';

SELECT segment_name ,SEGMENT_TYPE
FROM user_segments 
WHERE segment_name = 'TEST_SEG2';

prompt 
prompt SQL> SELECT segment_name, extent_id, bytes 
prompt 2 	FROM user_extents 
prompt 3 	WHERE segment_name = 'TEST_SEG2';

SELECT segment_name, extent_id, bytes 
FROM user_extents 
WHERE segment_name = 'TEST_SEG2';

prompt 
prompt SQL> SELECT table_name, SEGMENT_CREATED 
prompt 2 	FROM user_tables 
prompt 3 	WHERE table_name = 'TEST_SEG2';

SELECT table_name, SEGMENT_CREATED 
FROM user_tables 
WHERE table_name = 'TEST_SEG2';


prompt 
prompt SQL> SELECT table_name, index_name, SEGMENT_CREATED 
prompt 2 	FROM user_indexes 
prompt 3 	WHERE table_name = 'TEST_SEG2';

SELECT table_name, index_name, SEGMENT_CREATED 
FROM user_indexes 
WHERE table_name = 'TEST_SEG2';

Accept return


prompt =========================================================================================
prompt In addition, the feature can be enabled/Disabled for a Single table.
prompt While the parameter is set to TRUE, we will disable the deferred segment creation for a single table .
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return
prompt

prompt ALTER SESSION SET deferred_segment_creation=TRUE; 

ALTER SESSION SET deferred_segment_creation=TRUE; 

prompt 
prompt SQL> CREATE TABLE test_seg3 (id number CONSTRAINT id3_pk primary key,    		        
prompt 2	    			name VARCHAR2(30))
prompt 3				SEGMENT CREATION IMMEDIATE;

CREATE TABLE test_seg3 (id number CONSTRAINT id3_pk primary key,    		        
    			name VARCHAR2(30))
	SEGMENT CREATION IMMEDIATE;
	
Accept return	

prompt =========================================================================================
prompt Displaying relevant information FROM the System views.
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> SELECT segment_name ,SEGMENT_TYPE
prompt 2 	FROM user_segments 
prompt 3 	WHERE segment_name = 'TEST_SEG3';

SELECT segment_name ,SEGMENT_TYPE
FROM user_segments 
WHERE segment_name = 'TEST_SEG3';

prompt 
prompt SQL> SELECT segment_name, extent_id, bytes 
prompt 2 	FROM user_extents 
prompt 3	WHERE segment_name = 'TEST_SEG3';

SELECT segment_name, extent_id, bytes 
FROM user_extents 
WHERE segment_name = 'TEST_SEG3';


prompt 
prompt SQL> SELECT table_name, SEGMENT_CREATED 
prompt 2 	FROM user_tables 
prompt 3 	WHERE table_name = 'TEST_SEG3';

SELECT table_name, SEGMENT_CREATED 
FROM user_tables 
WHERE table_name = 'TEST_SEG3';


prompt 
prompt SQL> SELECT table_name, index_name, SEGMENT_CREATED 
prompt 2 	FROM user_indexes 
prompt 3 	WHERE table_name = 'TEST_SEG3';

SELECT table_name, index_name, SEGMENT_CREATED 
FROM user_indexes 
WHERE table_name = 'TEST_SEG3';


Accept return



prompt =========================================================================================
prompt While the parameter is set to FALSE, we will Enable the deferred segment creation for a single table .
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> ALTER SESSION SET deferred_segment_creation=FALSE; 

ALTER SESSION SET deferred_segment_creation=FALSE; 

prompt 
prompt SQL> CREATE TABLE test_seg4 (id number CONSTRAINT id4_pk primary key,    		        
prompt   2         			name VARCHAR2(30))
prompt 	 3  	SEGMENT CREATION DEFERRED;

CREATE TABLE test_seg4 (id number CONSTRAINT id4_pk primary key,    		        
    			name VARCHAR2(30))
	SEGMENT CREATION DEFERRED;

Accept return

prompt =========================================================================================
prompt Displaying relevant information FROM the System views.
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> SELECT segment_name ,SEGMENT_TYPE
prompt 2	FROM user_segments 
prompt 3 	WHERE segment_name = 'TEST_SEG4';

SELECT segment_name ,SEGMENT_TYPE
FROM user_segments 
WHERE segment_name = 'TEST_SEG4';


prompt  
prompt SQL> SELECT segment_name, extent_id, bytes 
prompt 2 	FROM user_extents 
prompt 3 	WHERE segment_name = 'TEST_SEG4';

SELECT segment_name, extent_id, bytes 
FROM user_extents 
WHERE segment_name = 'TEST_SEG4';


prompt 
prompt SQL> SELECT table_name, SEGMENT_CREATED 
prompt 2 FROM user_tables 
prompt 3 WHERE table_name = 'TEST_SEG4';

SELECT table_name, SEGMENT_CREATED 
FROM user_tables 
WHERE table_name = 'TEST_SEG4';

prompt 
prompt SQL> SELECT table_name, index_name, SEGMENT_CREATED 
prompt 2 	FROM user_indexes 
prompt 3 	WHERE table_name = 'TEST_SEG4';

SELECT table_name, index_name, SEGMENT_CREATED 
FROM user_indexes 
WHERE table_name = 'TEST_SEG4';

Accept return


prompt =========================================================================================
prompt The END. Dropping the demonstration user
prompt =========================================================================================
prompt ********** Hit 'return' to continue **********
Accept return

prompt SQL> CONNECT / as sysdba
conn / as sysdba
prompt
prompt  DROP USER seg_demo CASCADE;
drop user seg_demo cascade;