How to manage database storage structures?

To Manage database storage strutures first we should know what are logical structures and what are physical structures?
In simpe words the structures which can be queried by sql statement are logical structures and which cannot are physical structures.

When a table is created, a segment is created to hold its data.A tablespace contains collection of segments.When a row is inserted it is ultimately stored in data block in datafile.
A extent occupies set of data blocks from data file and segment comprises of multiple extents.It seems a complex structure but transparent also.

How to create a tablespace?

SQL>create tablespace sales_data datafile
‘/u01/app/oracle/oradata/sales/sales_data01.dbf’ size 10m;

Here I have specified default size for tablespace as 10Mb.
Note: Its good practice to create custom tablespaces and then assign tables to that tablespaces.
It reduces system tablespace overhead.

SQL>CREATE TABLE scott.emp1 tablespace sales_data as select * from scott.emp;

SQL>INSERT INTO SCOTT.EMP1 SELECT * FROM SCOTT.EMP1;

SQL>INSERT INTO SCOTT.EMP1 SELECT * FROM SCOTT.EMP1;
*
ERROR at line 1:
ORA-01653: unable to extend table SCOTT.EMP1 by 128 in tablespace SALES_DATA

So here I altered the size of datafile and then altered tablesapce.

SQL>ALTER DATABASE datafile ‘/u01/app/oracle/oradata/sales/sales_data01.dbf’ resize
20m;

SQL>ALTER tablespace sales_data add datafile
‘/u01/app/oracle/oradata/sales/sales_data02.dbf’ size 20m;

SQL>ALTER DATABASE datafile ‘/u01/app/oracle/oradata/sales/sales_data01.dbf’
autoextend ON next 5m maxsize 100M;

By setting autoextend on,at each insert filesize gets incresed upto max specified size.

Note: All commands needs DBA privilages.

storage structures1
storage structures2

Another example where I tried to create a table in express edition where system.dbf has size of 614mb. While creating a table from another, it gave me this ORA-01652. Here I resolved using same way by altering datafile size with autoextend on mode and specified max size.

storage structures4

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j