What is Tablespace?

Oracle physically stores data in data files, along with the physical structure there is also a logical structure. Logically each database consists of two or more tablespace, each tablespace consists of one or more data files. One data file can only belong to one tablespace. We can create new tablespaces if we want to separate both physically and logically the data from two systems: CRM and reporting system.

When creating a new space we have to point to the file in which the data contained in that tablespace is to be located. We also need to determine its size. Here 100MB. If the tablespace is full, the file will not expand.

 

Note that the file immediately after the creation takes 100MB. In fact, it is empty, this space has only been allocated to the needs of this tablespace data.

 

We can also configure the tablespace so that it can expand at full capacity:

 

By the way, we can also indicate how much to expand. In the example below, if you run out of space, the file will expand immediately by 10MB:

 

Tablespace deletes the DROP command:

You will find the tablespace information in the dba_tablespaces dictionary:

 

By creating an object we can specify which tablespace to find:

 

Verification:

 

It is also possible to create a user so that all objects that will be created will be in the selected tablespace:

 

Existing objects can also be moved between tablespace:

ALTER TABLE HR.EMPLOYEES MOVE TABLESPACE ERP;

Similarly, we can change the user’s property:

ALTER USER HR DEFAULT TABLESPACE ERP;

However, in this case, it is important to remember that all objects created by him will remain in their original place.

Uncle good advice:  If we move objects between tablespace, let’s try to recreate indexes on these objects if they exist. This is because of indexes store ROWID information (this is the address of a given data port), so they automatically become incompatible with the actual state. This makes the indexes no longer used in the query execution process.

 

We can change the state of the tablespace so that the data contained in them is read-only and can not be modified:

 

Transactions that were started during the change of ownership will be completed, the only read-only mode will be applied to subsequent transactions. Of course, we are able to return to the possibility of writing:

 

If we want to completely disable access to the tablespace we switch to offline mode. This functionality is used, for example, when a single tablespace is restored after a crash:

 

When switching to online tablespace becomes available again:

If there is a need (and when the first file in the tablespace reaches 32GB) we can add another file to the tablespace: