CODEWITHSUNDEEP
oracle-databaseschematablespace
user13746660
user13746660

Reputation:

How to change the default tablespace of the schema in Oracle

There is a schema called Docker, in which there are tables named TABLE2, TABLE3.

Example

SELECT * FROM all_all_tables WHERE TABLE_name= 'TABLE3';

Also, the Docker schema belongs to the DEFAULT TABLESPACE SYSTEM tablespace.

select 
    username
,   default_tablespace from dba_users WHERE USERNAME = 'DOCKER';

The following syntax is used to change the default tablespace of the schema. (TS3 tablespace already exists)

ALTER USER docker DEFAULT tablespace TS3;

Then, when I searched again, I found that the DEFAULT TABLESPACE was changed.

select 
    username
,   default_tablespace from dba_users WHERE USERNAME = 'DOCKER';

And, of course, I thought that the tablespace in which TABLE2 and TABLE3 were designated would also have been changed to TS3, and the following statement was executed.

However, the tablespace of the table was SYSTEM, not TS3. I am curious about why it hasn't changed, and I want to know how. SELECT * FROM all_all_tables WHERE TABLE_name= 'TABLE3';

Upvotes: 0

Views: 16724

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

The default tablespace is just that-- a default for when you create a segment (normally a table or an index but a segment could be a partition of a table, a materialized view, or anything else that requires space) and don't specify the tablespace. Once you create a segment and it is assigned to a particular tablespace, it will remain in that tablespace unless you move it.

Assuming you are on 12.2 or later so that online moves are an option (in other versions you'd need to remove the online keyword)

alter table table3
  move online tablespace ts3;

You'd need to do that for each table. If there are also indexes in the system tablespace, you'd want to move those as well

alter index index_name
  rebuild online tablespace ts3;

Depending on the number of tables and indexes involved, you may want to write a bit of dynamic SQL to generate the various alter table and alter index statements for you.

Upvotes: 3

Related Questions