Home » RDBMS Server » Server Administration » Increasing tablespace size (Oracle EBusiness 12.1.1 and Linux)
Increasing tablespace size [message #446355] Sun, 07 March 2010 23:41 Go to next message
namb
Messages: 35
Registered: September 2009
Member
I want to increase the size of the tablespace but when i login as sysdba or admin user i can just see the 21 tables in the dba_tablespaces or user_tablespaces. I want to see the tablespaces related to the application.

Can anyone please help which table i should be searching in??

Many thanks in advance....
Re: Increasing tablespace size [message #446389 is a reply to message #446355] Mon, 08 March 2010 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i can just see the 21 tables in the dba_tablespaces or user_tablespaces

dba_tablespaces shows tablespaces not tables.

Tablespace are NOT related to an application for Oracle. This is only your interpretation of tablespace.
DBA_TABLESPACES shows ALL tablespaces.
If you don't see yours then either you're searching the wrong name, either you're not connected to the correct database.

Regards
Michel

[Updated on: Mon, 08 March 2010 01:33]

Report message to a moderator

Re: Increasing tablespace size [message #446539 is a reply to message #446355] Tue, 09 March 2010 03:58 Go to previous messageGo to next message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
Hi,

If you want to search the tablespaces related to Application then try to login to application database.

You can check the application oracle home by using;
#echo $ORACLE_HOME


Thanks-
P
Re: Increasing tablespace size [message #446540 is a reply to message #446539] Tue, 09 March 2010 04:00 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
pokhraj_d wrote on Tue, 09 March 2010 09:58
Hi,

If you want to search the tablespaces related to Application then try to login to application database.

You can check the application oracle home by using;
#echo $ORACLE_HOME


Thanks-
P


What makes you think the OP is logging into a different database?
Re: Increasing tablespace size [message #446771 is a reply to message #446540] Wed, 10 March 2010 05:57 Go to previous messageGo to next message
gmaheshji
Messages: 6
Registered: March 2010
Location: Hyderabad
Junior Member
Run this query ( Insert the tablespace name that you wish to increase.) :-

select file_name, sum(bytes)/(1024*1024*1024) In_GB, autoextensible from dba_data_files where tablespace_name='xxxxxxxxxxx';

This will let you know on which filesystem your datafiles are located. Based on space availablity you can run alter database add datafile sql..

Post your findings / queries if you still have any ?

Cheers..

Mahesh.G

Re: Increasing tablespace size [message #446869 is a reply to message #446771] Wed, 10 March 2010 15:10 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
This would give you an error having to do with "not a group by expression."

You will need to group by all non-aggregate columns.
Re: Increasing tablespace size [message #446999 is a reply to message #446869] Thu, 11 March 2010 07:14 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
You probably need to figure out what all tables are been used in the application.Based on the tables ,you can query the following sql to get what tablespaces they belong to:

SQL>select table_name,tablespace_name 
from user_tables 
where table_name in('<table_name1>','<table_name2>') etc...

[Updated on: Thu, 11 March 2010 07:23] by Moderator

Report message to a moderator

Re: Increasing tablespace size [message #447003 is a reply to message #446869] Thu, 11 March 2010 07:59 Go to previous messageGo to next message
gmaheshji
Messages: 6
Registered: March 2010
Location: Hyderabad
Junior Member
sorry.. Its my mistake..

Use this.

select file_name, sum(bytes)/(1024*1024*1024) In_GB, autoextensible from dba_data_files where tablespace_name='xxxxxxxxxxx'
group by file_name;

Mahesh
Re: Increasing tablespace size [message #447005 is a reply to message #447003] Thu, 11 March 2010 08:05 Go to previous message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
You will STILL get a group by error.
Previous Topic: INVALID Objects
Next Topic: ORA-609 and ORA-28 in oracle 11g
Goto Forum:
  


Current Time: Fri Sep 30 03:48:20 CDT 2016

Total time taken to generate the page: 0.08443 seconds