OraFAQ Forum: General » Default Tablespace

Home » Other » General » Default Tablespace

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Default Tablespace [message #254078]

Wed, 25 July 2007 12:53

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

hi guys,
could anybody tell me what is the syntax to view the default & temporary tablespace in oracle9i ??

Regards,
Balaji

Report message to a moderator

Re: Default Tablespace [message #254079 is a reply to message #254078]

Wed, 25 July 2007 13:07

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

dba_users.

Regards
Michel

Report message to a moderator

Re: Default Tablespace [message #254082 is a reply to message #254079]

Wed, 25 July 2007 13:12

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

hi ,

Could u tellme the command to find default & temporary tablespace other than dba_users ?? i mean for the entire session.

Regards,
Balaji

Report message to a moderator

Re: Default Tablespace [message #254083 is a reply to message #254082]

Wed, 25 July 2007 13:16

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

i mean for the entire session.

What does that mean?

Regards
Michel

Report message to a moderator

Re: Default Tablespace [message #254087 is a reply to message #254083]

Wed, 25 July 2007 13:27

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

hi,

could u tell me the command other than dba_users to find default & temporary tablespace in oracle9i.

Regards,
Balaji

Report message to a moderator

Re: Default Tablespace [message #254089 is a reply to message #254087]

Wed, 25 July 2007 13:36

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

SQL> ;
  1  select PROPERTY_NAME,PROPERTY_VALUE
  2  from database_properties
  3* where property_name in ('DEFAULT_TEMP_TABLESPACE','DEFAULT_PERMANENT_TABLESPACE')
SQL> /

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_TEMP_TABLESPACE        MY_TMP_TS
DEFAULT_PERMANENT_TABLESPACE   MY_USERS

Report message to a moderator

Re: Default Tablespace [message #254090 is a reply to message #254087]

Wed, 25 July 2007 13:36

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is none.
user/dba_users are the only views.
Why not dba_users?

Regards
Michel

Report message to a moderator

Re: Default Tablespace [message #254091 is a reply to message #254090]

Wed, 25 July 2007 13:37

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Michel
Not database_properties?

Report message to a moderator

Re: Default Tablespace [message #254092 is a reply to message #254089]

Wed, 25 July 2007 13:38

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

DreamzZ,

IN 9I

Please read CAREFULLY the question.

Regards
Michel

Report message to a moderator

Re: Default Tablespace [message #254093 is a reply to message #254091]

Wed, 25 July 2007 13:40

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No database default tablespace in 9i, only database default temporary tablespace.

Regards
Michel

Report message to a moderator

Re: Default Tablespace [message #254094 is a reply to message #254092]

Wed, 25 July 2007 13:43

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

yes in oracle 9i. database_properties does not contain default & temporary tablespace.

could you tell me any other views??

Report message to a moderator

Re: Default Tablespace [message #254095 is a reply to message #254093]

Wed, 25 July 2007 13:44

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Thanks For correction.

Report message to a moderator

Re: Default Tablespace [message #254096 is a reply to message #254094]

Wed, 25 July 2007 13:48

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

could you tell me any other views??

No, as there is none.

WHY NOT %_USERS????

Regards
Michel

Quote:

[Updated on: Wed, 25 July 2007 13:49]

Report message to a moderator

Re: Default Tablespace [message #254097 is a reply to message #254096]

Wed, 25 July 2007 13:54

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

hi,

there is no such view called %_users (illegal identifier)

Regards,
Balaji

Report message to a moderator

Re: Default Tablespace [message #254098 is a reply to message #254097]

Wed, 25 July 2007 13:55

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

balaji1482 wrote on Wed, 25 July 2007 12:54

hi,

there is no such view called %_users (illegal identifier)

Regards,
Balaji

Report message to a moderator

Re: Default Tablespace [message #254102 is a reply to message #254098]

Wed, 25 July 2007 14:12

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

hi,

%_users is an illegal identifier.

Report message to a moderator

Re: Default Tablespace [message #254105 is a reply to message #254078]

Wed, 25 July 2007 14:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

2:21:55 SQL> select view_name from dba_views where view_name like '%_USERS';

VIEW_NAME
------------------------------
V_$PWFILE_USERS
GV_$PWFILE_USERS
USER_USERS
ALL_USERS
DBA_USERS
PROXY_USERS
AQ$INTERNET_USERS

7 rows selected.

12:22:27 SQL>

Report message to a moderator

Re: Default Tablespace [message #254112 is a reply to message #254102]

Wed, 25 July 2007 14:48

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

balaji1482 wrote on Wed, 25 July 2007 21:12

hi,

%_users is an illegal identifier.

Are you kidding or are you really stupid?
In the latter case, I recommend you to blow off your brain maybe you'll become smarter after that.

Regards
Michel

Report message to a moderator

Re: Default Tablespace [message #254123 is a reply to message #254105]

Wed, 25 July 2007 15:58

balaji1482
Messages: 312
Registered: July 2007
Location: INDIA

Senior Member

hey i already told u i asked u the default & temporary tablespace for the index data,table data, etc... not only the user default & tablespace.

now u r the big stupid .

first understand the question properly .

Report message to a moderator

Re: Default Tablespace [message #254126 is a reply to message #254123]

Wed, 25 July 2007 16:20

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

balaji1482 wrote on Wed, 25 July 2007 14:58

calm down man,Respect seniors

Quote:

hey i already told u i asked u the default & temporary tablespace for the index data,table data, etc

ok tell me when you will create table where it will go?
offcourse your default tablespace.
if i have default tablespace USERS and i have quota on USERS1 i issue the create table command where will be table create in this case USERS or USERS1
it will create in USERS because thats mine default tablespace but if i create table like this

create table balaji
tablespace USERS1
as
select * from dba_users

in this case it will not create in default tablespace.

[Updated on: Wed, 25 July 2007 16:21]

Report message to a moderator

Re: Default Tablespace [message #254127 is a reply to message #254078]

Wed, 25 July 2007 16:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

balaji1482,
>now u r the big stupid

Folks looking for answers should NOT disparage those who can & do provide answers.

Please NOTE this link on every post "ignore all messages by this user".

You'll never know for sure how many "Senior Members" just clicked this link for you.

Report message to a moderator

Re: Default Tablespace [message #254171 is a reply to message #254123]

Thu, 26 July 2007 01:26

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

i already told u i asked u the default & temporary tablespace for the index data,table data,

I read and read again the whole topic and can't see anywhere you mention table or index.

As you even don't know what you're writing, I maintain my previous advice.

About the notion of default tablespace that you seem to not understand, Dreamz already answered.

I add the following advice: stop watching TV and playing games and start openning Oracle books, avoid advanced ones, just try to read the basic.

Regards
Michel

Report message to a moderator

Re: Default Tablespace [message #254178 is a reply to message #254123]

Thu, 26 July 2007 01:43

Maaher
Messages: 7065
Registered: December 2001

Senior Member

balaji1482 wrote on Wed, 25 July 2007 22:58

hey i already told u i asked u the default & temporary tablespace for the index data,table data, etc... not only the user default & tablespace.

You did not.

balaji1482 wrote on Wed, 25 July 2007 22:58

now u r the big stupid .

you should reread what you type. There are characters missing. That's rude and unprofessional. I detest language like that. I really do.
Second: It is YOU who ask a question here. If your not satisfied with the response at least have some respect for the people who take the time to answer you without getting anything in return.

balaji1482 wrote on Wed, 25 July 2007 22:58

first understand the question properly .

First understand a database properly. %_USERS (% as in "Oracle wild card", an equivalent to the asterisk or "*" in Windows) can be used in the query 'SELECT object_name FROM dba_objects WHERE object_name like '%_USERS'.

Sad. Just sad.

MHE

[Updated on: Fri, 27 July 2007 01:34]

Report message to a moderator