Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, wallacel.

Asked: August 28, 2000 - 1:12 pm UTC

Last updated: March 24, 2003 - 10:16 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Tom:

When i read document about tuning i/o. I don't understand why
I should "put table extents slightly smaller than the datafile"
why not exectly? what kind of overhead are these document talking?
below is the document.


Tuning I/O 20-23
To stripe disks manually, you need to relate an object’s storage
requirements to its I/O requirements.
1. Begin by evaluating an object’s disk storage requirements by
checking:
The size of the object.
The size of the disk.
For example, if an object requires 5GB in Oracle storage space,
then you need one 5GB disk or two 4GB disks to accommodate it.
On the other hand, if the system is configured with 1GB or 2GB
disks, then the object may require 5 or 3 disks, respectively.
2. Compare to this the application’s I/O requirements, as described in "Analyzing I/O Requirements" on page 20-3. You must
take the larger of the storage requirement and the I/O requirement.For example, if the storage requirement is 5 disks
(1GB each), and the I/O requirement is 2 disks, then your application requires the higher value: 5 disks.
3. Create a tablespace with the CREATE TABLESPACE statement. Specify the datafiles in the DATAFILE clause. Each of the files
should be on a different disk. For example:
CREATE TABLESPACE stripedtabspace
DATAFILE ’file_on_disk_1’ SIZE 1GB,
’file_on_disk_2’ SIZE 1GB,ഊSolving I/O Problems
’file_on_disk_3’ SIZE 1GB,
’file_on_disk_4’ SIZE 1GB,
’file_on_disk_5’ SIZE 1GB;
4. Then, create the table with the CREATE TABLE statement. Specify the newly created tablespace in the TABLESPACE clause.
Also specify the size of the table extents in the STORAGE clause. Store each extent in a separate datafile. The table
extents should be slightly smaller than the datafiles in the
************************************************************
tablespace to allow for overhead. For example, when
********************************
preparing for datafiles of 1GB (1024MB), you can set the table extents to be 1023MB. For example:
CREATE TABLE stripedtab (
col_1 NUMBER(2),
col_2 VARCHAR2(10) )
TABLESPACE stripedtabspace
STORAGE ( INITIAL 1023MB NEXT 1023MB
MINEXTENTS 5 PCTINCREASE 0 );
(Alternatively, you can stripe a table by entering an ALTER TABLE ALLOCATE EXTENT statement with DATAFILE 'datafile' SIZE 'size'.) These steps result in the creation of table STRIPEDTAB. STRIPEDTAB has 5 initial extents, each of size 1023MB. Each extent takes up one of the datafiles named in the
DATAFILE clause of the CREATE TABLESPACE statement. Each of these files is on a separate disk. The 5 extents are all allocated immediately, because MINEXTENTS is 5.


and Tom said...


well, they went a tad overboard in their example. 1meg is no slightly smaller to many people.

for a locally managed tablespace, you must allow for 64k/file of overhead for the space management bitmap. All other tablespaces take <blocksize>k bytes from the each file added. It would be better to say "add 1 block or 64k depending on the type of tablespace you have for overhead".

rather then making the extent smaller -- I would probably prefer to make the file a tad bit larger....

Note that if you did not make them a tad smaller in the above example (which is NOT locally managed) you would get an error like this:

system@TKYTE816> create tablespace stripedtabspace
2 datafile 'c:\temp\testing1.dbf' size 1m
3 /

Tablespace created.

system@TKYTE816> alter tablespace stripedtabspace
2 add datafile 'c:\temp\testing2.dbf' size 1m;

Tablespace altered.

system@TKYTE816> alter tablespace stripedtabspace
2 add datafile 'c:\temp\testing3.dbf' size 1m;

Tablespace altered.

system@TKYTE816> alter tablespace stripedtabspace
2 add datafile 'c:\temp\testing4.dbf' size 1m;

Tablespace altered.

system@TKYTE816> alter tablespace stripedtabspace
2 add datafile 'c:\temp\testing5.dbf' size 1m;

Tablespace altered.

system@TKYTE816>
system@TKYTE816> create table stripedtab (
2 col_1 NUMBER(2),
3 col_2 VARCHAR2(10) )
4 TABLESPACE stripedtabspace
5 STORAGE ( INITIAL 1024k NEXT 1024k
6 MINEXTENTS 5 PCTINCREASE 0 );
create table stripedtab (
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace STRIPEDTABSPACE


1 create table stripedtab (
2 col_1 NUMBER(2),
3 col_2 VARCHAR2(10) )
4 TABLESPACE stripedtabspace
5 STORAGE ( INITIAL 1016k NEXT 1016k
6* MINEXTENTS 5 PCTINCREASE 0 )
system@TKYTE816> /

Table created.

That works -- when we consider the 8k overhead for each file. In a locally managed tablespace, it'll be a little different:

system@TKYTE816> create tablespace stripedtabspace
2 datafile 'c:\temp\testing1.dbf' size 1m
3 extent management local
4 uniform size 1024k
5 /
create tablespace stripedtabspace
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required

that shows the overhead is "builtin". It knows that with an extent size of 1024k -- it cannot allocate any extents in that file and fails the command. We'll add 64k to each file and it'll go for us (this is my preferred method -- make the FILE a tad bigger -- not the extent smaller):

system@TKYTE816> create tablespace stripedtabspace
2 datafile 'c:\temp\testing1.dbf' size 1088k
3 extent management local
4 uniform size 1024k
5 /

Tablespace created.

system@TKYTE816> alter tablespace stripedtabspace
2 add datafile 'c:\temp\testing2.dbf' size 1088k;

Tablespace altered.

system@TKYTE816> alter tablespace stripedtabspace
2 add datafile 'c:\temp\testing3.dbf' size 1088k;

Tablespace altered.

system@TKYTE816> alter tablespace stripedtabspace
2 add datafile 'c:\temp\testing4.dbf' size 1088k;

Tablespace altered.

system@TKYTE816> alter tablespace stripedtabspace
2 add datafile 'c:\temp\testing5.dbf' size 1088k;

Tablespace altered.

system@TKYTE816>
system@TKYTE816> create table stripedtab (
2 col_1 NUMBER(2),
3 col_2 VARCHAR2(10) )
4 TABLESPACE stripedtabspace
5 STORAGE ( MINEXTENTS 5 PCTINCREASE 0 );

Table created.





Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, March 22, 2003 - 9:50 pm UTC

BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('AK',10); END;

*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM
ORA-06512: at "SYS.DBMS_STATS", line 4652
ORA-06512: at "SYS.DBMS_STATS", line 4720
ORA-06512: at "SYS.DBMS_STATS", line 4704
ORA-06512: at line 1

It failed as schema analyzing.
What shall I do?
TIA



Tom Kyte
March 23, 2003 - 5:13 pm UTC

no clue as to version or anything -- hmm...


I would guess it is trying to create a temp table or some other object, your default tablespace is set to SYSTEM. You have no ability to create anything in system so it fails.

Try altering the account to have a default tablespace where it can actually do something.

without a testcase, probably won't be able to help you. you could:

alter session set sql_trace=true;
exec dbms_stats....

and then edit the trc file and look for the statement that failed -- that would be very telling (not the tkprof report, the trc file)

A reader, March 22, 2003 - 10:10 pm UTC

follow up: The pct_increase for the SYSTEM is 50%. Is the setup problem? and found all the 5GB SYSTEM full for the Oracle Apps (11.5.7 and 8173 db). Why does Oracle set it this way 50%?

Tom Kyte
March 23, 2003 - 5:14 pm UTC

alter your user to not have system as its default and or temporary tablespace.

then ignore system exists, it is ours.

A reader, March 23, 2003 - 6:04 pm UTC

I used the APPS account to do the schema ANALYZE, which has the default tablespace APPLSYSD. It's 8174 database.

apps@prod1m> exec DBMS_STATS.GATHER_SCHEMA_STATS('ABM',10);
BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('ABM',10); END;

*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM
ORA-06512: at "SYS.DBMS_STATS", line 4652
ORA-06512: at "SYS.DBMS_STATS", line 4720
ORA-06512: at "SYS.DBMS_STATS", line 4704
ORA-06512: at line 1

Trace file (after tkprof with sys=yes):


BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('ABM',10); END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.03 0.03 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.03 0.03 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173
********************************************************************************

SELECT /*+ ordered full(t) full(o) use_hash(o) full(u) use_hash(u) */'TABLE'
OBJ_TYPE,O.NAME OBJ_NAME,U.NAME USER_NAME,O.OBJ# OBJ_NUM
FROM
SYS.TAB$ T,SYS.OBJ$ O,SYS.USER$ U WHERE BITAND(T.PROPERTY,1) != 1 AND
BITAND(T.PROPERTY,512) != 512 AND BITAND(T.PROPERTY,4194304) != 4194304
AND BITAND(T.PROPERTY,8388608) != 8388608 AND BITAND(T.PROPERTY,8192) !=
8192 AND T.OBJ# = O.OBJ# AND O.OWNER# = U.USER# AND O.LINKNAME IS NULL
UNION ALL SELECT /*+ ordered use_nl(c) use_nl(o) use_nl(u) */'INDEX'
OBJ_TYPE,O.NAME OBJ_NAME,U.NAME USER_NAME,O.OBJ# OBJ_NUM FROM SYS.IND$ I,
SYS.CLU$ C,SYS.OBJ$ O,SYS.USER$ U WHERE I.BO# = C.OBJ# AND I.OBJ# =
O.OBJ# AND O.OWNER# = U.USER# AND O.LINKNAME IS NULL ORDER BY 3,1 DESC,2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 2.90 19.10 10282 10342 52 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.92 19.12 10282 10342 52 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

select file#
from
file$ where ts#=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 15 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.00 0.00 0 15 0 7

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
7 TABLE ACCESS BY INDEX ROWID FILE$
8 INDEX RANGE SCAN (object id 39)

********************************************************************************

select file#,block#,length
from
fet$ where length>=:1 and ts#=:2 and file#=:3


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.01 0.01 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.01 1 14 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.01 0.02 1 14 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS CLUSTER FET$
2 INDEX UNIQUE SCAN (object id 7)

********************************************************************************

select file#, block#
from
fet$ where ts#=:1 and file#=:2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.01 0.01 0 0 0 0
Fetch 7 0.00 0.00 0 14 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.01 0.01 0 14 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS CLUSTER FET$
2 INDEX UNIQUE SCAN (object id 7)

********************************************************************************

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173
********************************************************************************

alter session set sql_trace=false


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173

Questions:
Why Oracle setup SYSTEM as pct_increase = 50% for the Oracle APPS?
Why there any CREATE statement caputured in the trace, which result the failed
ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM
Thanks


Tom Kyte
March 24, 2003 - 7:19 am UTC

you know, I could have sworn I said:

alter session set sql_trace=true;
exec dbms_stats....

and then edit the trc file and look for the statement that failed -- that would
be very telling (not the tkprof report, the trc file)


Not that you should post it here - but rather look at the TRC file, find the statement that FAILED and at most post that statement.

Ignore the storage characteristics of SYSTEM as I said.
We don't know that the create statement wasn't captured - do we? Look through the trace file for any possible create statements. Look for 1658 and look backwards in the trc file for the possible cause.

Also -- you have filed a TAR with support right?




A reader, March 24, 2003 - 8:13 am UTC

Sorry for not showing the trace file, becuase I could not find any failure messages in. I thought it was the space issue of SYSTEM, and wanted to ask a couple of question shown above.
May I put some kind of trace levels order to capture the error related in the trace file?
Here is the trace file, posted it because of the relative small size of the file:

Dump file /d02/oracle/xxxx1db/8.1.7/admin/xxxx1/udump/xxxx1_ora_15933.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
ORACLE_HOME = /d02/oracle/xxxx1db/8.1.7
System name: SunOS
Node name: yyyy
Release: 5.8
Version: Generic_108528-16
Machine: sun4u
Instance name: xxxx1
Redo thread mounted by this instance: 1
Oracle process number: 28
Unix process pid: 15933, image: oracle@yyyy (TNS V1-V3)

*** 2003-03-23 17:41:20.544
*** SESSION ID:(43.52641) 2003-03-23 17:41:20.523
APPNAME mod='01@ sqltracetrue.sql' mh=2121163651 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=33 dep=0 uid=173 oct=42 lid=173 tim=1766712950 hv=3732290820 ad='838aef84'
alter session set sql_trace=true
END OF STMT
EXEC #1:c=0,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1766712950
=====================
PARSING IN CURSOR #1 len=45 dep=0 uid=173 oct=42 lid=173 tim=1766712963 hv=3937768788 ad='8bc4a96c'
alter session set max_dump_file_size=1000000
END OF STMT
PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1766712963
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1766712963
=====================
PARSING IN CURSOR #1 len=40 dep=0 uid=173 oct=42 lid=173 tim=1766712975 hv=3270856335 ad='812d8364'
alter session set timed_statistics=true
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1766712975
EXEC #1:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1766712976
=====================
PARSING IN CURSOR #1 len=53 dep=0 uid=173 oct=47 lid=173 tim=1766713000 hv=3759542639 ad='8dda30a0'
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
END OF STMT
PARSE #1:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1766713000
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
EXEC #1:c=1,e=12,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=1766713012
*** 2003-03-23 17:42:57.577
=====================
PARSING IN CURSOR #1 len=54 dep=0 uid=173 oct=47 lid=173 tim=1766722653 hv=1035406852 ad='801949d0'
BEGIN DBMS_STATS.GATHER_SCHEMA_STATS('ABM',10); END;
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1766722653
=====================
PARSING IN CURSOR #2 len=726 dep=1 uid=0 oct=3 lid=0 tim=1766722655 hv=3928717011 ad='8d611d3c'
SELECT /*+ ordered full(t) full(o) use_hash(o) full(u) use_hash(u) */'TABLE' OBJ_TYPE,O.NAME OBJ_NAME,U.NAME USER_NAME,O.OBJ# OBJ_NUM FROM SYS.TAB$ T,SYS.OBJ$ O,SYS.USER$ U WHERE BITAND(T.PROPERTY,1) != 1 AND BITAND(T.PROPERTY,512) != 512 AND BITAND(T.PROPERTY,4194304) != 4194304 AND BITAND(T.PROPERTY,8388608) != 8388608 AND BITAND(T.PROPERTY,8192) != 8192 AND T.OBJ# = O.OBJ# AND O.OWNER# = U.USER# AND O.LINKNAME IS NULL UNION ALL SELECT /*+ ordered use_nl(c) use_nl(o) use_nl(u) */'INDEX' OBJ_TYPE,O.NAME OBJ_NAME,U.NAME USER_NAME,O.OBJ# OBJ_NUM FROM SYS.IND$ I,SYS.CLU$ C,SYS.OBJ$ O,SYS.USER$ U WHERE I.BO# = C.OBJ# AND I.OBJ# = O.OBJ# AND O.OWNER# = U.USER# AND O.LINKNAME IS NULL ORDER BY 3,1 DESC,2
END OF STMT
PARSE #2:c=2,e=2,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1766722655
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1766722655
*** 2003-03-23 17:43:16.705
=====================
PARSING IN CURSOR #3 len=36 dep=2 uid=0 oct=3 lid=0 tim=1766724566 hv=1705880752 ad='8d746fe8'
select file# from file$ where ts#=:1
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=1766724566
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724566
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1766724566
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1766724566
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1766724566
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1766724566
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1766724566
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1766724566
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,tim=1766724566
FETCH #3:c=0,e=0,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724566
STAT #3 id=1 cnt=7 pid=0 pos=0 obj=17 op='TABLE ACCESS BY INDEX ROWID FILE$ '
STAT #3 id=2 cnt=8 pid=1 pos=1 obj=39 op='INDEX RANGE SCAN '
=====================
PARSING IN CURSOR #3 len=84 dep=2 uid=0 oct=3 lid=0 tim=1766724567 hv=904892542 ad='873ef0cc'
select file#,block#,length from fet$ where length>=:1 and ts#=:2 and file#=:3
END OF STMT
PARSE #3:c=1,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=1766724567
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724567
FETCH #3:c=0,e=1,p=1,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=56 dep=2 uid=0 oct=3 lid=0 tim=1766724568 hv=2714675196 ad='84a49634'
select file#, block# from fet$ where ts#=:1 and file#=:2
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=0,tim=1766724568
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=84 dep=2 uid=0 oct=3 lid=0 tim=1766724568 hv=904892542 ad='873ef0cc'
select file#,block#,length from fet$ where length>=:1 and ts#=:2 and file#=:3
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=56 dep=2 uid=0 oct=3 lid=0 tim=1766724568 hv=2714675196 ad='84a49634'
select file#, block# from fet$ where ts#=:1 and file#=:2
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=84 dep=2 uid=0 oct=3 lid=0 tim=1766724568 hv=904892542 ad='873ef0cc'
select file#,block#,length from fet$ where length>=:1 and ts#=:2 and file#=:3
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=56 dep=2 uid=0 oct=3 lid=0 tim=1766724568 hv=2714675196 ad='84a49634'
select file#, block# from fet$ where ts#=:1 and file#=:2
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=84 dep=2 uid=0 oct=3 lid=0 tim=1766724568 hv=904892542 ad='873ef0cc'
select file#,block#,length from fet$ where length>=:1 and ts#=:2 and file#=:3
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=56 dep=2 uid=0 oct=3 lid=0 tim=1766724568 hv=2714675196 ad='84a49634'
select file#, block# from fet$ where ts#=:1 and file#=:2
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724568
EXEC #3:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=84 dep=2 uid=0 oct=3 lid=0 tim=1766724569 hv=904892542 ad='873ef0cc'
select file#,block#,length from fet$ where length>=:1 and ts#=:2 and file#=:3
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=56 dep=2 uid=0 oct=3 lid=0 tim=1766724569 hv=2714675196 ad='84a49634'
select file#, block# from fet$ where ts#=:1 and file#=:2
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=84 dep=2 uid=0 oct=3 lid=0 tim=1766724569 hv=904892542 ad='873ef0cc'
select file#,block#,length from fet$ where length>=:1 and ts#=:2 and file#=:3
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=56 dep=2 uid=0 oct=3 lid=0 tim=1766724569 hv=2714675196 ad='84a49634'
select file#, block# from fet$ where ts#=:1 and file#=:2
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=84 dep=2 uid=0 oct=3 lid=0 tim=1766724569 hv=904892542 ad='873ef0cc'
select file#,block#,length from fet$ where length>=:1 and ts#=:2 and file#=:3
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
=====================
PARSING IN CURSOR #3 len=56 dep=2 uid=0 oct=3 lid=0 tim=1766724569 hv=2714675196 ad='84a49634'
select file#, block# from fet$ where ts#=:1 and file#=:2
END OF STMT
PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
EXEC #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
FETCH #3:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=1766724569
STAT #3 id=1 cnt=0 pid=0 pos=0 obj=12 op='TABLE ACCESS CLUSTER FET$ '
STAT #3 id=2 cnt=2 pid=1 pos=1 obj=7 op='INDEX UNIQUE SCAN '
FETCH #2:c=292,e=1913,p=10283,cr=10385,cu=52,mis=0,r=0,dep=1,og=4,tim=1766724569
EXEC #1:c=295,e=1916,p=10283,cr=10385,cu=52,mis=0,r=0,dep=0,og=4,tim=1766724569
ERROR #1:err=1658 tim=1766724569
=====================
PARSING IN CURSOR #1 len=52 dep=0 uid=173 oct=47 lid=173 tim=1766724621 hv=1697159799 ad='8639b790'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=0,tim=1766724621
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=1766724621
*** 2003-03-23 17:43:35.374
=====================
PARSING IN CURSOR #1 len=53 dep=0 uid=173 oct=47 lid=173 tim=1766726433 hv=3759542639 ad='8dda30a0'
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1766726433
APPNAME mod='01@ sqltracefalse.sql' mh=1221103161 act='' ah=4029777240
EXEC #1:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=1766726447
=====================
PARSING IN CURSOR #1 len=34 dep=0 uid=173 oct=42 lid=173 tim=1766726470 hv=1582735206 ad='82464318'
alter session set sql_trace=false
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1766726470
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1766726470

Thanks for your time

Tom Kyte
March 24, 2003 - 9:12 am UTC

well, it looks big from my screen ;)

anyway -- your users TEMP tablespace is set to system, the analyze begins to run, runs out of room and bombs trying to create a temp segment in system.

Alter user X temporary tablespace <SOMETHING ELSE>;


It is the initial allocation of a temporary segment that is failing here.




A reader, March 24, 2003 - 10:03 am UTC

Qoute:
anyway -- your users TEMP tablespace is set to system, the analyze begins to
run, runs out of room and bombs trying to create a temp segment in system.

Alter user X temporary tablespace <SOMETHING ELSE>;


It is the initial allocation of a temporary segment that is failing here.

********

How could you be sure?

apps@xxx> select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from user_users;

Mon Mar 24
Database Users

DEFAULT TEMPORARY
--------------- ------------
APPLSYSD TEMP

1 row selected.


Tom Kyte
March 24, 2003 - 10:16 am UTC

because of the lack of CREATES in there. tell you what -- if this is still happening, with the user set to TEMP, followup the rest of this with your TAR you must have filed by now with support. Nothing else I can do.




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.