 |
|
Oracle Tips by Burleson |
Exercise 3B: Create a
Locally-Managed Tablespace
As we know from Kevin Loneys book, Oracle
introduced Locally Managed Files to reduce fragmentation and to move
the data dictionary information out into the tablespace itself. We
also see a new Oracle 10g feature called automatic space management
that provides bitmap freelists for every segment in the tablespace.
In this exercise we will verify that
locally-managed tablespace have low fragmentation. We will perform
the following steps:
1 Create a locally-managed tablespace with
automatic extent management
2 Create a large table inside the new
tablespace
3 Run the space_mapper.sql script and look
at the extent usage within the tablespace.
Step1 Create the tablespace
The first step is to allocate the tablespace.
Please run this script on your Oracle database
create
tablespace
test4
datafile
'c:\oracle\oradata\diogenes\test4.dbf'
size
1m
extent management local
segment space management auto;
alter
database
datafile
'c:\oracle\oradata\diogenes\test4.dbf'
autoextend on;
Step 2 Populate the tablespace with a
large table
In this step we create a table by selecting
over 30,000 object names from the Oracle data dictionary. We also
deliberately allocate the table with a small initial and next extent
size. It is our plan to make this table extend into the
locally-managed tablespace.
create table
test_table
tablespace test4
storage (initial 10k next 10k)
as
select object_name from dba_objects
;
Here is a handy script that you can use to
map all of the objects in your tablespace. It accepts the name of
your tablespace as an argument and gives a complete extent map,
showing all free space inside the tablespace.
If we query the dba_segments view, we should
see that our test_table is over two megabytes and has extended within
the tablespace. Run the query below on your Oracle database.
select
bytes,
extents
from
dba_segments
where
segment_name = 'TEST_TABLE';
You should see something like this:
BYTES
EXTENTS
------------ ----------
2,097,152 17
Step 3 Run the space_mapper.sql script and examine extents
Now, we will use the following SQL script to
display fragments within the tablespace.
space_mapper.sql accepts the tablespace name as a parameter
set pagesize
60 linesize 132 verify off
break on
file_id skip 1
column file_id heading "File|Id" format 999
column tablespace_name for a15
column object for a15
column owner for a15
column bytes for 999,999,999
select
tablespace_name,
'free space' owner, -- "owner" of free space
' ' object, -- blank object name
file_id, -- file id for the extent header
block_id,
-- block id for the extent header
bytes -- length of the extent
from
dba_free_space
where
upper(tablespace_name) like upper('%&1%')
union
select
tablespace_name,
substr(owner, 1, 20), -- owner name (first 20 chars)
substr(segment_name, 1,
32), -- segment name
file_id, -- file id for extent header
block_id, -- block id for extent header
bytes -- length of the extent
from
dba_extents
where
upper(tablespace_name)
like upper('%&1%')
order by
1, 4, 5
;
To begin, try running this script against the
tablespace that contains your sample database. You have see chunks of
fragmented free space inside the tablespace:
TABLESPACE_NAME OWNER OBJECT Id BLOCK_ID BYTES
--------------- --------------- --------------- ---- ----------
---------
USERS PUBS AUTHOR 8 17 65,536
USERS PUBS EMP 33 65,536
USERS PUBS JOB 49 65,536
USERS PUBS PUBLISHER 65 65,536
USERS PUBS SALES 81 65,536
USERS PUBS STORE 97 65,536
USERS PUBS BOOK_AUTHOR 113 65,536
USERS PUBS BOOK 129 65,536
USERS PUBS PLAN_TABLE 145 65,536
USERS PUBS BOOK_TYPE_SALES 161 65,536
USERS PUBS PREVIOUS_EVENTS 177 65,536
USERS PUBS T1 193 65,536
USERS free space 209 65,536
USERS PUBS NEW_TITLE 225 65,536
USERS PUBS TITLE_CAPS 241 65,536
USERS PUBS PUB_SALARIES 257 65,536
USERS free
space 273 25,100,288
In the output above, we see a free space
fragment, starting at block 209 and continuing through block 224.
Your task is to start by running the script
to:
1 - Create the locally managed tablespace
2 Create the fragmented test_table
Then, run the space_mapper.sql script against
your test4 tablespace and verify that there are no fragments within
the tablespace. You should see one line of output for each extent of
the table.
Is there a difference between your NEXT extent
size (when you created the test_table) and the actual size of the
extents? Why did this happen?
ANSWER
File
TABLESPACE_NAME OWNER OBJECT Id
BLOCK_ID BYTES
--------------- --------------- --------------- ---- ----------
---------
TEST4 SYSTEM TEST_TABLE 15 17
65,536
TEST4 SYSTEM TEST_TABLE 33
65,536
TEST4 SYSTEM TEST_TABLE 49
65,536
TEST4 SYSTEM TEST_TABLE 65
65,536
TEST4 SYSTEM TEST_TABLE 81
65,536
TEST4 SYSTEM TEST_TABLE 97
65,536
TEST4 SYSTEM TEST_TABLE 113
65,536
TEST4 SYSTEM TEST_TABLE 129
65,536
TEST4 SYSTEM TEST_TABLE 145
65,536
TEST4 SYSTEM TEST_TABLE 161
65,536
TEST4 SYSTEM TEST_TABLE 177
65,536
TEST4 SYSTEM TEST_TABLE 193
65,536
TEST4 SYSTEM TEST_TABLE 209
65,536
TEST4 SYSTEM TEST_TABLE 225
65,536
TEST4 SYSTEM TEST_TABLE 241
65,536
TEST4 SYSTEM TEST_TABLE 257
65,536
TEST4 SYSTEM TEST_TABLE 273
1,048,576
The student will create a locally-managed tablespace in their
system and then compare the tablespace storage characteristics. Submit
findings to instructor by due date.
For more details,
see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology. |
