Hands-On 13 (Managing Storage Structures)

As a DBA, you are responsible to manage a table storage structure due to performance problems or a spacing issue. If you have a table that has lots of update transactions you would want to be sure that you have enough space in the PCTFREE space. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Oracle Segments

Extent Allocations

PCTFREE

PCTUSED

Using the DBA_SEGMENTS view

INDEX segment

TABLE segment

Creating an INDEX_ORGANIZED table

Using the ORGANIZATION INDEX parameter

Using the TABLESPACE option

Using the PCTTHRESHOLD parameter

Using the OVERFLOW TABLESPACE parameter

Understanding Different Segment Types

Using the DBA_SEGMENTS view

Using the DBA_TABLES view

Using the DBA_EXTENTS view

Using the MAXEXTENTS option

Space allocation in the Oracle Block unit

Setting the PCTFREE parameter

Setting the PCUSED parameter

Commands:

DROP TABLE my_

CREATE TABLE STORAGE

ANALYZE TABLE COMPUTE STATISTICS

ALTER TABLE STORAGE

-- Hands-On 13 (Managing Storage Structures)
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
SET SQLPROMPT 'SQL> '
COL name FORMAT a50
col segment_name format a40
col file_name format a50
col extent_management format a20
col username format a10
col member format a50

pause

--Start

CLEAR SCR
-- In this exercise you will learn how Oracle segments, 
-- extents allocations, pctfree and pctused setting 
-- works, and more.

-- Now, connect to SQL*Plus as the system/manager user.

pause

CONNECT system/manager AS SYSDBA

pause

CLEAR SCR
-- Query the number of different segment types you have
-- in the database.

pause


SELECT segment_type, count(segment_type)
FROM dba_segments
GROUP BY segment_type
/

-- Notice that you may have more or less than this list. 
-- It all depends on the segments that were created in 
-- the database.

-- Take notes about the counts on the INDEX and TABLE segments.

pause

CLEAR SCR
-- Let's create an Index-Organized Table. 

pause

CREATE TABLE my_iot (
partno NUMBER, 
name VARCHAR2(20),
CONSTRAINT pk_my_iot PRIMARY KEY (partno))
ORGANIZATION INDEX
TABLESPACE users
PCTTHRESHOLD 20
OVERFLOW TABLESPACE users
/

pause 

CLEAR SCR
-- Query to see how many different segment types you have 
-- in the database this time.

pause


SELECT segment_type, count(segment_type)
FROM dba_segments
GROUP BY segment_type
/

-- Notice that the count in the INDEX and TABLE segments 
-- have been incremented by one. The reason for that is when 
-- you created the IOT table, it creates the INDEX and TABLE segments.

-- Remember that although the SYSTEM tablespace can store any 
-- database objects, it is not recommended that you put objects in 
-- it other than the dictionary objects and the system UNDO segment.

pause

CLEAR SCR
-- Now, drop the table.

pause


DROP TABLE my_iot
/

pause

CLEAR SCR

-- Let's see how the extent allocation works in tablespace.

-- Create a table with all of the following options.
-- The initial size of 50k, next extent size of 50k,
-- a percent increase of zero, a minimum extent of 1, and
-- a maximum extent of 5.

pause


CREATE TABLE my_default_table
(col1 CHAR(2000))
STORAGE (INITIAL 50k 
NEXT 50k 
PCTINCREASE 0
MINEXTENTS 1 
MAXEXTENTS 5)
/
pause

CLEAR SCR
-- Now, let's check or view what we have in the Oracle Dictionary.
-- Query the DBA_TABLES view and display statistical information
-- about the table. Be sure to first analyze the table with the
-- compute statistics option.

pause

ANALYZE TABLE my_default_table COMPUTE STATISTICS
/

SELECT table_name, initial_extent, next_extent, max_extents, num_rows
FROM dba_tables
WHERE table_name = 'MY_DEFAULT_TABLE'
/ 

-- The number you see is the default number for an initial and
-- next extention.

-- Take notes about it.

pause

CLEAR SCR
-- Query the distribution of the segments.

pause


SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
/

-- Take notes about that.

pause

CLEAR SCR
-- Lets now insert 20 records into the table.

pause


BEGIN
FOR i IN 1..20 LOOP
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
END LOOP;
COMMIT;
END;
/

pause

CLEAR SCR
-- Query the distribution of segments.

pause


SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/

-- Notice that an extent was added to the list.

pause

CLEAR SCR
-- Alter your table with a 50 percent extension increase.

pause


ALTER TABLE my_default_table 
STORAGE (PCTINCREASE 50)
/

pause

CLEAR SCR
-- Lets insert 50 more records into the table.

pause


BEGIN
FOR i IN 1..50 LOOP
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
END LOOP;
COMMIT;
END;
/

pause

CLEAR SCR
-- Query the distribution of the segments again.

pause


SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/

-- Notice that 3 more extents were added to the list and each
-- new extent was incremented by 50 percent.

pause

CLEAR SCR
-- This time let's insert 50 more records into the table, keeping
-- in mind, we are not allowed to allocate more than 
-- 5 extents, because the MAXEXTENTS option was set to 5.

pause 


BEGIN
FOR i IN 1..50 LOOP
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
END LOOP;
COMMIT;
END;
/

pause


CLEAR SCR
-- Query the distribution of the segments again.

pause


SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/

-- Remember that the table can grow up to 5 extents.
-- You can alter the table to expand the extents.

pause


CLEAR SCR
-- Now, let's exercise how to set the pctfree and the pctused 
-- space allocation in the Oracle block unit.

-- Drop MY_DEFAULT_TABLE and recreate it with an initial 
-- extent size of 100k, a next extent size of 200K, and a percent
-- increase of 0 in the USERS tablespace.


pause


DROP TABLE my_default_table
/

CREATE TABLE my_default_table
(col1 CHAR(1000))
STORAGE (INITIAL 100k 
NEXT 200k 
PCTINCREASE 0)
TABLESPACE users
/

pause

CLEAR SCR
-- Once again, query the distribution of segments.

pause


SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/

pause

CLEAR SCR
-- Let's now insert 100 records into the table.

pause


BEGIN
FOR i IN 1..100 LOOP
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
END LOOP;
COMMIT;
END;
/

pause

CLEAR SCR
-- Once again, query the distribution of the segments.

pause


SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/

-- Take notes on the number of created extents.

pause

CLEAR SCR
-- Check the block space allocation in MY_DEFAULT_TABLE.

pause


SELECT pct_free, pct_used 
FROM user_tables
WHERE table_name = 'MY_DEFAULT_TABLE'
/

-- Notice that the default percent free is set to 10 and
-- the percent used is set to 40.

pause

CLEAR SCR
-- Now, change the percent free to 50 percent.
-- Then, truncate the table and check the segments allocation
-- in the table.

pause

ALTER TABLE my_default_table
PCTFREE 50
/

TRUNCATE TABLE my_default_table
/

SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/

pause

CLEAR SCR
-- Let's insert another 100 records into the table again.

pause


BEGIN
FOR i IN 1..100 LOOP
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
END LOOP;
COMMIT;
END;
/

pause

CLEAR SCR
-- Then, query the distribution of segments one more time and drop
-- the table after that.

pause


SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/

-- Write down the number of created extents.
-- Do you see the difference?
-- This is what is going to happen if you don't properly assign
-- your block size with the right pctfree and pctused allocation.

-- Remember that if you are only inserting records with no 
-- changes, the pctfree allocation should be as low as 5.
-- If you have a lot of updates you may want to have the pctfree
-- allocation set to 20 percent or more.

DROP TABLE my_default_table
/

pause

CLEAR SCR
-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck.
--
pause
pause