TABLESPACE CONCEPT IN ORACLE DATABASE

Tablespaces and Data files

Oracle stores data logically in table spaces physically in data files

 

Types of tablespaces

            Big file table spaces

            Small file table spaces

Table space name and data file name location;

 

Small file tablespace;

 

Creating small file tablespace:

 

We can create smallfile tablespace two ways just we give create tablespace query by default it can take it as smallfile table space or else we use create small file tablespace keyword

 

Check tablespace smallfile or bigfile tablespace

 

            Check the BIG column values as no it can be a small file tablespace if it is yes it as bigfile tablespace.

 

Create new user and set tablespace to the user

            By using alter command to set tablespace to the user

 

Datafile size

 

Resize Datafile

Adding new datafile

Using alter command we can add multiple datafile in single tablespace each file size upto 32 GB

 

Read/Write Mode

offline/online Mode

Move Datafile from another location from 12c method

 

Before 12c method   (1st Method) offline database

Step 1-Shutdown database

Step 2 - move the files

 

 

Step 3- startup with mount stage

 

 

Step-4  alter the files in mount stage and open the database  finally the files moved

 

2nd method (online database)

 

Step-1 offline the table space

 

Step -2 move the files

 

Step -3 alter the tablespace

 

 

step -4 get back tablespace online check the result

 

BIG file tablespace

A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size.

 

Create big file tablespace

 

            Mention the bigfile word to create bigfile tablespace

            The BIG column value is YES it can be bigfile tablespace

 

Resize bigfile tablespace

 

Drop tablespace

 

 

 

Read write and move big file tablespace

 

 

Temporary tablespaces

 

Temporary tablespaces are used for special operations, particularly for sorting data results on disk and for hash joins in SQL.

Temp tablespace property name & value

Temp file location

Create new Temp Tablespace

Resizing Temp tablespace:-

Adding new temp datafile:-

Change temp tablespace property value

Drop temp tablespace