Queries for Tablespace in Oracle

Helpful Queries for Tablespace in Oracle

In this article, I am going to discuss Helpful Queries for Tablespace in Oracle. Please read our previous article where we discussed Creating a new PDB for Tablespace Practice in Oracle.

Helpful Queries for Tablespace in Oracle

As of now, we have learned about what is tablespace and we have created a new database and a new pluggable database admin. Now, we will learn about the tablespace exercise. We will try to understand how to query tablespaces. We will be working with the SQL developer on the queries.

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 New Pluggable Database.

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

Connect to New Pluggable Database

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

Connect to New Pluggable Database

Step 2: Check the Database Properties.

There is a data dictionary called database_properties. This data dictionary displays a lot of information about the currently logged database. Let’s go ahead and use this data dictionary and run a query to check the database properties of the database PDBTS.

Query: select * from database_properties;

Check the Database Properties

There is a lot of information about the database to look at. There is a parameter global_db_name which is a combination of the database name and domain name. Apart from this, there are other two properties we have to check as we are working on the tablespaces. DEFAULT TEMPORARY TABLESPACE and DEFAULT PERMANENT TABLESPACE are the two properties that we have to take a look at.

Helpful Queries for Tablespace in Oracle

We can see the default permanent tablespace is the users tablespace and the default temp tablespace is the temp tablespace. We have created this pluggable database using DBCA. Whenever we create a database using DBCA then the DBCA will set the users tablespace as the default tablespace for the newly created database. Let us go ahead and show how the database is taken by default.

Step 3: Create a User with Default Temporary and Primary Tablespaces.

Let us go ahead and create a user hr. We are not going to mention the default tablespace and default temporary tablespaces and the database will take the default database properties.

Statement: create user hr identified by hr;

Create a User with Default Temporary and Primary Tablespaces

So, we have created the hr user. Let’s go ahead and check the entire statement behind this statement. There is a package called dbms_metadata which contains some procedures and functions. This package is from oracle. We have a function called get_ddl which returns the DDL statement for any object. So, let us make a query to get the DDL for the newly created user hr. The syntax for the statement is as follows

Syntax: Select dbms_metadata.get_ddl(‘object’,’object_name’) from dual;

Statement: select dbms_metadata.get_ddl(‘USER’,’HR’) from dual;

Create a User with Default Temporary and Primary Tablespaces

From the output, we can see the DDL statement that is used to create the hr user. The oracle database adds some other clauses in the statement that we don’t see. If we double-click on the statement, we will get the entire statement. Let’s go ahead and double-click on the entire query to check the entire statement behind the user creation.

Helpful Queries for Tablespace in Oracle

From the output, we can see the default tablespace is users and the temporary tablespace is temp. This is added default by oracle.

Step 4: Create a Table in the hr Schema

Let us go ahead and create a table in the hr schema. Currently, we are logged in to the pdbts_admin user. Let’s go ahead and create a table in the hr schema. pdbts_Admin has all the privileges to create the table in the hr schema as pdbts_admin is the admin user in pdbts database.

Statement: create table hrms.emp (emp_id number, ename varchar2(100));

Create a Table in the hr Schema

The table emp is created in the hr schema. Let’s go ahead and insert two records in the table emp in the hr schema.

Statement:
Insert into hr.emp values(1, ‘JAY’);
Insert into hr.emp values(2, ‘ABDC’);

Create a Table in the hr Schema

We got an error indicating no privileges on tablespace users. This is because while creating the hr user we didn’t grant any tablespace size privileges to the user hr. So, in order to bypass this error, we have to grant unlimited space to user hr on the tablespace users.

Statement: grant unlimited tablespace to hr;

Helpful Queries for Tablespace in Oracle

So, we have granted unlimited tablespace. So, generally, the hr doesn’t have any quota of space on the tablespace users. So, we are using this grant statement to grant the quota size to the hr schema on users tablespace. Now, let’s try to run the insert statement again.

Statement:
Insert into hr.emp values(1, ‘JAY’);
Insert into hr.emp values(2, ‘ABDC’);

Helpful Queries for Tablespace in Oracle

So, now the two rows are inserted. When the database admin needs to insert any row in the table emp in the hr schema. The user hr doesn’t have any quota on the tablespace users. So, everything is working fine. As we have created table emp. Let’s check the existence of the table using the data dictionary dba_tables.

Query: select * from dba_tables where owner=‘HR’;

Helpful Queries for Tablespace in Oracle

From the output, we can see that the default tablespace is users. So, any data that is stored in table emp will be stored in users tablespace.

Step 5: Tablespace Information using Data Dictionary dba_tablespaces.

We are going to use another data dictionary called dba_tablespaces. This data dictionary has a lot of information regarding tablespaces. Let’s check about this data dictionary.

Command: desc dba_tablespaces

We have more information about the tablespaces. We are not going to cover the entire data dictionary. We will be learning about TABLESPACE_NAME, BLOCK_SIZE, STATUS, CONTENTS, LOGGING, SEGMENT_SPACE_MANAGEMENT, and COMPRESS_FOR because we have discussed these properties during our tablespace information.

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

Tablespace Information using Data Dictionary dba_tablespaces

From the output, we can see we have five 5 tablespaces. The block size of each tablespace is 8kb. The status of the tablespaces is online so the users can create data in these tablespaces. The contents section is different for different tablespaces according to their role in the database. Regarding LOGGING all the tablespaces are in logging mode but only the temp tablespace is in nologging mode. This mode is not mandatory for temp tablespace. The tablespaces will be in logging mode, the information will be going to the redo log file and this information might be necessary while backup and recovery methods. The segement_space_management is auto for most of the tablespaces.

There is another view called v$tablespace. This view also displays similar kinds of information.

Tablespace Information using Data Dictionary dba_tablespaces

We have 5 tablespaces. We have columns mentioned “INCLUDED IN DATABASE BACKUP” which is yes for all tablespaces except the temp as the temp tablespace is not for backup. The other column is a big file and all the tablespaces display no in this column. Actually, a big file is a data file that is a single datafile

Step 6: Data File Information.

A tablespace is a logical form. Physically there is a data file that stores all this information in the server. We have a data dictionary called dba_data_files which displays the information about the data files. Let’s go ahead and run a query to check the information about data files.

Query: select * from dba_data_files;

Data File Information

From the output we can see there are four data files mentioned for the tablespaces system, sysaux, undo, users tablespaces. There is a column called bytes. All the data files are stored in the form of bytes. Let’s take the system data file. The data file size is 283115520. This is in bytes. If we need to convert this into megabytes. We have to divide it by 1024 and again divide it by 1024. Then the value of the datafile would be around 270 MB.

We have another column called blocks. The Blocks size of the file in Oracle blocks. The other column status describes the status of the data file. The other column is auto extensible which oracle auto extends the datafile once it reaches a certain limit. INCREMENT_BY column is used to increment the number of tablespaces blocks once the tablespace had reached its threshold value. The other columns are USER_BYTES and USER_BLOCKS. The USER_BYTES describes the size of the file available for user data. USER_BLOCKS is the number of blocks that can be used by the data. This is the actual data about the metadata.

If we require any information about the tablespace and datafiles we can go through the below website

Data File Information

This is documentation about the data file from the original oracle website. We can see a lot of information about all the parameters in the data file. We can search on www.google.com as well by searching with dba_data_files 19c

Helpful Queries for Tablespace in Oracle

So, here we can go ahead and search for the parameter that is necessary. Let’s go ahead and check another view for datafile v$datafile. Let’s query with v$datafile.

Query: select * from v$datafile;

Helpful Queries for Tablespace in Oracle

We can see the information about the data files.

Step 7: Check the Tempfile.

There is another data dictionary view for tempfile. The data dictionary is dba_temp_files. We use this data dictionary to view the tempfile.

Query: select * from dba_temp_files;

Check the Tempfile

So, here we can view the information about the temp files which is required for temp tablespace. There is a similar view called v$tempfile. Let’s try to query the view.

Query: select * from v$tempfile.

This is the information about the tempfile.

Note: The extension of the datafile and tempfile is .dbf. Any dba cannot remember all the information as the database is a vast subject. If we need to learn any information then we will be using the internet and browsing the oracle documentation.

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

Leave a Reply

Your email address will not be published. Required fields are marked *