Re: Creating table in new tablespace

bluebody wrote:
>
> Steve Phelan <stevep_at_no-spam.pmcgettigan.demon.co.uk> wrote in article
> <33C4D41F.58D48B09_at_no-spam.pmcgettigan.demon.co.uk>...
> >
> > If it's a 'temp' segment - NOT a 'table' segment - look at the
> > 'temporary tablespace' setting of the user you are creating the table
> > under. Seems to me that that is where your problem lies, not in the
> > actual new table's tablespace. You may have to change or increase the
> > size of the temporary tablespace.
> >
> > Steve Phelan.
> >
> I have my user setting pointing to a large temp tablespace so I don't think
> that is the problem. I tried creating an empty table by defining all the
> columns manually and still get the same error.

I have the following suggestions:

1. Check the init.ora parameter sort_area_size. If the is less then 256000 then have the dba raise it. The default is 64K and on complex queries it helps to use more memory. NOTE: This parameter effects all connections to the database. You may also try 1M of sort area.
2. If you are selecting from another set of tables and running out of temp this indicates that you are not using any indexes, the indexes are very inefficient or you are disabling the indexes in the where clause. Try to do just the select without the create table part. If this fails it indicates that the database is joining the tables without using any indexes.
3. Use explain plan to see what the database is doing.

HERE IS SOME sample code that should help

set pagesize 50
set timing on
undefine PLAN_ID

delete from plan_table
where statement_id = '&&PLAN_ID'
/

explain plan set statement_id = '&PLAN_ID' into plan_table for SELECT < your sql goes here >
ename from emps
/

commit;

rem
rem Generate the plan
rem

column plan format a70

select lpad(' ',3*level)||operation||'('||options||')'|| object_name||' '||object_type
from plan_table
connect by prior id = parent_id and
statement_id = '&PLAN_ID'
start with id =1 and statement_id = '&PLAN_ID'

/ Received on Thu Jul 10 1997 - 00:00:00 CDT