iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 14

"I have never let my schooling interfere with my education." - Mark Twain (1835-1910)

 

Read first then play the video:

   DBA-VIDEO -Managing Storage Structures

   

Managing Storage Structures

 

Introduction

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 PCT space. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

 

Oracle Segments

Extent Allocations

PCT

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 PCT parameter

Setting the PCUSED parameter

Commands:

DROP TABLE my_

CREATE TABLE STORAGE

ANALYZE TABLE COMPUTE STATISTICS

ALTER TABLE STORAGE

 

Hands-on

In this exercise you will learn how Oracle segments, extents allocations, pct and pctused setting works, and more. Note that PCT means that an Oracle user can add records to a block until the unused space block reaches to the PCT value. When a block uses all space up to the “1-PCT” percentage, it stops adding records to the block. Oracle takes that block out of the list. 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 list 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.


PCT and PCTUSED space allocation
Now, let's exercise how to set the pct 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_, pct_used
              FROM user_tables
              WHERE table_name = 'MY_DEFAULT_TABLE'
/
Notice that the default percent is set to 10 and the percent used is set to 40.

Now, change the percent to 50 percent. Then, truncate the table and check the segments allocation in the table.
SQL> ALTER TABLE my_default_table
              PCT 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 pct and pctused allocation. Remember that if you are only inserting records with no changes, the pct allocation should be as low as 5. If you have a lot of updates you may want to have the pct allocation set to 20 percent or more.

Drop the table.

SQL> DROP TABLE my_default_table
/

 

"The longer I live the more I see that I am never wrong about anything, and that all the pains that I have so humbly taken to verify my notions have only wasted my time." - George Bernard Shaw (1856-1950)

 

Questions:

Q: Describe a Segment in the Oracle database?

Q: Describe the DBA_SEGMENTS view?

Q: What are the PCT 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> /