Home » RDBMS Server » Server Administration » KNOW MY TABLESPACE
KNOW MY TABLESPACE [message #187030] Thu, 10 August 2006 10:28 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Hi:

I'm a simple user, how can i know what tablespace i'm accessing??
and how can i change it??

Are there any way to specify in a control file (sql loader) the tablespace where my tables are if they´re in another tablespace?

Thanks a lot
Alex.

[Updated on: Thu, 10 August 2006 10:30]

Report message to a moderator

Re: KNOW MY TABLESPACE [message #187033 is a reply to message #187030] Thu, 10 August 2006 10:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I'm a simple user, how can i know what tablespace i'm accessing??
Look into user_users.
This shows only the default tablespace. Not all the tablespaces you have quota on.
test@9i > desc user_users
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 USERNAME                                              NOT NULL VARCHAR2(30)
 USER_ID                                               NOT NULL NUMBER
 ACCOUNT_STATUS                                        NOT NULL VARCHAR2(32)
 LOCK_DATE                                                      DATE
 EXPIRY_DATE                                                    DATE
 DEFAULT_TABLESPACE                                    NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                                  NOT NULL VARCHAR2(30)
 CREATED                                               NOT NULL DATE
 INITIAL_RSRC_CONSUMER_GROUP                                    VARCHAR2(30)
 EXTERNAL_NAME                                                  VARCHAR2(4000)

>>how can i change it?
You can create any object in any tablespace you allowed to.
RESOURCE role allows you to write to any tablespace.

>>Are there any way to specify in a control file (sql loader) the tablespace where my tables are if they´re in another tablespace?
IN sql*loader, you no need to worry about it.
Wherever the table is , you will loading data into it.
If you want to have the data to be loaded into different tablespace, move the table.(if it is a partitioned table, it is a different case)

[Updated on: Thu, 10 August 2006 10:37]

Report message to a moderator

Re: KNOW MY TABLESPACE [message #187035 is a reply to message #187033] Thu, 10 August 2006 10:44 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

yesterday i created the same tables in 2 different tablespaces, and the first still have data... i think the tables weren't deleted.

but i dont know how access the other tables...(in my 2nd tablespace)

thanks for your time
Alex

[Updated on: Thu, 10 August 2006 10:45]

Report message to a moderator

Re: KNOW MY TABLESPACE [message #187037 is a reply to message #187035] Thu, 10 August 2006 10:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> yesterday i created the same tables in 2 different tablespaces
You mean, tables with same table_name and within the same user/schema?
that is not possible ( unless you have enclosed table_name in quotes or something like that).
You must have created the tables as a different user.
scott@9i > select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
EMP                            USERS
PLAN_TABLE                     USERS

scott@9i > create table sample
  2  (id number)
  3  tablespace tools;

Table created.

scott@9i > select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
EMP                            USERS
PLAN_TABLE                     USERS
SAMPLE                         TOOLS

scott@9i > create table sample (id number) tablespace users;
create table sample (id number) tablespace users
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

scott@9i > create table "sample" (id number) tablespace users;

Table created.

scott@9i >  select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
EMP                            USERS
PLAN_TABLE                     USERS
SAMPLE                         TOOLS
sample                         USERS
Re: KNOW MY TABLESPACE [message #187038 is a reply to message #187037] Thu, 10 August 2006 11:00 Go to previous message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

you're right i´m accessing another tables that i dont delete.

Thank you very much
Alex.
Previous Topic: Oracle 10g RAC+ASM Storage Issue
Next Topic: Oracle DOS Versions 4, 5, and 6
Goto Forum:
  


Current Time: Fri Sep 20 10:40:22 CDT 2024