How to Create, Modify, Delete, Tablespace in Oracle Database 10g, 11g, 12c..

 

বিসমিল্লাহির রাহমানির রাহিম আসসালামু আলাইকুম

Oracle Database এর লজিক্যাল স্টোরেজ ইউনিট এর মধ্যে ১টি হল টেবিল স্পেস যেখানে ডেটাবেইজ এর সকল স্কীমা অবজেক্ট(table,view, index, sequence) গুলো জমা থাকে ।

নতুন Database তৈরির সময় Details ভাবে দেওয়া থাকে

1. SYSTEM tablespace
2. SYSAUX tablespace
3. Temp tablespace
4. UNDOTBS1 tablespace
5. USERS tablespace

5টি টেবিলস্পেস থাকে যেখানে SYSTEM,SYSAUX হচ্ছে র্পামানেন্ট Table space

Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 

desc dba_tablespaces;

Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(30)
DEF_INMEMORY VARCHAR2(8)
DEF_INMEMORY_PRIORITY VARCHAR2(8)
DEF_INMEMORY_DISTRIBUTE VARCHAR2(15)
DEF_INMEMORY_COMPRESSION VARCHAR2(17)
DEF_INMEMORY_DUPLICATE VARCHAR2(13)

এখন আমি শুধুমাত্র TABLESPACE_NAME,STATUS,BLOCK_SIZE দেখব

 SELECT TABLESPACE_NAME,STATUS,BLOCK_SIZE FROM DBA_TABLESPACES;

TABLESPACE_NAME STATUS BLOCK_SIZE
------------------------------ --------- ----------
SYSTEM ONLINE 8192
SYSAUX ONLINE 8192
UNDOTBS1 ONLINE 8192
TEMP ONLINE 8192
USERS ONLINE 8192

TABLE SPACE Physical Location দেখতে

SQL> SELECT NAME FROM V$DATAFILE;

NAME

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

C:\ORACLE\APP\ORACLEHOMEUSER\ORADATA\ORCL\SYSTEM01.DBF

C:\ORACLE\APP\ORACLEHOMEUSER\ORADATA\ORCL\SYSAUX01.DBF

C:\ORACLE\APP\ORACLEHOMEUSER\ORADATA\ORCL\UNDOTBS01.DBF

C:\ORACLE\APP\ORACLEHOMEUSER\ORADATA\ORCL\USERS01.DBF

...

10 rows selected.

এখন আমি একটা TABLESPACE তৈরি করব তার কমান্ড

Create tablespace test_tab

 datafile 'C:\ORACLE\APP\ORACLEHOMEUSER\ORADATA\ORCL\test.dbf' size 500M

 AUTOEXTEND ON;

Tablespace created.

 Create tablespace test_tbs2
datafile 'C:\ORACLE\APP\ORACLEHOMEUSER\ORADATA\ORCL\demo_tablespace.dbf' size 800M
extent management local
segment space management auto;

Tablespace created.

আরো কয়েকভাবে তৈরি করা যায়,আর Tablespace এর মধ্যে আরো ২টা table space হচ্ছে Bigfile, small file

Bigfile তৈরির 

Create bigfile tablespace test_big

datafile 'C:\ORACLE\APP\ORACLEHOMEUSER\ORADATA\ORCL\test_big.dbf' size
500M
AUTOEXTEND ON;

Tablespace created.

smallfile tablespace command

Create smallfile tablespace test_small
datafile 'C:\ORACLE\APP\ORACLEHOMEUSER\ORADATA\ORCL\test_small.dbf' size
500M
AUTOEXTEND ON;

Tablespace created.

সকল টেবিলস্পেস দেখতে

select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST_TAB ONLINE
TEST ONLINE

7 rows selected.
Table space এর নাম পর্রিবতন করতে

SQL> alter tablespace test rename to new_tablespace;

Table space এর মুড পর্রিবতন করতে online/offline

SQL> alter tablespace new_tablespace offline;

Tablespace altered.

SQL> alter tablespace new_tablespace online;

Tablespace altered.

টেবিল স্পেস ড্রপ করতে

drop tablespace table_spacename;

SQL> drop tablespace new_tablespace
2 including contents and datafiles;

Tablespace dropped.

এখানে Tablespace এর নামের সাথে সাথে datafile এর লোকেশন সহ মুছে গেছে