OraFAQ Forum: Server Administration » 'defaulted/pointed' to the right tablespace

Home » RDBMS Server » Server Administration » 'defaulted/pointed' to the right tablespace (Oracle 10gR2, 10.2.0.4.0, Solaris 10)

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

'defaulted/pointed' to the right tablespace [message #464592]

Thu, 08 July 2010 08:15

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

Sir/Ma'am,

As I queried our newly created db and checked if the tables I created were supposed to be on the default tablespace (LEDGER)using this SQL command:

SQL> select owner, table_name, tablespace_name from dba_tables
2 where owner = 'ELEDGER01';

OWNER TABLE_NAME TABLESPACE_NAME
-------------------- --------------------- ---------------
ELEDGER01 USER_INFO USERS
ELEDGER01 CERTIFICATES USERS

I need to know how would I point those 2 tables for it's supposed or right tablespace (from USERS to LEDGER)..

Thanks again.

Report message to a moderator

Re: 'defaulted/pointed' to the right tablespace [message #464594 is a reply to message #464592]

Thu, 08 July 2010 08:21

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Look up

alter table move tablespace

Report message to a moderator

Re: 'defaulted/pointed' to the right tablespace [message #464598 is a reply to message #464594]

Thu, 08 July 2010 08:23

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

Senior Member
Account Moderator

In addition, if a user would not have objects in a tablespace then it would have no quota in this tablespace.
This means ban UNLIMITED TABLESPACE privilege and RESOURCE role.

Regards
Michel

[Updated on: Thu, 08 July 2010 08:24]

Report message to a moderator

Re: 'defaulted/pointed' to the right tablespace [message #464611 is a reply to message #464592]

Thu, 08 July 2010 08:50

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

I connected as sysdba, but how would I do it?
It gives me this error:

SQL> alter table USER_INFO move tablespace ELEDGER;
alter table USER_INFO move tablespace ELEDGER
*
ERROR at line 1:
ORA-00942: table or view does not exist

These were all my tablespaces...

SQL> select file_name from dba_data_files order by file_name;

FILE_NAME
-------------------------------------------------
/VOL02/oradata/eledger/system/sysaux01.dbf
/VOL02/oradata/eledger/system/system01.dbf
/VOL02/oradata/eledger/undo/undo01.dbf
/VOL03/oradata/eledger/data/eledger01.dbf
/VOL03/oradata/eledger/data/users01.dbf
/VOL03/oradata/eledger/ndex/indx01.dbf

and this was part of my_create-db.sql script ...

DEFAULT TABLESPACE eledger DATAFILE '/VOL03/oradata/eledger/data/eledger01.dbf' SIZE 2048M

Report message to a moderator

Re: 'defaulted/pointed' to the right tablespace [message #464612 is a reply to message #464611]

Thu, 08 July 2010 08:52

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

SQL> alter table USER_INFO move tablespace ELEDGER;
alter table USER_INFO move tablespace ELEDGER
*
ERROR at line 1:
ORA-00942: table or view does not exist

Try

alter table ELEDGER01.USER_INFO move tablespace ELEDGER

Babu

Report message to a moderator

Re: 'defaulted/pointed' to the right tablespace [message #464615 is a reply to message #464592]

Thu, 08 July 2010 08:57

reym21
Messages: 241
Registered: February 2010
Location: Philippines

Senior Member

It works! Very good!

Thanks for helping me guys!

Regards to you gentlebabu, joy_division & Michel...
God bless!

Report message to a moderator

Re: 'defaulted/pointed' to the right tablespace [message #464617 is a reply to message #464615]

Thu, 08 July 2010 09:10

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

Senior Member
Account Moderator

Quote:

I connected as sysdba

Do NOT do that.

- Never ever use SYS (or SYSDBA) but for maintenance purpose (startup, shutdown, backup, recover)
- SYS/SYSDBA is special
- SYS/SYSDBA is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS/SYSDBA" and you'll see the immediate answer)
- SYS/SYSDBA does not act like any other user
- When you use SYS/SYSDBA Oracle deactivates some code path and activates others
- Whatever you do with SYS/SYSDBA will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS/SYSDBA for anything that can be done by another user.
Use SYS/SYSDBA ONLY for something that can't be done by someone else.

Regards
Michel

Report message to a moderator

Previous Topic:	buffer cache
Next Topic:	Temporary Tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 19 12:33:06 CDT 2024