| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Temporary Tables
A copy of this was sent to Neil Marshall <marshaln_at_shaw.wave.ca>
(if that email address didn't require changing)
On Sat, 29 Aug 1998 11:14:28 -0600, you wrote:
>I am assigned to create a Crystal report. It requires to retrieve data
>from Oracle 8 through stored procedures due to the complexity. What it
>is really necessary to process is create a few temporary tables. Oracle
>people mentioned me that Oracle has no ability of creating these
>particular ones -- all I have to do is create tables at the beginning
>and delete them at the end of procedures. Agree with them. No
>problem. My just concern is that what if many users are running the
>report at the same table. Will created tables be messed up? Or Will
>they be created "privately" in their own directory?
>Please clarify this for me. Thanks.
>
>
In Order to do "temporary" tables in Oracle, we will use a real table. This implementation will have these properties:
· you will only see rows in the temp table that you put in there.
· operations will be non-blocking on temporary tables -- just because someone
else is writing to the same 'real' table in Oracle does not mean any blocking on
inserts/reads will take place
· if you log out/log back in, the temporary table will appear empty
· they will always be in the database -- from the time you logon till you logout
the temporary table will exist.
Caveats
· You will always want at least one index on your temporary table -- an index on
the column we use to identify 'your' rows. If you want, you may add OTHER
indexes but they should all include the column x$id as the first column in the
index. If you add OTHER indexes, you may safely drop the index we created on
just the x$id column as it is redundant at that point. If you create any UNIQUE
indexes it is *very* important that you remember to add the x$id column as the
first column of the index to avoid some blocking issues.
· We need to periodically clean up the temporary tables as space allocated to
them (rows inserted) will not be deleted when you log out -- just made not
visible. We will use dbms_jobs to automate this process in the background so
you do not have to constantly monitor these tables. An example is provided.
Example based on a table named TDO that will have a temporary table TEMP_TDO:
SQL> REM Creating a dummy TDO table with some columns and data. SQL> REM you will of course use your own TDO table. SQL> create table tdo as select * from all_users where rownum < 101;
Table created.
SQL> REM We create a permament table that is structurally like our temporary SQL> REM table. We will add to it one additional column, x$id SQL> REM this column is used to ID the rows owned by our session during the life SQL> REM of our connection. SQL> SQL> create table temp_tdo_TBL
Table created.
SQL>
SQL> alter table temp_tdo_tbl modify x$id default userenv('sessionid');
Table altered.
SQL>
SQL> create index x$id_idx on temp_tdo_tbl(x$id);
Index created.
SQL>
SQL> create view temp_tdo as
2 select username, user_id, created
3 from temp_tdo_tbl
4 where x$id = userenv('sessionid');
View created.
SQL> SQL> SQL> SQL> select * from temp_tdo;
no rows selected
SQL> insert into temp_tdo select * from all_users where username like '%KYT%';
2 rows created.
SQL> select * from temp_tdo;
USERNAME USER_ID CREATED ------------------------------ ---------- --------- WEB$TKYTE 894 01-SEP-97 TKYTE 4036 08-MAY-98
SQL>
SQL> disconnect
Disconnected from Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
SQL> connect tkyte/tkyte
Connected.
SQL> select * from temp_tdo;
no rows selected
As you can see, the temporary table acts as we expect it to act -- it is initially empty, we put rows into it and can see those rows, when we log out and log back in -- it appears empty. The same characteristics will be visible in a multi-user environment. That is, if you have 2 users logged in at the same time, neither user will ever be able to see the other users data.
In order to automatically clean out the temp table, we will use pl/sql and the job queues in Oracle. The job queues are like 'cron' in Unix and are used to schedule recurring jobs. Please see the Application developers guide for more information on dbms_job. In short, make sure the init.ora parameters
· job_queue_interval integer 60 · job_queue_keep_connections boolean FALSE · job_queue_processes integer 1
are set. My settings are above. These settings cause 1 job thread to be started with the database (job queue processes) and the job queues will be inspected every 60 seconds (job queue interval). Connections to the database will not be maintained by the job queue process (job queue keep connections). The database must be restarted after changing these parameters for them to take affect.
Once job queues are configured, you may code something like the following:
REM procedure to 'clean' the temp tbl every now and again. REM it is optimized to recognize that no sessions logged in REM have data in temp_tdo and will use truncate in that case REM otherwise it will use a convential delete.
create or replace procedure clean_a_temp_table( p_tname in varchar2 ) as
l_cursor integer default dbms_sql.open_cursor;
l_status number;
l_cnt number;
dbms_sql.parse( l_cursor,
'select count(*)
from v$session
where exists ( select null
from ' || p_tname || '
where '||p_tname||'.x$id = v$session.audsid )',
dbms_sql.native );
dbms_sql.define_column( l_cursor, 1, l_cnt ); l_status := dbms_sql.execute_and_fetch( l_cursor ); dbms_sql.column_value( l_cursor, 1, l_cnt );
if ( l_cnt = 0 ) then
dbms_sql.parse( l_cursor,
'truncate table ' || p_tname,
dbms_sql.native );
else
dbms_sql.parse( l_cursor,
'delete from ' || p_tname || '
where not exists ( select null
from v$session v
where v.audsid = '||p_tname||'.x$id )',
dbms_sql.native );
end if;
l_status := dbms_sql.execute( l_cursor );
dbms_sql.close_cursor( l_cursor );
commit;
end;
/
set serveroutput on
declare
l_jobno number;
begin
dbms_job.submit( job => l_jobno,
what => 'clean_a_temp_table( ''temp_tdo_tbl'' );',
interval => '(trunc(sysdate,''hh'')+1/24)' );
commit;
dbms_output.put_line( 'Job ' || l_jobno || ' submitted to run every hour');
end;
/
The procedure, clean_a_temp_table, is parameterized to take any table name and either TRUNCATE it or using a selective DELETE, get rid of the rows no longer needed. So, you need install the clean_a_temp_table only once in a schema and then use it for any and all temporary tables in that schema.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Aug 29 1998 - 12:54:15 CDT
 |
 |