DBA Fundamentals

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:

In this exercise you will learn how Oracle segments, extents allocations, pctfree and pctused setting works, and more. Note that PCTFREE means that an Oracle user can add records to a block until the unused space block reaches to the PCTFREE value. When a block uses all space up to the �1-PCTFREE� percentage, it stops adding records to the block. Oracle takes that block out of the Freelist. It means that records can not be added to the block any more unless you delete records from the block till it reaches to the PCTUSED value. Then Oracle will add the block in the Freelist again and records can be added to the block. And this process continues to determine when to add records in or stop adding records from the block.

Now, connect to SQL*Plus as the system/manager user.
SQL> CONNECT system/manager AS SYSDBA
 

View Segment types
Query the number of different segment types you have in the database.
SQL> 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.
 

Create an object
Let's create an Index-Organized Table.
SQL> 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
/

Query to see how many different segment types you have in the database this time.
SQL> 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.

Now, drop the table.
SQL> DROP TABLE my_iot
/
 

Extent Allocation in tablespace
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.
SQL> CREATE TABLE my_default_table
(col1 CHAR(2000))
STORAGE (INITIAL 50k
NEXT 50k
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 5)
/

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.
SQL> ANALYZE TABLE my_default_table COMPUTE STATISTICS
/
SQL> SELECT table_name, initial_extent, next_extent,

max_extents, num_rows
FROM dba_tables
WHERE table_name = 'MY_DEFAULT_TABLE'
/
Note that the number you see is the default number for an initial and next extension. Take notes about it.

Query the distribution of the segments.
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
/
Take notes about that.

Lets now insert 20 records into the table.
SQL> BEGIN
SQL> FOR i IN 1..20 LOOP
SQL> INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /

Query the distribution of segments.
SQL> 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.

Alter your table with a 50 percent extension increase.
SQL> ALTER TABLE my_default_table
STORAGE (PCTINCREASE 50)
/

Lets insert 50 more records into the table.
SQL> BEGIN
SQL> FOR i IN 1..50 LOOP
SQL> INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /

Query the distribution of the segments again.
SQL> 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.

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.
SQL> BEGIN
SQL> FOR i IN 1..50 LOOP
SQL> INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /

Query the distribution of the segments again.
SQL> 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.
 

PCTFREE and PCTUSED space allocation
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.
SQL> DROP TABLE my_default_table
/
SQL> CREATE TABLE my_default_table
(col1 CHAR(1000))
STORAGE (INITIAL 100k
NEXT 200k
PCTINCREASE 0)
TABLESPACE users
/

Once again, query the distribution of segments.
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/

Let's now insert 100 records into the table.
SQL> BEGIN
SQL> FOR i IN 1..100 LOOP
SQL> INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /

Once again, query the distribution of the segments.
SQL> 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.

Check the block space allocation in MY_DEFAULT_TABLE.
SQL> 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.

Now, change the percent free to 50 percent. Then, truncate the table and check the segments allocation in the table.
SQL> ALTER TABLE my_default_table
PCTFREE 50
/
SQL> TRUNCATE TABLE my_default_table
/
SQL> -- Check the segments allocation�
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/
 

Let's insert another 100 records into the table again.
SQL> BEGIN
SQL> FOR i IN 1..100 LOOP
SQL> INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /

Then, query the distribution of segments one more time and drop the table after that.
SQL> 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 the table.

SQL> DROP TABLE my_default_table
/
 

Questions:

Q: Describe a Segment in the Oracle database?

Q: Describe the DBA_SEGMENTS view?

Q: What are the PCTFREE and PCTUSED space allocations in the CREATE TABLE statement?

Q: How many types of segment do you have in the Oracle database?

Q: How do you create an INDEX_ORGANIZED table?

Q: Describe the ORGANIZATION INDEX parameter.

Q: Describe the PCTTHRESHOLD and OVERFLOW TABLESPACE parameters.

Q: Describe the DBA_EXTENTS view.

Q: What do the following SQL statements do?

SQL> SELECT segment_type, count(segment_type)
FROM dba_segments
GROUP BY segment_type
/
SQL> 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
/
SQL> CREATE TABLE my_default_table
(col1 CHAR(2000))
STORAGE (INITIAL 50k
NEXT 50k
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 5)
/
Q: How do you query the distribution of the segments in the database?

Q: How do you the following PL/SQL statement?

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