Is "global temporary table" a better solution to avoid "truncate table"?

An application is inserting and truncating from a permanent table (nologging). Since truncate is a DDL operation, can it be replaced with GTT? How does oracle delete/truncate rows from GTT when it is done. Is it equal to a normal TRUNCATE?

Hi,

What if I use a gtt to store complex search results?
In my system there can be more than one search in one transaction so there would be more than one search result sets in this user's gtt.

So in this case would you delete (truncate is out of question) the old result set before generating a new one? Or would you let the commit clean the old result set and instead add e.g. a search identifier column to distinguish stale and current result sets (perhaps a need for an index on that column)? In my case the result sets rarely exceed 100. And there are rarely than few searches in a transaction (nevertheless they do and can happen).

Since 8i does not recognize "order SIBLINGS by" in a query with a connect by, I created a type...object and type... table of and a function to recursively load a menu/submenu/application tree.
The data is returned as a result set using select * from TABLE ( cast ( data as defined_type ) ).

procedure RS_OPTIONS
(iMenuID in varchar2
,oResultSet out cursor_type) is
pCursor cursor_type;
begin
LOAD_TREE(iMenuID);
open pCursor for
select *
from TABLE ( cast ( gMenuData as menu_table_type ) );
oResultSet := pCursor;
end RS_OPTIONS;


The data could have just as well been stored as a temporary table.
Would a temporary table be better than the "table of type" approach?

It's easy to overlook the obvious answer.
Thanks!

There must be a way to determine if a temporary table is of the type that will preserve or delete rows from information in the data dictionary.

Where do I find it?

dba_tables only specifies TEMPORARY = Y/N...
dbms_metadata will return the DDL showing preserve or commit...

Thanks for your help.

Answer was staring me in the face.
Sorry for bothering you.

Dear Tom

Is there any HWM problem in GTT(with on commit
preserve).

For example, on daily basis if I do the following activity

pseudo code
===========
for i in( 1..365)
loop
sqlplus user/password

declare
begin
INSERT /*+ APPEND */ INTO GTT_ON_COMMIT_PRESERVE select * from heap_tab;
commit;
select ...from GTT_ON_COMMIT_PRESERVE;
end;
exit;
end loop;

OR
do I have to truncate GTT_ON_COMMIT_PRESERVE to avoid HWM?

Tom,

1. Global Temporary tables (a.k.a temporary tables) do not generate "true redo". What I mean by "true redo" is the "redo" associated with "roll forward and media recovery".
2. They only generate "redo" which is used to protect the "undo" (for rollback, rollback to savepoint and read consistency).

Is that correct? Pls. clarify

Hi Tom,

I have the following clarifications for which I need your help, possibly with an example

1. "they need not generate redo for their segment - but for the undo they generate.".
My understanding so far has been that if I perform some inserts/deletes/updates (DML) into a GTT and the disk goes bad. In that case
it will not be possible to apply the online and archive redo logs to bring the data to current point in time? If it is not correct, what does the above statement mean
and please clarify me with an example.

2. I remember reading in your book on "Redo and Rollback" that GTTs will not generate REDO logs and so transactions on GTTs will not be
recoverable.

In the same chapter, you mention that Oracle uses the online and archived REDO logs for the purpose of "roll forward and media recovery"
and the UNDO is used only to "ROLLBACK" or provide read-consistency mechanism and NOT for rolling forward.
If that is the case and since the "redo" generated for GTTs is to protect the "undo", how can this generated redo
be used during roll forward and media recovery as mentioned by you in your previous reply :

"......the redo they do generate would be used during roll forward and media recovery.".

Does it mean that the UNDO mechanism working differently for GTTs as compared to normal tables. If not, can you please clarify and help me understand
what the statement means.


Thanks much

Hello,Tom,
i have temporary table "ON COMMIT PRESERVE ROWS"
In the begining of procedure that uses this table i do:
EXECUTE IMMEDIATE
'TRUNCATE TABLE KM_TMP_PREDICT_CUSTOMERS REUSE STORAGE';
AND IT DOESN'T WORK,
but
EXECUTE IMMEDIATE
'TRUNCATE TABLE KM_TMP_PREDICT_CUSTOMERS' WORKED;
What is the reason for that?
Thnk you.
Mariana

not that REUSE storage is really "meaningful" for global temporary tables (they do not have their own storage, they are in TEMP) but

ops$tkyte%ORA9IR2> create global temporary table gtt ( x int ) on commit preserve rows;
 
Table created.
 
ops$tkyte%ORA9IR2> begin
  2  execute immediate 'truncate table gtt reuse storage';
  3  end;
  4  /
 
PL/SQL procedure successfully completed.


"AND IT DOESN'T WORK" - does not mean anything to me.

Not any more than "my car won't start - why not" would mean to you. 

What i meabt in 'doesn't work'
is when i do in temporary table: truncate table reuse storage and then select to this table i see records
but when i don't use "reuse storage" i see that the table is empty.
What is the reason for this?
Thank you,
Mariana

The reason that i did truncate to temp table is because of strange behaviour if this table:
i have a procedure that activated from jobs each night automatically, in this procedure i insert records into temporary table and this table has a unique key,this table is defined "preserve rows on commit" ,therefore i expect that after each automatic activation of the proc the table will be empty, but sometimes i saw that the insert in proc fail on "ORA-00001: unique constraint (KM.IND1) violated"
and it seems to be like the table wasn't empty when the proc was ativated again.
My question is if is it possible that records in temporary table are not deleted after session is closed?

Hello Tom,
What could be a reason where a fresh session is seeing rows in a temporary table
without having put them there itself ?
Thank you,
Mariana










something entirely
different and would not involve truncate at all.

Hi Tom,

I just tried to know what is temporary table and how that is used. I have gone thru all the questions and answers that I can see in this page. However, I could not understand the value of this topic. I tried with "Temporary table" topic that you have written in the expert on-on-one Oracle. Sorry to say; I really struggle to get the exact info given in that chapter.

What I got to know
------------------

Two diff temporary tables are there which differ in the behaviour of preserving the data in them.
The space is allocated in differnt segment.

What I like to know
-------------------
What is the need of having temporary table?
If the usage of temporary tables improve performance, how do they? and when do they?
When should we use them?


I really like the way that you have taken to write this book. However, this particular topic if far away from me. Can you pls take this closer to me.

Thanks in advance.

Regards,
Maria Antony Samy

Hi Tom,

How about this as a "good" reason for a GTT? The requirement is to return selected rows from table MAIN and additional data from several other tables. The WHERE clause for the other tables is always IN (list of IDs returned from the MAIN query).

Of course I would prefer just one SQL statement, but using joins would send lots of redundant data over the network and UNION ALL is out because the columns aren't the same.

My idea is to store the IDs I need in a GTT ON COMMIT DELETE, then use the GTT in the IN list of the subsequent queries.

One problem is how to get read consistency across these queries. I can't use a read only transaction because it wouldn't let me insert into the GTT, so I'm left with a serializable transaction or SELECT FOR UPDATE on the MAIN table (changes to the other tables don't hurt).

1. Is there a way that avoids the GTT?

2. Which is preferable, serializable or SELECT FOR UPDATE?

As always, many thanks for this site and your blog.

1) "The columns aren't the same." I need output from tables with different structures, varying from 5 to 30 columns. To get it all in one SQL statement wouldn't I have to define ONE result set with all possible columns, then use UNION ALL?

2) Touché! Back to the Concepts book. I NEED to avoid phantom reads, so "for update" is not an option.

2.5) Since only the GTT is modified, there should be no ORA-08177 errors, right?

Sorry for not being clear, and thanks for your patience.
OK, stripped down illustration:

Drop table T1;
Create table T1 ("ID" NUMBER(10,0));
insert into T1 VALUES(1);
insert into T1 VALUES(2);

Drop table T2;
Create table T2 ("ID" NUMBER(10,0),
"NUMBER1" NUMBER(10,0),
"NUMBER2" NUMBER(10,0));
insert into T2 VALUES(1, 11, 111);
insert into T2 VALUES(1, 1111, 11111);
insert into T2 VALUES(2, 22, 222);
insert into T2 VALUES(2, 2222, 22222);

Drop table T3;
Create table T3 ("ID" NUMBER(10,0),
"DATE1" DATE,
"DATE2" DATE);
alter session set NLS_DATE_FORMAT='DD/MM/YYYY';
insert into T3 VALUES(1, '01/01/2006', '01/12/2006');
insert into T3 VALUES(2, '02/02/2006', '02/12/2006');

Drop table T4;
Create table T4 ("ID" NUMBER(10,0),
"STRING1" VARCHAR2(20),
"STRING2" VARCHAR2(20));
insert into T4 VALUES(1, 'STRING1A', 'STRING1B');
insert into T4 VALUES(1, 'STRING1X', 'STRING1Y');
insert into T4 VALUES(2, 'STRING2A', 'STRING2B');
insert into T4 VALUES(2, 'STRING2X', 'STRING2Y');

-- GOAL: get list of IDs from T1 based on varying selection criteria,
-- then get data belonging to those IDs from T2, T3 and T4.
-- If possible, manufacture list once only for performance.
-- Read consistency required.
-- NOTE: output goes to a program, not a screen, so no need to be "pretty".

-- FIRST TRY: one request per table

with in_list as (select ID from T1 where ID > 1 and 'varying' > 'criteria')
select * from T2 where ID in (select * from in_list);
with in_list as (select ID from T1 where ID > 1 and 'varying' > 'criteria')
select * from T3 where ID in (select * from in_list);
with in_list as (select ID from T1 where ID > 1 and 'varying' > 'criteria')
select * from T4 where ID in (select * from in_list);

-- Result:
-- ID NUMBER1 NUMBER2
-- ---------------------- ---------------------- -----------------
-- 2 22 222
-- 2 2222 22222
--

-- ID DATE1 DATE2
-- ---------------------- ------------------------- --------------
-- 2 02/02/2006 02/12/2006
--

-- ID STRING1 STRING2
-- ---------------------- -------------------- --------------------
-- 2 STRING2A STRING2B
-- 2 STRING2X STRING2Y

-- GOOD/BAD/UGLY: "with" executed more than once; no read consistency.

-- SECOND TRY: one "with"

with in_list as (select ID from T1 where ID > 1 and 'varying' > 'criteria')
select * from T2 where ID in (select * from in_list)
select * from T3 where ID in (select * from in_list)
select * from T4 where ID in (select * from in_list);

-- Result:
-- SQL Error: ORA-00933
-- GOOD/BAD/UGLY: doesn't work

-- THIRD TRY: one "with", UNION ALL

with in_list as (select ID from T1 where ID > 1 and 'varying' > 'criteria')
select * from T2 where ID in (select * from in_list)
UNION ALL
select * from T3 where ID in (select * from in_list)
UNION ALL
select * from T4 where ID in (select * from in_list);

-- Result:
-- SQL Error: ORA-01790
-- GOOD/BAD/UGLY: doesn't work

-- FOURTH TRY: one "with", UNION ALL, "wide" result set

with in_list as (select ID from T1 where ID > 1 and 'varying' > 'criteria')
select NUMBER1, NUMBER2, to_date(NULL) as DATE1, to_date(NULL) as DATE2,
NULL as STRING1, NULL as STRING2
from T2 where ID in (select * from in_list)
UNION ALL
select NULL, NULL, DATE1, DATE2, NULL, NULL
from T3 where ID in (select * from in_list)
UNION ALL
select NULL, NULL, NULL, NULL, STRING1, STRING2
from T4 where ID in (select * from in_list);

-- Result:

-- NUMBER1 NUMBER2 DATE1 DATE2 STRING1 STRING2
-- ---------- ---------- -------- -------- ------------- ------------
-- 22 222
-- 2222 22222
-- 02/02/06 02/12/06
-- STRING2A STRING2B
-- STRING2X STRING2Y

-- GOOD/BAD/UGLY: achieves goals, but "ugly",
-- i.e. hard to document / maintain given 100 columns in "real life".

-- FIFTH TRY: GTT and serializable transaction

drop table in_list;
create global temporary table in_list ("ID" NUMBER(10,0))
ON COMMIT DELETE ROWS;
set transaction isolation level serializable;
insert into in_list
(select ID from T1 where ID > 1 and 'varying' > 'criteria');
select * from T2 where ID in (select * from in_list);
select * from T3 where ID in (select * from in_list);
select * from T4 where ID in (select * from in_list);
commit;

Drop table T1;
Drop table T2;
Drop table T3;
Drop table T4;
drop table in_list;

-- Result:

-- ID NUMBER1 NUMBER2
-- ---------------------- ---------------------- ----------------
-- 2 22 222
-- 2 2222 22222
--
-- ID DATE1 DATE2
-- ---------------------- ------------------------- -------------
-- 2 02/02/2006 02/12/2006
--
-- ID STRING1 STRING2
-- ---------------------- -------------------- ------------------
-- 2 STRING2A STRING2B
-- 2 STRING2X STRING2Y

-- GOOD/BAD/UGLY: achieves goals with simpler SQL and more straightforward
-- result sets, but ORA-08177 errors must be anticipated.

Am I missing a simpler, more elegant approach? If not I will drop the GTT and go with the "wide" result set.

Thanks again !

why not cast all columns to a character string.

ops$tkyte%ORA10GR2> with in_list as (select ID from T1 where ID > 1 and 'varying' > 'criteria')
  2  select 'T2', id, to_char(number1) v1, to_char(number2) v2
  3    from T2 where ID in (select * from in_list)
  4  UNION ALL
  5  select 'T3', id, to_char(date1) v1, to_char(date2) v2
  6    from T3 where ID in (select * from in_list)
  7  UNION ALL
  8  select 'T4', id, string1, string2
  9    from T4 where ID in (select * from in_list);

'T         ID V1              V2
-- ---------- --------------- ---------------
T2          2 2222            22222
T2          2 22              222
T3          2 02/02/2006      02/12/2006
T4          2 STRING2X        STRING2Y
T4          2 STRING2A        STRING2B
 

Well, in "real life" there are 8 tables involved with from 6 to 47 columns. There would be column mapping and data conversions in Oracle, then mapping back and converting back in the calling program.

There are about 80 distinct column names, so keeping the original names and formats about doubles the "width" of the result set. By placing at the end the columns most likely to be null, I believe the actual increase in size of the result set will not be great.

I think the maintenance developers of the calling program would far prefer getting the data in the same format from the same column name that they use today. It's a small price to pay to stop them from making one query PER ROW as they are doing today !!!

Just for information, "single user" tests get practically identical response times from the two solutions we have been discussing.

So, my gtt was even more temporary than I thought !

"Well, I'm back."

Just in case anyone comes across my "reviews" on this topic and has a similar requirement, forget the "wide result set" approach: too unweildy.

As a reminder, I need to query several tables with different structures using an IN list obtained from a previous query.  I finally thought of keeping the list in PL/SQL memory rather than a GTT.  This lets me query each table separately and simply. I see this solution everywhere now that I know to look for it :) 
For a consistent read, the calling program can use a READ ONLY transaction, which is safe from ORA-08177 errors.

Base on my stripped down illustration above:

create or replace type IDS as table of number(10,0);
/
create or replace procedure getmany(
p_cursor_T2 in out SYS_REFCURSOR,
p_cursor_T3 in out SYS_REFCURSOR,
p_cursor_T4 in out SYS_REFCURSOR)
as
id_list IDS;
begin
open p_cursor_T2 for select ID from T1 where ID > 1 and 'varying' > 'criteria';
fetch p_cursor_T2 bulk collect into id_list;
open p_cursor_T2 for select * from T2 where ID in (select * from TABLE(CAST(id_list as IDS)));
open p_cursor_T3 for select * from T3 where ID in (select * from TABLE(CAST(id_list as IDS)));
open p_cursor_T4 for select * from T4 where ID in (select * from TABLE(CAST(id_list as IDS)));
end;
/

SQL> variable a refcursor
SQL> variable b refcursor
SQL> variable c refcursor
SQL> set transaction read only;
SQL> exec getmany(:a, :b, :c)
SQL> print a

        ID    NUMBER1    NUMBER2
---------- ---------- ----------
         2       2222      22222
         2         22        222

SQL> print b

        ID DATE1    DATE2
---------- -------- --------
         2 02/02/06 02/12/06

SQL> print c

        ID STRING1              STRING2
---------- -------------------- --------------------
         2 STRING2X             STRING2Y
         2 STRING2A             STRING2B

SQL> commit; 

Tom, just a word of thanks: your suggestion "why not cast all columns to a character string" wound up being the fastest of 4 solutions I tried. Views made the actual query pretty straightforward.

I now get data from up to 13 tables in one query, taking full advantage of array fetching even when some tables contain few rows.

I have also learned a LOT :)

Tom, I have been reading Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions . What it says about ORA-08177 seems different from what I thought I understood above. Two questions for clarification:

1) Suppose within a serializable transaction I do only SELECTs: could I ever get ORA-08177 ?

2) Suppose within a serializable transaction I do only SELECTs on permanent tables, but I do some INSERTs on a GTT: could I ever get ORA-08177 ?

Thanks in advance...

At my shop I have put together a system where we take nightly snapshots of demographic data of our associates. The snapshots provide a source of data for most systems requiring such data, which prevents hits against the real tables which is considerably more costly.

We have built stored procedures where we received a series of parameter inputs and dynamically build a query with bind variables and return a refcursor of data to the requestor.

Due to a recent project requirement, I needed a method to permit more complex searches. What I didn't want to is create another version that had even more input parameters. Instead I have chosen to accept a refcursor which is a criteria grid if you will. The criteria grid is then bounced against a table of values and conditions, which build a custom where clause. Essentially :

BLOCKS
|__SETS
|___MEMBERS

Where :

1. Blocks are always separated by AND
2. Blocks contain one or more SETS
3. Sets are always separated by OR
4. Sets contain one more more members
5. Members are either separated by OR or AND, but can only be of one operator in any given Set.

All of the conditions end up being selects against a GTT where the GTT is structered with parameter names, values, operator, inclusion/exclusion flag, block_id,set_id, and member_id. The GTT is where we store the criteria grid that we initially received from the requestor.

The rules of how the condition is stated comes from another table where I maintain the business rules pertaining to each type of parameter selection that may be desired (and found in the passed criteria grid).

I then generate the necessary query dynamically and pass the results back to the requestor... very little, if any at all, bind variables are used... mostly GTT.

There is more to it as well, such some optional pagination features and dynamic selection of columns to be returned, but its the use of the GTT that I am interested in if its a solid approach.

So far I have been able to generate some fairly complex conditional queries on the fly... and it has performed very well.. even better than I expected....

So thoughts ? I would be happy to post the code somewhere for someone to read through if desired.

Thank you in advance.

Hi Barry,

I would like very much to read your code on your earlier post on the use of GTT to capture filter conditions to retrieve data.

I will put together a document/whitepaper and post something soon regarding dynamic searching of data.

Since there was a request on what I put together regarding dynamic search conditions and use of global temp tables.

http://www.myoracleportal.com/Downloads/details/id=31.html

This topic has cleared many GTT related questions. I like all the responses.

Tom, is there a query which can be use to monitor the size of GTT while program is running?

I mean outside the program from different session.

Dear Tom,
Is it worthful to create an index for a temporary table?
why?

thank a lot and best wishes!

¿

I have a procedure which goes like this in Oracle 10 g

1)insert into GTT A
2)insert into GTT B
3)use cursor to return the output of (join between A and B)
commit;

GTT A and GTT B are created with ON COMMIT DELETE ROWS;
But i notice that the 3rd step always returns no rows. The GTT tables are automatically truncated by the time the 3rd step is executed. I have no commits until after the 3rd step.
Can you point whats wrong? Thanks!!

ops$tkyte%ORA11GR1> create global temporary table t2 ( x int ) on commit delete rows;

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> variable x refcursor
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create or replace procedure p( p_x out sys_refcursor, p_commit in boolean default false )
  2  as
  3  begin
  4          insert into t1 values ( 1 );
  5          insert into t2 values ( 1 );
  6          open p_x for select * from t1, t2 where t1.x = t2.x;
  7          if (p_commit) then commit; end if;
  8  end;
  9  /

Procedure created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec p(:x);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> print x;

         X          X
---------- ----------
         1          1

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec p(:x,true)

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> print x
ERROR:
ORA-01410: invalid ROWID



no rows selected

ops$tkyte%ORA10GR2> exec p(:x);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x;

         X          X
---------- ----------
         1          1

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec p(:x,true)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x
ERROR:
ORA-08103: object no longer exists



no rows selected

Hi Tom,

An application creates temporary tables on runtime. These tables are being used by several sessions (> 100) and when finished they are dropped. From time to time some GTT's remain raising "ORA-14452: attempt to create, alter or drop an index on temporary table already
in use". Ok, a session did not complete and exit when the drop attempt was made. After 5 days the table is still in use and cannot be dropped.

Is there a way to query the database for which session is holding a seqment in a speciffic temporary table?

Thanks & regards
Måns

ops$tkyte%ORA11GR2> select sid from v$lock where id1 = (select object_id from
  2  dba_objects where owner = 'OPS$TKYTE' and object_name = 'GTT' );

       SID
----------
        68
        68
        68

that my previous question look very stupid since this is a totally wrong way of using temporary tables in Oracle.
This is how things work in a business application and there is nothing I can do about it... just put out the fire when it starts.

Regards
Måns

Dear colleagues,

In our application we have a global temporary table created with "on commit delete rows" option. Several parallel (2-30) sessions can insert into that table and commit independently. At the end of each transaction, just before commit, there is a step as a PL/SQL procedure that iterates through all rows in the temporary table using a cursor.

The problem:
Sometimes there are long running transactions. They insert "much" (some hundreds) rows in the temp table. In some of these transactions the PL/SQL step fails with
"ORA-08103 : Object no longer exist".

Environment:
- Oracle 10.2.0.4 /64bit
- access it through JDBC connections with default (READ COMMITTED) isolation level

1. Is it possible a transaction that just committed, to affect the rows the failed transaction was iterating through?
Could this be related to number of segments allocated for the temp table in the failed transaction?

2. Is it possible the transaction isolation level (READ COMMITTED) to be the reason?

Please comment how to resolve this problem
Thanks in advance
Rusi

Great! I will check again thoroughly what is called in the cursor's loop. There must be something hidden...
Thank you!
Rusi

Hi Tom,

There is procedure in which GTT table is used.First the GTT table is populate by using permanent table query.Then it use merge of permanent and GTT table.This run in 54 parallel in batch process.My question is whether if i create a index on GTT will it give me performance benefit.Also should i use dynamic sampling hint in merge query of GTT and permanent table. As GTT table stats not gather.And i have read on some site that GTT doesnt get correct cardinality on this merge of GTT and permanent table.

Please suggest.

Thanks alot Tom....A great learning.

Taking a step back... are you sure a GTT is a good idea here? If you are using the results (presumably very large, given the parallel 54 in the following step) in a successor query, it might be better to have just a single query.

If you are using that result set multiple times, then the GTT may be helpful. But if it is just once then you may be introducing complexities that are detrimental.

Thanks alot tom...Yes Mike we do use result sets after insert into GTT table.Done some bench marking and its worth using GTT here.

Tom,

We run the below adhoc sql in our pre-prod environment where we get this error ORA-08103.
But I don't see none of the objects in the From clause of this select statement got dropped or truncated (during this query execution) and tables referred in this query are Heap organized tables and not GTT.

Can you help us to understand the reason for this error ORA-08103 during this statement execution? (we are on 10.2.0.5)

app@ORA10G> create table t4
  2  nologging parallel 4 as
  3   select /*+ parallel(e,4) parallel(dc,4) parallel(ed,4) */ b.audit_filenm
  4   , e.audit_key
  5   , e.audit_nbr
  6   , e.audit_min_dt
  7   , e.audit_max_dt
  8   , e.audit_hic_nbr
  9  , e.auditor_last_nm
 10  , e.auditor_first_nm
 11  , ed.audit_proc_cd_list as cpt_cd
 12  , ed.audit_rev_cd_list as rev_cd
 13  , e.audit_dx_cd
 14  , cast(null as varchar2(30)) as audit_status_cd
 15  , 'executed' as audit_state_cd
 16  , 'executed by end' as audit_status_cd
 17  , cast(null as varchar2(30)) as error_cd
 18  , cast(null as varchar2(30)) as d_error
 19  , 'duplicate' as error_status
 20  , ed.create_dt as creation_date
 21  , ed.source_cd
 22  , ed.audit_source_cd
 23  from audit_info b
 24  inner join asm_duplicates e on e.file_key = b.file_key
 25  inner join audit_dx dc on  e.audit_key = dc.audit_key
 26  inner join audit_dups ed on e.audit_key = ed.audit_key
 27  where  ed.sub_source_cd in ('DIRECT', 'DIRECTHDC') AND
 28  e.audit_max_dt BETWEEN to_date('01/01/2011','mm/dd/yyyy')
 29  and to_date('12/31/2011','mm/dd/yyyy')
 30  /
create table t4
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P002, instance dbsl0050:irdpr012 (2)
ORA-08103: object no longer exists


Elapsed: 00:11:27.89
app@ORA10G>

Hi Tom,

We are getting ORA-08103 error from our production environment. This is thrown from a procedure which used a Merge statement. We have replaced the Merge with Select and Update statements. The modified procedure ran for a few days but it again threw the same error at the point where records are fetched from the cursor. We have tried the below
1) Analyze table validate structure
2) Checking the alert log
but couldn't find any problem.

Also one of the permanent tables used in the query got truncated after this error( our client has confirmed that there is no process that truncates this table). We are not able to reproduce this issue in any other environment.

Will dropping and recreating tables involved (in the query) likely to solve this issue

Thank you

Team,

could you help us to understand, that why can't we drop the "Session" specific temp table - within the same session?

but this doesn't happen with Transaction specific temp tables.

demo@ORA12C> select userenv('sid') from dual;

USERENV('SID')
--------------
           138

demo@ORA12C> create global temporary table t1(x int)
  2  on commit preserve rows;

Table created.

demo@ORA12C> insert into t1(x) select object_id from all_objects;

61519 rows created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> select session_id
  2  from v$locked_object
  3  where object_id = ( select object_id
  4    from user_objects
  5    where object_name ='T1' );

no rows selected

demo@ORA12C> drop table t1 purge;
drop table t1 purge
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use


demo@ORA12C> conn demo@ora12c
Enter password:
Connected.
demo@ORA12C> drop table t1 purge;

Table dropped.

demo@ORA12C>

SQL> create global temporary table t1(x int) on commit preserve rows;

Table created.

SQL> insert into t1(x) select object_id from all_objects;

73531 rows created.

SQL> commit;

Commit complete.

SQL> drop table t1 purge;
drop table t1 purge
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use


SQL> truncate table t1;

Table truncated.

SQL> drop table t1 purge;

Table dropped.

Hi Tom,

I have created a GTT with preserve rows on commit.

I am inserting few records by fetching from a table over the DB link.

If I commit or gather the stats on GTT, I am loosing all the data just inserted.

Is there any bug that is deleting the data on COMMIT, if I pull the data over DB link?

For now to proceed with next steps, I have commented out Commit and gather stats steps after inserting data into GTT and it is working fine now.

how many days until 22,222 2/22 2:02