To Create Tablespace & Datafile in Oracle

TASK: To Create A Tablespace ( Etc. USERS) and assign size of the Tablespace ( 20M ) then Add 3 Datafiles into the USERS Tablespace and the Total Size of USERS Tablespace should be 80M and Create a VIEW.

[ oracle@localhost ]~ export ORACLE_SID=AR

[ oracle@localhost ]~ sqlplus ‘/as sysdba’

SQL> startup

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1220796 bytes

Variable Size 167776068 bytes

Database Buffers 427819008 bytes

Redo Buffers 7163904 bytes

Database mounted.

Database opened.

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC

---------- ------------------------------ ----- ----- ----- -----

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

3 TEMP NO NO YES

SQL> create tablespace USERS datafile '/u01/app/oracle/oradata/AR/USERS.dbf' size 20m;

Tablespace created.

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC

---------- ----------------------------- ----- ----- ----- ---

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

3 TEMP NO NO YES

4 USERS YES NO YES

SQL> desc v$tablespace;

Name Null? Type

----------------------------------------- -------- ----------------------------

TS# NUMBER

NAME VARCHAR2(30)

INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)

BIGFILE VARCHAR2(3)

FLASHBACK_ON VARCHAR2(3)

ENCRYPT_IN_BACKUP VARCHAR2(3)

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC

---------- ------------------------------ --- --- --- ---

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

3 TEMP NO NO YES

4 USERS YES NO YES

SQL> select a.TABLESPACE_NAME,

a.BYTES bytes_used,

b.BYTES bytes_free,

b.largest,

round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used

from

(

select TABLESPACE_NAME,

sum(BYTES) BYTES

from dba_data_files

group by TABLESPACE_NAME

)

a,

(

select TABLESPACE_NAME,

sum(BYTES) BYTES ,

max(BYTES) largest

from dba_free_space

group by TABLESPACE_NAME

)

b

where a.TABLESPACE_NAME=b.TABLESPACE_NAME

order by ((a.BYTES-b.BYTES)/a.BYTES) desc ;

TABLESPACE_NAME BYTES_USED BYTES_FREE LARGEST PERCENT_USED

------------------------------ -------------------- ------------------- --------------- -----------------------

SYSTEM 209715200 35463168 35463168 83.09

SYSAUX 104857600 56885248 56885248 45.75

USERS 20971520 20905984 20905984 .31

SQL> select OWNER,

SEGMENT_NAME,

SEGMENT_TYPE,

TABLESPACE_NAME,

BYTES

from dba_segments

where TABLESPACE_NAME = 'SYSTEM'

and OWNER not in ('SYS','SYSTEM')

order by OWNER, SEGMENT_NAME ;

OWNER

------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

SEGMENT_TYPE TABLESPACE_NAME BYTES

------------------ ------------------------------ ----------

OUTLN

OL$

TABLE SYSTEM 16384

OUTLN

OL$HINTS

TABLE SYSTEM 16384

OWNER

------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

SEGMENT_TYPE TABLESPACE_NAME BYTES

------------------ ------------------------------ ----------

OUTLN

OL$HNT_NUM

INDEX SYSTEM 16384

OUTLN

OL$NAME

OWNER

------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

SEGMENT_TYPE TABLESPACE_NAME BYTES

------------------ ------------------------------ ----------

INDEX SYSTEM 16384

OUTLN

OL$NODES

TABLE SYSTEM 16384

OUTLN

OWNER

------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

SEGMENT_TYPE TABLESPACE_NAME BYTES

------------------ ------------------------------ ----------

OL$SIGNATURE

INDEX SYSTEM 16384

OUTLN

SYS_IL0000000453C00021$$

LOBINDEX SYSTEM 16384

OWNER

------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

SEGMENT_TYPE TABLESPACE_NAME BYTES

------------------ ------------------------------ ----------

OUTLN

SYS_LOB0000000453C00021$$

LOBSEGMENT SYSTEM 24576

8 rows selected.

SQL> select TABLESPACE_NAME,

sum(BYTES) Total_free_space,

max(BYTES) largest_free_extent

from dba_free_space

group by TABLESPACE_NAME;

TABLESPACE_NAME TOTAL_FREE_SPACE LARGEST_FREE_EXTENT

------------------------------ ------------------------------- -----------------------------------

SYSAUX 56885248 56885248

USERS 20905984 20905984

SYSTEM 35422208 35422208

SQL> select * from dba_free_space;

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO

------------------------------ ---------- --------------- -------------- ---------- -----------------------

SYSTEM 1 21277 35422208 4324 1

SYSAUX 3 5857 56885248 6944 3

USERS 4 9 20905984 2552 4

SQL> select TABLESPACE_NAME,

sum(BYTES) BYTES

from dba_data_files

group by TABLESPACE_NAME;

TABLESPACE_NAME BYTES

------------------------------ ----------

UNDOTBS1 20971520

SYSAUX 104857600

USERS 20971520

SYSTEM 209715200

SQL> create view dbdatafiles as select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name;

View created.

SQL> select * from dbdatafiles;

TABLESPACE_NAME BYTES

------------------------------ ----------

UNDOTBS1 20971520

SYSAUX 104857600

USERS 20971520

SYSTEM 209715200

SQL> alter tablespace USERS add datafile '/u01/app/oracle/oradata/AR/Users1.dbf ' size 20m;

Tablespace altered.

SQL> select * from dbdatafiles;

TABLESPACE_NAME BYTES

------------------------------ ----------

UNDOTBS1 20971520

SYSAUX 104857600

USERS 41943040

SYSTEM 209715200

Note : After Adding a Datafile Users1.dbf the Tablespace USERS Size Increased.

SQL> alter tablespace USERS add datafile '/u01/app/oracle/oradata/AR/Users2.dbf ' size 20m;

Tablespace altered.

SQL> alter tablespace USERS add datafile '/u01/app/oracle/oradata/AR/Users3.dbf ' size 20m;

Tablespace altered.

SQL> select * from dbdatafiles;

TABLESPACE_NAME BYTES

------------------------------ ----------

UNDOTBS1 20971520

SYSAUX 104857600

USERS 83886080

SYSTEM 209715200

Note: Here the USERS Tablespace 83886080 bytes ie. 80M Increased.