Hi,

i am creating a employeeTmp in which i am using the order by clause...

SELECT p.*,a.action from audit_activity a, employee p where a.time_stamp >= to_date('11232009 00:00:00', '%m%d%Y %T') and a.table_name = 'employee' and trunc(a.pkey_value_1+0,0) = p.loc_id and a.pkey_name_1 = 'loc_id' order by p.loc_id into temp employeeTmp with no log;

and in other part of the code i will using this query.

what is happening is ..because of the order by clause it is consuming lot of DB temp space and it is failing.

can anyone pls let me know whether there will be any difference in the DB temp space usage when we use the order by clause and with out using order by clause( taking into acccount i do not need the ordering of the particular column).

When you use an ORDER BY, the database must re-sort all data and for this they create a new temporary table.
If you have problem with space on your DBSPACETEMP and don't wanna grow up them, try use the variables: PSORT_DBTEMP (to use a file system area) and PSORT_NPROCS (to speed up your sorts)
PSORT_DBTEMP
PSORT_NPROCS

I forgot...
If this temp table have a lot of requests, you can create indexes for it.