Last week I got an email from Oracle Certification Team that I’ve successfully passed OCM exam. I can proudly say that I’m Oracle Certified Master now!

Although I’m working as a production DBA, I prepared for this exam for more than 6 months. I made a plan for each month, even for each week, prepared own scenarios and practiced for hours.

The exam was tough, you need to be prepared for any case, you have to make decisions very quickly, and even you have to type very fast.

I will prepare blog post on how I prepared for OCM exam and hope it will help and inspire you to take it

 

From the following link you can download my Step by Step Oracle 10g RAC installation on VMware ebook

Downloaded 3941 times

While importing a dump file, sometimes it takes too long and seems to be “hanging” and processing something unknown in the background, or queuing for something. As a DBA, you HAVE NOT wait “until it finishes”. You have to try to find out the solution.

Yesterday, while importing a metadata of a big database, the session hold and took hours to finish.

This is the command I run to import the dump file into the new database:

impdp system/oracle directory=mydir dumpfile=dump01.dmp exclude=tablespace, statistics parfile=param.ini METRICS=y 

The session was hold in the following section:

Processing object type DATABASE_EXPORT/SCHEMA/CLUSTER/CLUSTER

     Completed 1 CLUSTER objects in 0 seconds

Processing object type DATABASE_EXPORT/SCHEMA/CLUSTER/INDEX

     Completed 1 INDEX objects in 0 seconds

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Note: I suggest that we use undocumented METRICS=Y parameter to get information about the number of objects and the time it takes to process them into the log file

I waited shortly and was curious about the activities in the background, so decided to debug the Data Pump session and run the following query to get the SID and SERIAL number of the running data pump sessions. These values will be used to trace the session in the next step:

col username format a10
set linesize 150
col job_name format a20
col program format a25
SELECT TO_CHAR (SYSDATE, ‘YYYY-MM-DD HH24:MI:SS’) “DATE”,
     s.program,
     s.sid,
     s.status,
     s.username,
     d.job_name,
     p.spid,
     s.serial#,
     p.pid
FROM V$SESSION s, V$PROCESS p, DBA_DATAPUMP_SESSIONS d
WHERE p.addr = s.paddr AND s.saddr = d.saddr;

DATE                PROGRAM                          SID STATUS   USERNAME   JOB_NAME             SPID            SERIAL#        PID

——————- ————————- ———- ——– ———- ——————– ———— ———- ———-

2013-03-07 14:52:04 udi@TestDB02 (TNS V1-V3)         152 ACTIVE   SYSTEM     SYS_IMPORT_FULL_01   12143                36         17

2013-03-07 14:52:04 oracle@TestDB02 (DM00)           148 ACTIVE   SYSTEM     SYS_IMPORT_FULL_01   12147                 3         19

2013-03-07 14:52:04 oracle@TestDB02 (DW01)           142 ACTIVE   SYSTEM     SYS_IMPORT_FULL_01   12149                15         20

 

 

 

 

Then I queried V$SESSION_WAIT view to get the waiting event:

SELECT   w.sid, w.event, w.seconds_in_wait

  FROM   V$SESSION s, DBA_DATAPUMP_SESSIONS d, V$SESSION_WAIT w

 WHERE   s.saddr = d.saddr AND s.sid = w.sid;

     SID EVENT                                                            SECONDS_IN_WAIT

———- —————————————————————- —————

       142 control file sequential read                                                                  1

       148 wait for unread message on broadcast channel                                  43

       152 wait for unread message on broadcast channel                                 306

If you run the same query a number of times, you’ll see how the wait event changes:

To get detailed information I decided to debug the first session (SID = 142) and provided values of SID and SERIAL# columns to DBMS_SYSTEM.SET_EV procedure and started the trace:

SQL>  EXECUTE SYS.DBMS_SYSTEM.SET_EV (142,15,10046,8,”);

Then I switched to udump folder and checked the file labeled “dbname_ora_PID” – “testdb_ora_12149”

-bash-4.2$ tail -f testdb_ora_12149.trc

WAIT #4: nam=’Data file init write’ ela= 3 count=4294967295 intr=32 timeout=2147483647 obj#=51780 tim=13410366772411

WAIT #4: nam=’control file sequential read’ ela= 21 file#=0 block#=1 blocks=1 obj#=51780 tim=13410366772623

WAIT #4: nam=’control file sequential read’ ela= 15 file#=1 block#=1 blocks=1 obj#=51780 tim=13410366772660

WAIT #4: nam=’control file sequential read’ ela= 15 file#=2 block#=1 blocks=1 obj#=51780 tim=13410366772695

WAIT #4: nam=’control file sequential read’ ela= 13 file#=0 block#=16 blocks=1 obj#=51780 tim=13410366772728

WAIT #4: nam=’control file sequential read’ ela= 13 file#=0 block#=18 blocks=1 obj#=51780 tim=13410366772760

WAIT #4: nam=’control file sequential read’ ela= 13 file#=0 block#=24 blocks=1 obj#=51780 tim=13410366772802

WAIT #4: nam=’db file sequential read’ ela= 12 file#=4 block#=1 blocks=1 obj#=51780 tim=13410366772834

WAIT #4: nam=’db file single write’ ela= 11313 file#=4 block#=1 blocks=1 obj#=51780 tim=13410366784170

WAIT #4: nam=’control file parallel write’ ela= 38910 files=3 block#=17 requests=3 obj#=51780 tim=13410366823104

WAIT #4: nam=’control file parallel write’ ela= 46532 files=3 block#=15 requests=3 obj#=51780 tim=13410366869661

WAIT #4: nam=’control file parallel write’ ela= 40258 files=3 block#=1 requests=3 obj#=51780 tim=13410366909955

WAIT #4: nam=’control file sequential read’ ela= 12 file#=0 block#=1 blocks=1 obj#=51780 tim=13410366909986

WAIT #4: nam=’rdbms ipc reply’ ela= 7016 from_process=5 timeout=21474836 p3=0 obj#=51780 tim=13410366917049

=====================

PARSING IN CURSOR #15 len=296 dep=3 uid=0 oct=6 lid=0 tim=13410366917452 hv=2379717279 ad=’de799c58′

update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3

END OF STMT

PARSE #15:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=3,tim=13410366917447

EXEC #15:c=0,e=337,p=0,cr=5,cu=1,mis=0,r=1,dep=3,og=3,tim=13410366918000

STAT #15 id=1 cnt=0 pid=0 pos=1 obj=0 op=’UPDATE  SEG$ (cr=5 pr=0 pw=0 time=277 us)’

STAT #15 id=2 cnt=1 pid=1 pos=1 obj=14 op=’TABLE ACCESS CLUSTER SEG$ (cr=5 pr=0 pw=0 time=166 us)’

STAT #15 id=3 cnt=1 pid=2 pos=1 obj=9 op=’INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=31 us)’

By using tkprof I formatted content of the trace file into a readable output:

tkprof testdb_ora_12149.trc impdp_output.dat

And read the file:

insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,

extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr,

spare1, scanhint)

values

(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,DECODE(:17,0,NULL,

:17),:18)

call     count       cpu    elapsed       disk      query    current        rows

——- ——  ——– ———- ———- ———- ———-  ———-

Parse      905      0.04       0.01          0          0          0           0

Execute    905      0.26       0.32          0       2756       6420         905

Fetch        0      0.00       0.00          0          0          0           0

——- ——  ——– ———- ———- ———- ———-  ———-

total     1810      0.30       0.34          0       2756       6420         905

Everything is fine. It’s importing the metadata into the database by inserting data to the seg$ (segments) table. I used tail command to get the output of the trace file and run the first query.

After a delay, I got “statement suspended, wait error to be cleared” message, checked alert.log file and found that the USERS tablespace is FULL

SQL> /

SID EVENT                                                            SECONDS_IN_WAIT

———- —————————————————————- —————

142 statement suspended, wait error to be cleared                       20

148 wait for unread message on broadcast channel                                 905

152 wait for unread message on broadcast channel                                 246

SQL>

tail –f Alert.log file

statement in resumable session ‘SYSTEM.SYS_IMPORT_FULL_01.1’ was suspended due to

    ORA-01658: unable to create INITIAL extent for segment in tablespace USERS

After adding some space to the USERS tablespace the message disappeared.

Then suddenly I begin receiving “data file init write” wait event. That was the main reasons why the import took much time.

The reason was that the AUTOEXTEND value of the datafile was set very low and Oracle was making too many calls to OS in order to add new extents

The output from the trace file was as follows:

WAIT #4: nam=’Data file init write’ ela= 3 count=4294967295 intr=32 timeout=2147483647 obj#=51780 tim=13410366772411

In order to define the tablespace which is the cause of this event, I got the object id from the trace file (obj#=51780) and checked obj$ table.

SELECT   name

  FROM   OBJ$

 WHERE   obj# = 51780

Then using DBMS_METADATA.GET_DLL function I got the tablespace name of the object that couldn’t be imported

SELECT   DBMS_METADATA.get_ddl (‘TABLE’, ‘TAB_TEST’, ‘USR01’) FROM DUAL

As the ‘Data file init write’ event directly relates to AUTOEXTEND and occurs while adding extents to the datafile, I checked AUTOEXTEND value of the datafiles. Their values were too small. I increased the AUTOEXTEND values and the event disappeared from the trace log and import job finished successfully.

Reference: Export/Import DataPump Parameter TRACE – How to Diagnose Oracle Data Pump [ID 286496.1]

Last month I got an email from wordpress.com that my password was changed. Hmm … I opened the browser and tried to login to my page, but password was incorrect! I resetted my password and asked wordpress to send me a new password to my email, but I didn’t get it. Ohh .. The password was changed and sent to the hacker’s email! I checked my webpage and got “hacked” message. The hacker get into my wordpress database (either due to wordpress plugin bug or hosting security hole)

I called hosting service and asked them to check my webpage and recover it back immediately! It took more than 2 hours to recover my blog.

In my opinion, getting your webpage hacked is not a big deal, the big deal is if you don’t have backup of your blog! Can you imagine what happened if I lost my blog which contains 140 technical posts with 1983 Comment? Your blog is your career. If you lost it, you can lose your career.

I would advice you to have backup of your blog (don’t rely to hosting backup, make backup after each blog post you post, copy the backup to the different storage), to not use unnecessary and untested wordpress plugins and to choose the one of the best hostings in the market

Two months ago I was invited by my friend, an Oracle ACE Director – Francisco Munoz Alvarez to present at APAC OTN Tour this year. My papers were accepted for Thailand Oracle User Group (OUGTH) meeting. I was very excited as it was my first time to present at OUGTH

At Bangkok airport, I was met by my Azerbaijani friend, Ibrahim Jabbari, who helped me a lot when I was at Bangkok.

At the next day, I got up early, have a wonderful breakfast at the hotel and went to the Eastin Grand Hotel Sathorn for the conference where I was met warmly by OUGTH staff. After a while Francisco and Mike Dietrich arrived.

It was my pleasure to meet Francisco in person  and present with him in the same conference. He has a very deep knowledge of Oracle and he’s doing a great job for the Oracle community. Francisco talked about Database Security and showed some magics with Oracle in real life scenario

Mike, who works Upgrade Consultant for Oracle, presented three sessions in this conference. Two of them were related with Database upgrade, the last one was related with Data Pump which was very interesting. I must say that I haven’t seen such guy who has very strong knowledge of Oracle Upgrade issues in my whole DBA career.

Then I talked about RMAN 11g new features and showed some pre-created video tutorials

Then Thai DBA, Tanakorn talked about Constraints in Thai language, although I didn’t understand anything :), the presentation was prepared good

At the end, we took some photos together with OUGTH staff. You can find some of pictures from the following link:

http://oracle.in.th/?p=11691

To see my updated during the conference, check my twitter account:

https://twitter.com/KamranAgayev

I would like to thank to OTN and OUGTH for organizing this event and wish to present at Bangkok again next year

Here’re some pictures I’ve taken at conference and at Bangkok

I would like to share a good news with you which I got two week ago – I was nominated for Oracle ACE Director award! This is the biggest award in my life. It has encouraged me and I feel myself more responsible and will keep contributing to the community

Whatever path you take in your life, you must always find the time to give something back to the community

I would also like to thank Syed Jaffar Hussain, Uwe Hesse and Porus Homi Havewala for nominating me for this award

My friend and former ACE Director Porus Homi Havewala’s new book on Enterprise Manager 12c Cloud Control, will be available in September 2012 or earlier, perhaps the First EM 12c Cloud Control book in the world.

If you are interested in learning about the capabilities of Enterprise Manager 12c, please have a look. The book can be pre-ordered in advance. Electronic copies will be available too on publication.

http://www.packtpub.com/oracle-enterprise-manager-12c-cloud-control/book

Oracle Enterprise Manager 12c Cloud Control: Managing Data Center Chaos

 

I’m currently reviewing this book and must say that it’s very well written!

There will be an online forum with interesting sessions on BIG Data at February 16th. If you’re interested on Big Data, don’t miss it. Here’s the registration link:

https://event.on24.com/eventRegistration/EventLobbyServlet?target=registration.jsp&eventid=388878&sessionid=1&key=4FEDBC21F62A9834AAE0B109EBBFC918&partnerref=exadata_email1_Jan_Bigdata&sourcepage=register

 

I’m getting a lot of emails from readers asking me to share my OpenWorld 2011 presentation files. So I decided to share it with you. Here, you can download the presentation with necessary video tutorials that I’ve shown at OpenWorld 2011. Download the .zip file and extract it under the folder C:\ and make sure you run the presentation from the following folder –c:\OOW 2011 – Kamran Agayev A\

Downloaded 3125 times

I’ve also uploaded a part of my speech to youtube, you can watch it online:

httpv://www.youtube.com/watch?v=5RPUxotUk9A&hl=en&fs=1&border=1

In this post I’d like to show you how to dump a smallest logical structure of an Oracle Database – Data Block. Have you ever interested what’s actually stored in the data block and if it’s possible to get any information by “dumping” it? Sure you have. You can dump a single data block of the datafile. Let me show you how it’s done

First of all let’s create a new tablespace and a table:

[php]SQL> create tablespace my_tbs datafile ‘/u01/oracle/oradata/repdb/my_tbs.dbf’ size 100m;

Tablespace created.

SQL> create table my_tab tablespace my_tbs as select * from dba_objects;

Table created.[/php]

Now let’s get more information about this table from DBA_SEGMENTS view:

[php]SQL> select header_file, header_block, bytes, blocks, extents from dba_segments where segment_name=’MY_TAB’;

HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
———– ———— ———- ———- ———-
7 9 7340032 896 22

SQL> [/php]

From the output we can say that the header block of the table is stored in the datafile 7, block 9. The size of the table 7340032 bytes (7.3 Mb) and it contains 896 blocks and 22 extents. After getting block size of the database, let’s do some math:

[php]SQL> show parameter db_block_size

NAME TYPE VALUE
———————————— ———– ——————————
db_block_size integer 8192
SQL>
[/php]

BLOCKS (dba_segments) * DB_BLOCK_SIZE (parameter file) = BYTES (dba_segments)

896*8192=7340032

Now let’s query DBA_EXTENTS view to get some information on extents and data blocks of this table:

[php]SQL> col segment_name format a10
SQL> set pagesize 100
SQL> select segment_name, extent_id, block_id, blocks, bytes from dba_extents where segment_name=’MY_TAB’;

SEGMENT_NA EXTENT_ID BLOCK_ID BLOCKS BYTES
———- ———- ———- ———- ———-
MY_TAB 0 9 8 65536
MY_TAB 1 17 8 65536
MY_TAB 2 25 8 65536
MY_TAB 3 33 8 65536
MY_TAB 4 41 8 65536
MY_TAB 5 49 8 65536
MY_TAB 6 57 8 65536
MY_TAB 7 65 8 65536
MY_TAB 8 73 8 65536
MY_TAB 9 81 8 65536
MY_TAB 10 89 8 65536
MY_TAB 11 97 8 65536
MY_TAB 12 105 8 65536
MY_TAB 13 113 8 65536
MY_TAB 14 121 8 65536
MY_TAB 15 129 8 65536
MY_TAB 16 137 128 1048576
MY_TAB 17 265 128 1048576
MY_TAB 18 393 128 1048576
MY_TAB 19 521 128 1048576
MY_TAB 20 649 128 1048576
MY_TAB 21 777 128 1048576
[/php]

Using DBA_EXTENTS view we can get BLOCK_ID range and number of data blocks in each extent. Look at the following output:

[php]MY_TAB 0 9 8 65536
MY_TAB 1 17 8 65536[/php]

Here it’s seen that the first extent (0) contains 8 blocks which id range between 9 and 17
9 (block_id) + 8 (number of blocks) = 17 (block_id of the next extent)

Ok, now let’s start dumping the header block of the table. For this we need data file and header block number. It can be taken from the DBA_SEGMENTS

[php]SQL> select header_file, header_block from dba_segments where segment_name=’MY_TAB’;

HEADER_FILE HEADER_BLOCK
———– ————
7 9

SQL> [/php]

The dump file will be stored in UDUMP directory (user_dump_dest). We can use TRACEFILE_IDENTIFIER to specify part of the trace file name and find it quickly under the UDUMP directory.

[php]SQL> alter session set tracefile_identifier = db_block_dump;

Session altered.[/php]

Now, dump the data block 9 of the datafile 7:

[php]SQL> alter system dump datafile 7 block 9;

System altered.

SQL> [/php]

Ok, now open the second session (in new terminal) and open the trace file. You will see the following output:

[php]tail -f repdb_ora_18308_DB_BLOCK_DUMP.trc

*** 2011-10-28 17:05:21.015
Start dump data blocks tsn: 7 file#: 7 minblk 9 maxblk 9
buffer tsn: 7 rdba: 0x01c00009 (7/9)
scn: 0x0000.02eeaf02 seq: 0x01 flg: 0x04 tail: 0xaf021001
frmt: 0x02 chkval: 0x1a9f type: 0x10=DATA SEGMENT HEADER – UNLIMITED
Extent Control Header
—————————————————————–
Extent Header:: spare1: 0 spare2: 0 #extents: 22 #blocks: 895
last map 0x00000000 #maps: 0 offset: 4128
Highwater:: 0x01c0037a ext#: 21 blk#: 113 ext size: 128
#blocks in seg. hdr’s freelists: 0
#blocks below: 880
mapblk 0x00000000 offset: 21
Unlocked
Map Header:: next 0x00000000 #extents: 22 obj#: 130752 flag: 0x40000000
Extent Map
[/php]

Let’s try to read the above output :

* Start dump data blocks tsn: 7 file#: 7 minblk 9 maxblk 9
This means that the following lines is the output of datafile 7, block 9. Range of blocks can be given to dump:
[php]alter system dump datafile 1 block min 29081 block max 30539 [/php]

* rdba is the Data Block Address which is internal representation of the block address. The first 10 bits contains file number, and the rest 22 bits contains block id. So here if we convert 0x01c00009 to decimal, we would get 7/9 which is shown between brackets. Moreover, DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE and DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK can be used to get the file and block number based on RDA value. But before using these functions, rda should be converted to the decimal value.
So here, we convert the rda value (0x01c00009) to decimal and got – 29360137. Then pass this value to the above mentioned functions:

[php]SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29360137) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(29360137)
———————————————-
7

SQL> select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29360137) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(29360137)
———————————————–
9

SQL> [/php]

In order to convert hexadecimal value to the decimal online, you can use the following link:
http://www.statman.info/conversions/hexadecimal.html

By using DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS function and passing file and block value, we can get RDA value:

[php]SQL> select DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(7,9) from dual;

DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(7,9)
—————————————–
29360137

SQL>
[/php]

* scn – is the SCN value of the block which defines when it was last modified. We can compare it with each transaction SCN value inside the dump file. The SCN value of the data block is 0x0000.02eeaf02. If second part of this value (02eeaf02) is converted to the decimal, then we got – 49196802. If we query the current_scn value we got:

[php]SQL> select current_scn from v$database;

CURRENT_SCN
———–
49249322

SQL> [/php]

* tail is used to keep track of consistency information between the beginning and end of the block against the possibility of distribution of oracle blocks over multiple OS blocks

* frmt – is the block format which tells whether it’s Oracle 7 or Oracle 8 and higher block. 0x02 represents that it’s Oracle 8 and higher block. (0x01 represents that it’s Oracle 7 block)

* chkval – which is checksum written to the blocks when it is set and used by Oracle in part to check the consistency and validity of the block

* type : Type defines which type of block it is. In this example it is “Data Segment Header”

[php] Extent Header:: spare1: 0 spare2: 0 #extents: 22 #blocks: 895 [/php]

Then in the Extent Control Header part we can get information about extents. So it has 22 extents and 895 blocks.

[php] Highwater:: 0x01c0037a ext#: 21 blk#: 113 ext size: 128
#blocks in seg. hdr’s freelists: 0
#blocks below: 880 [/php]

Highwater mark is at extent 21, block 113. This extent has 128 blocks. And there’re 880 data block below it. Let’s do some math:

As it has 880 blocks below and the number of block is 895 (dump file didn’t count the header block), then there’re 895-880=15 data blocks free (and above the hightwater mark)
Moreover, as the highwater mark is at block 113 and the extent has 128 data blocks, then 128-113=15 data blocks are free
So this means that there’s 15*8192=122880 bytes (122Kb) free space

[php] Map Header:: next 0x00000000 #extents: 22 obj#: 130752 flag: 0x40000000[/php]

Next, we got object id. To find more details on that object, query sys.obj# view:

[php]SQL> col name format a10
SQL> select obj#, owner#, name, status, ctime, type# from sys.obj$ where obj#=130752;

OBJ# OWNER# NAME STATUS CTIME TYPE#
———- ———- ———- ———- ——— ———-
130752 0 MY_TAB 1 28-OCT-11 2

SQL> [/php]

So the owner id is 0 (which is SYS user) and type is 2 (which means table)

[php]SQL> select username from dba_users where user_id=0;

USERNAME
——————————
SYS

SQL> [/php]

and to get information about types, use the following query:

[php]select USERNAME,

count(decode(o.TYPE#, 2,o.OBJ#,”)) Tabs,

count(decode(o.TYPE#, 1,o.OBJ#,”)) Inds,

count(decode(o.TYPE#, 5,o.OBJ#,”)) Syns,

count(decode(o.TYPE#, 4,o.OBJ#,”)) Views,

count(decode(o.TYPE#, 6,o.OBJ#,”)) Seqs,

count(decode(o.TYPE#, 7,o.OBJ#,”)) Procs,

count(decode(o.TYPE#, 8,o.OBJ#,”)) Funcs,

count(decode(o.TYPE#, 9,o.OBJ#,”)) Pkgs,

count(decode(o.TYPE#,12,o.OBJ#,”)) Trigs,

count(decode(o.TYPE#,10,o.OBJ#,”)) Deps

from obj$ o,

dba_users u

where u.USER_ID = o.OWNER# (+)

group by USERNAME

order by USERNAME
[/php]

In order to show the free space, we can use DBMS_SPACE.UNUSED_SPACE procedure. Let’s create a procedure :

[php]SQL> create or replace
2 procedure show_space
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default ‘TABLE’,
6 p_partition in varchar2 default NULL )
7 authid current_user
8 as
9 l_free_blks number;
10 l_total_blocks number;
11 l_total_bytes number;
12 l_unused_blocks number;
13 l_unused_bytes number;
14 l_LastUsedExtFileId number;
15 l_LastUsedExtBlockId number;
16 l_LAST_USED_BLOCK number;
17
18 procedure p( p_label in varchar2, p_num in number )
19 is
20 begin
21 dbms_output.put_line( rpad(p_label,40,’.’) ||
22 p_num );
23 end;
24
25 begin
26 dbms_space.unused_space
27 ( segment_owner => p_owner,
28 segment_name => p_segname,
29 segment_type => p_type,
30 partition_name => p_partition,
31 total_blocks => l_total_blocks,
32 total_bytes => l_total_bytes,
33 unused_blocks => l_unused_blocks,
34 unused_bytes => l_unused_bytes,
35 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
36 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
37 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
38
39 p( ‘Total Blocks’, l_total_blocks );
40 p( ‘Total Bytes’, l_total_bytes );
41 p( ‘Total MBytes’, trunc(l_total_bytes/1024/1024) );
42 p( ‘Unused Blocks’, l_unused_blocks );
43 p( ‘Unused Bytes’, l_unused_bytes );
44 p( ‘Last Used Ext FileId’, l_LastUsedExtFileId );
45 p( ‘Last Used Ext BlockId’, l_LastUsedExtBlockId );
46 p( ‘Last Used Block’, l_LAST_USED_BLOCK );
47 end;
48
49 /

Procedure created.

SQL> set serveroutput on
SQL> exec show_space(‘MY_TAB’,’SYS’);
Total Blocks……………………….896
Total Bytes………………………..7340032
Total MBytes……………………….7
Unused Blocks………………………15
Unused Bytes……………………….122880
Last Used Ext FileId………………..7
Last Used Ext BlockId……………….777
Last Used Block…………………….113

PL/SQL procedure successfully completed.

SQL> [/php]

Now I copy/paste the math I’ve done above again to compare both results.

As it has 880 blocks below and the number of block is 895 (dump file didn’t count the header block), then there’re 895-880=15 data blocks free (and above the hightwater mark)
Moreover, as the highwater mark is at block 113 and the extent has 128 data blocks, then 128-113=15 data blocks are free
So this means that there’s 15*8192=122880 bytes (122Kb) free space

As you see comparison of both values led us to the same results

So in this post you’ve learned how to dump a header block of the segment and how to read it. In the next post I’ll try to go more deep and dump a data block that contains data, make some transactional changes on the data and see what happens in the background in the block level!