How to Create Permanent Tablespace in Oracle

Back to: Oracle DBA Tutorials

How to Create Permanent Tablespace in Oracle

In this article, I am going to discuss How to Create a Permanent Tablespace in Oracle. Please read our previous article where we discussed Helpful Queries for Tablespace in Oracle.

How to Create Permanent Tablespace in Oracle?

Previously we understood many concepts and many tables in order to query tablescape, data files, and temporary files. Now, we will try to learn about the concept of creating a permanent tablespace. We will try to understand the concept of oracle managed files. We will be explaining this in steps.

Note: Make sure the database and listener are up and running fine. Once the database and listener are up and running. Open SQL Developer and connect to the new pluggable database. The new pluggable database is PDBTS.

Step 1: Connect to a new Pluggable Database.

Connect to the pluggable database PDBTS using the admin user pdbts_Admin.

So, the connection is a success. Let’s go ahead and check the container name and the user name.

Step 2: Create a New Tablespace in Oracle.

We are going to create a tablespace without any additional syntax. We are not going to mention the data file, size, auto-extend, and all the other elements. We can create a tablespace without all these parameters.

Statement: create tablespace test

We got an error that displays the missing datafile/tempfile clause. So, we have a subject called oracle managed files.

Oracle Managed Files:

When creating a tablespace, either a permanent tablespace or an undo tablespace, the datafile clause is optional. When the datafile clause is included the file name is optional. If the datafile clause or file name is not provided, then the following rules apply:

• If the DB_CREATE_FILE_DEST initialization parameter is specified, then an oracle managed datafile is created in the location specified by the parameter.
• If the DB_CREATE_FILE_DEST initialization parameter is not specified then the statement creating the data files fails.
• DB_CREATE_FILE_DEST is a directory to store the database files.

When we tried to create a tablespace, we don’t have oracle managed files setup. We have to assign value to the parameter DB_CREATE_FILE_DEST. Let’s go ahead and check the parameter.

We can see the value of parameter db_create_file_dest is null. So, the oracle cannot manage files if the parameter is null. We have to mention the value of the parameter. So, the oracle will generate files automatically to that location. Let’s try to assign a location to the parameter DB_CREATE_FILE_DEST. But before that let’s try to check the location once. Open the file manager once and go to the below location.

We are in the location /u01/app/oracle/oradata/JAYADB/. In this path, we can see multiple folders for the pluggable databases. We are looking for the PDBTS folder as this path is used for the pluggable database PDBTS. Let’s get into this folder.

We can see the data files for the default tablespaces in the PDBTS folder. So, we are using this path for the DB_CREATE_FILE_DEST. Let’s open the terminal and take the path using the command ‘pwd’.

We are using the path /u01/app/oracle/oradata/JAYADB/PDBTS. Let’s use this path for DB_CREATE_FILE_DEST.

Statement: alter system set DB_CREATE_FILE_DEST=’/u01/app/oracle/oradata/JAYADB/PDBTS/’;

So, we have assigned the value. Let’s try to check the parameter again.

Command: show parameter DB_CREATE_FILE_DEST

So, the value is set. Let’s try to create the tablespace again to check if the creation works or not.

Statement: create tablespace test;

So, the tablespace is created. But we haven’t mentioned any other clauses. Previously we used the DBMS_METADATA package to view the DDL statement that oracle ran. Let’s try to use the same package and check the statement behind it.

Query: select dbms_metadata.get_ddl(‘TABLESPACE’,’TEST’) from dual;

Let’s double-click on the output to get the complete statement.

We can see there is a lot in the create statement. We have just entered the “create tablespace test”. Oracle assigned the size as 100 MB. The size is displayed in bytes. The size is mentioned as 104857600. If we divide it with 1024 and then divide it again with 1024. We will get a value of 100 MB.

We can see auto extend is on. Once the size of the tablespace increases more than 100 MB then the data file will be auto-extended to 32GB. Logging is online and the block size is 8192. This is the default from Oracle. We can use this as a template and build our own tablespaces. We can use only the “create tablespace” statement and oracle do the rest. Let’s check the existence of a new tablespace using the following query.

Query: select TABLESPACE_NAME, BLOCK_SIZE, STATUS, CONTENTS, LOGGING, SEGMENT_SPACE_MANAGEMENT, COMPRESS_FOR from dba_tablespaces;

We can see the new entry of the tablespace test.

Step 3: Check the Existence of the Datafile.

We are going to check the data file for the new tablespace test. As we have mentioned the path /u01/app/oracle/oradata/JAYADB/PDBTS/. Let’s check this folder for the data file.

We can see there is another folder with the name JAYADB that is created. Open this folder till we see the data file.

We are in the path where oracle created a new data file. The data file is created with a default name by oracle. We are in the PDBTS folder and oracle will create and manage the naming of the files. Let’s check the tablespace details with another view v$tablesapce.

Query: select * from v$tablespace;

We can see the tablespace is created. So, let’s check data files using the query.

Query: select * from dba_data_files;

We can see the data file name is generated by oracle.

Step 4: Create a User with Default Tablespace in Oracle.

Let us try to create a user hr2 with the default tablespace as test. So, any data that is created in the hr2 user will be stored in the test tablespace.

Query: create user hr2 identified by hr2 default tablespace test;

Now, let’s check the background statement that has been run by Oracle. We will be using the dbms_metadata package to check the background statement.

Query: select dbms_metadata.get_ddl(‘USER’,’HR2’) from dual;

We got the statement from the package. Double-click on the statement to get the entire create statement.

From the above statement, we can see the default tablespace is temp and the temporary tablespace is temp.

Step 5: Create a table inside the hr2 schema

Let us try to create a table inside the hr2 schema. We are going to create a table named test2 inside the hr2 schema. This table will be created in the tablespace test. This is because the default tablespace for hr2 schema is test.

Statement: create table hr2.test2( n number);

Let’s check the tablespace name for table test2.

Query: select * from dba_tables where owner=’HR2’;

We can see the table name is TEST2 and the default tablespace name is TEST tablespace. So, we have learned a lot of things about oracle managed files. In order to manage the oracle managed files, we have to set the parameter DB_CREATE_FILE_DEST. Then oracle will do the rest to create the data file in the location that we have mentioned in the DB_CREATE_FILE_DEST.

In the next article, I am going to discuss Tablespace Management in Oracle. Here, in this article, I try to explain How to Create a Permanent Tablespace in Oracle. I hope you enjoy this Creating Permanent Tablespace in Oracle article.