Re: Extremely Large Select Statements (HELP!!)

In article <1992May03.011205.21542_at_ecst.csuchico.edu> morganm_at_ecst.csuchico.edu (Mike Morgan) writes:
>
>I'm executing a rather large SELECT statement in Pro*C that could return
>between 85,000 and 750,000 records. The trouble I run into is I keep getting
>ORA-01547: Failed to allocate extent of size 912 in Tablespace 'SYSTEM'.
>I have expanded the tablespace size to 12M and can't extend it any further due
>to disk space limitiations. Is there an easy way I can break this up into
>several smaller SELECT's?
>
>Thanks for any information you can offer,
>
>Mike
>
>--
>Mike Morgan
>morganm_at_cscihp.ecst.csuchico.edu

Mike,

Another solution if you have disk space available (and it appears that you may not) is to allocate a tablespace purely for temporary table generation and storage. And another solution, if you know that you have room for it in one of your data tablespaces is to make sure that =that= tablespace is used as the temporary tablespace.

In our site, we have several tablespaces (list included below). Each user as part of their creation script also makes sure that any and all temporary space goes to the TEMP tablespace. This was sufficient except during index creation of the 340 megabyte Zip (street level) table... and I had to alter the user to use the large 500MB main data tablespace (#8) .

SQL> Alter user INDEX_OWNER temporary tablespace HOMEBASE;

In general to avoid database fragmentation in the SYSTEM tablespace, you should establish SOMEwhere as the temporary tablespace.

-Andy

• oracle status listing

Tablespace File % Free Total Bytes Free Space Largest Block Free Segs --------------- ----- -------- ----------- ---------- ------------- ----------

HOME2		    6	88.26%	  31457280   27764736	   25470976	   225
HOMEBASE	    7	  .69%	 272629760    1873920	    1640448	     2
HOMEBASE	    8	45.10%	 536870912  242124800	  152522752	    31
HOMESELLERS	    5	44.77%	  78643200   35205120	   35194880	     2
ROLLBACK	    3	57.22%	  52428800   30001152	   11843584	    24
SYSTEM		    1	62.18%	  31457280   19560448	    9535488	    20
TEMP		    4  100.00%	  62914560   62912512	   16570368	    21
ZIP		    2	15.36%	 402653184   61863936	   61863936	     1

-- 
Andrew Finkenstadt         +1 904 222-ANDY home    GEnie:  ANDY
Homes & Land Publishing    +1 904 575-0189 work    ...!uunet!rde!andy
Vista-Chrome, Inc.         1600 Capital Cir SW     andy_at_vistachrome.com
GEnie Unix Sysop/Manager   Tallahassee, FL 32310   ------------------------
--
...and a UNIX user said 'rm -fr *' ...and all was without form and void...