Permanent tablespace contains persistent schema object which means that the data stored in the permanent tablespace persists beyond the duration of a session or transaction. Objects in permanent tablespaces are stored in data files.
In this web article I will explain to you how to create a small file as well as a big file permanent tablespace in oracle database. So let’s start.
Suggested Reading: Tablespace Introduction
Database Connection
The first step in the creation of any type of tablespace is to connect to your database. You can do this by using sys user or any other user which either has sysdba privileges or CREATE TABLESPACE system privilege. I will connect to my database using SYS user with sysdba privileges.
C:\> sqlplus / as sysdba
How To create smallfile permanent tablespace
To create a tablespace we use CREATE TABLESPACE ddl statement
CREATE SMALLFILE TABLESPACE rebellionrider
DATAFILE
‘C:\app\TBSP_DEMO\Rebell1.dbf’ SIZE 100M,
‘C:\app\TBSP_DEMO\Rebell2.dbf’ SIZE 100M
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
SEGMENT SPACE MANAGEMENT AUTO;
This ddl statement starts with CREATE TABLESPACE where both of which are oracle reserved keywords. Next, since we are creating a small file tablespace thus we can explicitly specify this by writing keyword smallfile in between CREATE and TABLESPACE.
CREATE SMALLFILE TABLESPACE
Though this step is optional since oracle, by default, creates all the tablespaces as small file tablespace yet it is a good practice as it makes your code more readable. After CREATE SMALLFILE TABLESPACE you have to give the name of your tablespace. Tablespace name is completely user defined which means that you can give whatever name you want to your tablespace. In my case I will name my tablespace RebellionRider.
DATAFILE:
Using this clause we can add one or more datafiles to our tablespace. As I mentioned in my previous tutorial that Tablespaces are nothing but logical storage units made up of one or more datafiles.
To add the datafile you just have to write the name of the clause which is DATAFILE and then inside the single quote you either have to specify the name of datafile & the directory path (where you want to put your datafile) as I did here or you can simply write the name of your datafile. In latter case, oracle engine will create your datafile and place it in the default directory.
After writing the name of your datafile you have to specify the size for your datafile using SIZE clause which is mandatory, if you forget to do so then oracle engine will give you an error. Let’s say I want to specify 100MB as the size for my datafile rebel1.
LOGGING CLAUSE:
The logging clause lets you specify whether creation of a database object will be logged in the redo log file (LOGGING) or not (NOLOGGING). Logging clause has two options:
- Logging and
- Nologging
If you specify Logging then oracle engine will Generate redo logs for creation of tables, indexes and partitions, and for subsequent inserts. But in case you do not want to generate redo logs for all those operations which I just mentioned then simply write “NOLOGGING”. However logging is always better as it makes recovery much easier.
EXTENT MANAGEMENT:
Extents are the group of fixed number of bytes of disk space called data blocks or we can also say that extents are a group of data blocks.
You can use tablespaces either with local extent management or the older technique of dictionary extent management. The local extent management clause lets you specify how the extents of the tablespace will be managed. Oracle gives you two ways to manage extents:
- Uniform
- Autoallocate
Uniform option tells the database to allocate and de-allocate extents in the tablespace with the same unvarying size that you can specify or let extents default to be 1MB. UNIFORM is the default for temporary tablespaces and cannot be specified for undo tablespaces.
On the other hand, AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. The above query demonstrates the Uniform Extent Management with size 100MB.
If you want to switch extent management from uniform to auto-allocate then you just have to write auto allocate at the place of size because in this mode the oracle engine decides the sizes for the extents of your tablespace.
EXTENT MANAGEMENT LOCAL AUTOALLCOATE;
SEGMENT SPACE MANAGEMENT
Similar to the extents, Segments are the collection of one or more extents. For tablespaces that have local extent management, you can use either manual or automatic segment space management. Unlike extent management here we do not have to specify the size. But Oracle strongly recommends AUTOMATIC segment space management for permanent and locally managed tablespaces. In the above query I have set the Segment space management as AUTO. If you want to switch segment space management from auto to manual then simply replace the SEGEMENT SPACE MANAGEMENT clause with this one –
SEGMENT SPACE MANAGEMENT MANUAL;
Execution of the above query will create you a small file permanent tablespace
How To Create Big file Permanent Tablespace.
While creating a big file tablespace you just have to take care of a few things such as a big file tablespace can have only 1 data file unlike the small file tablespace where we can add several data files. Also while creating a big file tablespace it’s mandatory to write keyword BIGFILE in between Create and tablespace keywords otherwise oracle engine will create a small file.
Info Byte: Always remember, no two tablespaces and data files in a database can have the same name. Thus don’t forget to change the name of your tablespace and its data files.
The query for creating a big file permanent tablespace will be:
CREATE BIGFILE TABLESPACE “RebellionRider2”
DATAFILE
’C:\APP\MANN\ORADATA\ORCL\Big_Rebell.dbf’ SIZE 1G LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
In this query I am creating a big file permanent tablespace with the name of RebellionRider2 which has only 1 datafile Big_Rebell.dbf apart from this rest of the clauses are the same as in the previous query.
So that’s the steps of How to create permanent tablespace.
You can watch the tutorial for the practical execution of the steps. Kindly please share this on your social network and help me reach out to more people. Thanks & Have a great day!
