Sunday 8 April 2018

Find the storage structure when creating a table - Oracle Database

How to find the storage structure when creating a table - Oracle Database


How to know where my table gets stored when it is created? How to find the tablespace, extent, memory allocated information when creating an object in Oracle Database? How to investigate the storage structure in Oracle Database?


Find the storage structure when creating a table Oracle


When you create a table, it gets created in the tablespace allotted to you. That's the basic. What if you want to know more like the segment allotted for that particular table or the memory allotted to that table. Below mentioned are some steps which will help you find some detailed information regarding the storage structure which is created when you create some object in the database.

1) Login with SYSTEM user.

C:\Users\Parvinder Rathore>sqlplus system@dbcareer

SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 8 18:59:26 2018

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2) Create a table.

SQL> create table dbtab1 (col1 varchar2(10), col2 varchar2(20), col3 date, col4 number(5));

Table created.

3) Find out the information regarding tablespace name, extent Id, size of the extent, file number where extent resides and the block number from where the extent begins for the segment.

SQL> col segment_name for a30;
SQL> set lines 300
SQL> select tablespace_name, segment_name, extent_id, bytes, file_id, block_id from dba_extents where owner='SYSTEM' and segment_name='TAB1';

TABLESPACE_NAME                SEGMENT_NAME                    EXTENT_ID      BYTES    FILE_ID   BLOCK_ID
------------------------------ ------------------------------ ---------- ---------- ---------- ----------
SYSTEM                         TAB1                                    0      65536          1      87824

4) Find out the datafile name, path, size and status where the table is stored by running the below mentioned query. Here fill the File number taken from the above query.

SQL> col name for a50;
SQL> select name, status, bytes/1024/1024 from V$datafile where file#=1;

NAME                                               STATUS  BYTES/1024/1024
-------------------------------------------------- ------- ---------------
E:\APP\PARVINDER\ORADATA\DBCAREER\SYSTEM01.DBF     SYSTEM              700

The above example shows that the table TAB1 exists in 1 extent that is 64KB large. The extent is in the file E:\APP\PARVINDER\ORADATA\DBCAREER\SYSTEM01.DBF which is of around 700MB in size.

I hope this helps !!

No comments:

Post a Comment