Home » RDBMS Server » Server Administration » Unable to increase temp segment
| Unable to increase temp segment [message #126630] |
Wed, 06 July 2005 01:02  |
Rishi Mahajan
Messages: 29
Registered: April 2005
Location: India
|
Junior Member |
|
|
Hi all
contl.sql-
CREATE table CIBILCON_TL(CUST_ID,FORACID, CUST_CONST,SCHM_CODE, lim_sanct_date,
LAST_ADJ_DATE,ACCT_CLS_DATE,SANCT_LIM, CLR_BAL_AMT , MAIN_CLASSIFICATION_USER ) AS select
cibilcmg.cust_id,CIBILGAM.foracid,cibilcmg.cust_const,CIBILGAM.schm_CODE,
lht.lim_sanct_date,
lam.last_adj_date,
cibilgam.acct_cls_date,
lht.sanct_lim,
round(abs(cibilgam.clr_bal_amt)) as
clr_bal_amt,ach.main_classification_user
from cibilcmg,ach,lht,lam, cibilGAM
where
cibilgam.acid=ach.b2k_id(+) and cibilgam.cust_id=cibilcmg.cust_id and
lht.acid=cibilgam.acid;
SQL> @contl.sql
from cibilcmg,ach,lht,lam, cibilGAM
*
ERROR at line 10:
ORA-01652: unable to extend temp segment by 40964 in tablespace SYSTEM
Total records in existing table =1504
Database- Oracle 8i
Please provide a solution
Rgds
Rishi
|
|
|
|
| Re: Unable to increase temp segment [message #126635 is a reply to message #126630] |
Wed, 06 July 2005 01:15  |
Achchan
Messages: 86
Registered: June 2005
|
Member |
|
|
First of all PLEASE DO NOT MESS WITH SYSTEM TABLESPACE!
Do what you want in another (maybe self created) tablespaces.
Create another tablespace and if it became full add another datafiel (or tempfile) to it or resize it's current datafile.
-Let me know what happers
|
|
|
|
| Re: Unable to increase temp segment [message #126646 is a reply to message #126630] |
Wed, 06 July 2005 01:42 |
shettyshetty
Messages: 18
Registered: June 2005
Location: Malaysia
|
Junior Member |
|
|
Rishi,
The user in which you are creating the table has got SYSTEM as its default tablespace and/or temporary tablespace. You should not use SYSTEM tablespace for creating user objects or for as temporary tablespace.
select username,default_Tablespace,temporary_tablesapce
from dba_users;
Find out whether any of the user except SYS & SYSTEM are using SYSTEM as their default or temporary tablespace. If so, change issuing the following command.
alter user <username>
default tablespace <tablespace_name>
temporary tablespace <temp tablespace>;
where tablespace_name is the name of the other tablespaces in your database or create a new one and assign it to the user.
-Sunil Shetty
|
|
|
|
| Re: Unable to increase temp segment [message #126663 is a reply to message #126646] |
Wed, 06 July 2005 02:47 |
Rishi Mahajan
Messages: 29
Registered: April 2005
Location: India
|
Junior Member |
|
|
Hi
Now I have used another database user (tbaadm) to create the database object. The user has default_tablespace (tba_temp) and temporary_tablespace(sort_tblspc) . Besides this, I have added a datafile of 120 M to the temporary tablespace of this user. Now its, giving the same error for default tablespace of this user.
ORA-01652: unable to extend temp segment by 40964 in tablespace TBA_TEMP
What to do next??????????
Rgds
Rishi
|
|
|
|
| Re: Unable to increase temp segment [message #126670 is a reply to message #126630] |
Wed, 06 July 2005 02:56 |
shettyshetty
Messages: 18
Registered: June 2005
Location: Malaysia
|
Junior Member |
|
|
Rishi,
It's self-explantory. Tablespace TBA_TEMP doesn't have enough free space.
Use this sql(Tom Kyte's) to find out the amount of free space in your DB.
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a19 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss|Kbytes"
column pct_max_used format 999.9 heading "%|Max|Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select (select decode(extent_management,'LOCAL','*',' ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/
|
|
|
|
|
|
|
|
| Re: Unable to increase temp segment [message #126708 is a reply to message #126630] |
Wed, 06 July 2005 05:05 |
Rishi Mahajan
Messages: 29
Registered: April 2005
Location: India
|
Junior Member |
|
|
<code>
Tablespace Name KBytes Used Free Used Largest Kbytes Used
------------------- ------------ ------------ ------------ ------ ------------ ------------ ------
*TBA_TEMP 1,433,600 12,656 1,420,944 .9 507,904 0 .0
</code>
|
|
|
|
| Re: Unable to increase temp segment [message #126739 is a reply to message #126630] |
Wed, 06 July 2005 07:45 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Are you using dictionary managed tablespaces with some major fragmentation? What version are you running?
Am I reading that right that you have less than 1K in your largest chunk available but are trying to create extents of 40K each?
Create a locally managed tablespace if you can and try again. If you can't, defragment your tablespace and try again.
|
|
|
|
Goto Forum:
Current Time: Sat Jan 25 05:46:34 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
