This content has been marked as final. Show 3 replies
Is it stored in first created tablespace or newly created tablespace?by deafult table created in default tablespace if you'll not mention tablespace clause during creation of table.
like if scott create table it default created in users tablespace but if you've quota in another tablespace than user can create new table in different tablespace.
so answer is yes a user can create table/store data in multiple tablespace.
SQL> select username,DEFAULT_TABLESPACE from dba_users where username='SCOTT'; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ SCOTT USERS
Kuljeet Pal Singh
Thank u for your valuable answer...
if i creates more than one tablespace to the same user means it overrides the actual tablespace or else it creates multiple tablespaces for a single user?
and also if i want to write some data into the tablespace and store it into particular datafile. if i uses multiple tablespace for a single user means then in which tablespace it
writes and stores that data......
I need to mention the particular tablespace name in a query??
A user can have access to multiple tablespaces and similarly multiple users can access and store data in same tablespaces.
You can make a tablespace as default tablespace for a user.
alter user SCOTT default tablespace USERS_DATA;
select default_tablespace from dba_users where username = 'SCOTT';
If scott create any table without giving tablespace name, then it will go into default tablespace ie. USERS_DATA in this case.
Let's say you want to have seperate tablespace for indexes "USERS_INDEX" and you want SCOTT should be able to store indexes
in this tablespace.
In this case you have to give quota on this tablespace to scott.
alter user scott quota unlimited on USERS_INDEX;
Now to create index in "USERS_INDEX" scott need to specify the tablespace name when creating index, otherwise it will go into default tablespace.