SQL & PL/SQL :: Global Temp Table
Sep 2, 2010what are minimum privilege required to create GTT (Global Temp Table)?
View 7 Replies
ADVERTISEMENT
Cursor Versus Global Temp Table
Jan 16, 2013We had an issue with a PL/SQL package taking hours to run as a concurrent program. Database version is 10.2.0.4.0, running on Linux x86 64-bit. A tkprof'd trace file revealed the problem SQL statement to be a cursor. This one SQL statement would run for 3+ hours. I copied the SQL statement and ran it in TOAD and it completed in seconds, returning the exact same result set. To resolve the issue in the PL/SQL package I created a global temp table and ran the exact same SQL statement as an INSERT into the global temp table.
Again, instead of hours, the SQL statement completes in seconds. If I revert the change, it goes back to taking hours. I've attached the relevant sections of the tkprof showing the two SQL statements (identical other than the insert in front of one) and the resulting explain plans and performance data. I've always been under the impression that a cursor was a better option than a temp table and I've never run into a situation where the same SQL statement runs so much longer when executed as a cursor.
Attached File(s)
SQL_As_Cursor.jpg ( 274.02K )
Number of downloads: 7
Explain_for_SQL_As_Cursor.jpg ( 189.43K )
Number of downloads: 4
SQL_as_Insert.jpg ( 277.38K )
Number of downloads: 4
Explain_for_SQL_As_Insert.jpg ( 180.66K )
Number of downloads: 2
SQL & PL/SQL :: Performance With Union And Global Temp Table
May 24, 2010I have a view, which has a union. (Union is required because of the nature of the data fetched). THis view is later joined with a global temp table which holds the -say employee Id the user selects.
So at runtime there is a join with the global temp table and the view. But the performance is really bad. I have tried using various hints, like materialize, /*+ CARDINALITY(gtmp 1) */ etc.
When i query the view alone,. the performance is good. When I remove the union, the performance is good. Some how with the union- there is a full table scan on one of the joining tables.
SQL & PL/SQL :: Index On Global Temp Tables
Jun 16, 2010I am trying to use Global temporary tables, and index on this table to get my results faster. I can see even if I run any query on this table, it does full table scan and not Index scan..
create global temporary table abc_tab on commit preserve rows
as select a,b,c from xyz;
create index lmn on abc_tab(a,b,c)
Creating And Dropping Global Temp Tables Inside A Single Procedure
Aug 20, 2010I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
Performance Tuning :: ORA-01652 - Unable To Extend Temp Segment By 128 In Tablespace TEMP
Jul 25, 2012One of our customer have problem with following sql statement:
SELECT c.table_name, c.column_name
FROM user_tab_columns c, user_tables t
WHERE c.table_name = t.table_name
AND c.data_type IN ('CLOB', 'BLOB');
During execution it takes all the TEMP tablespace size(8GB).
I gather system stats (dbms_stats.gather_dictionary_stats(estimate_percent=>null)) but it doesn't resolve problem.Above sql statement works fine with RULE hint but I want to know what is the reason of problem with temporary tablespace.
Reports & Discoverer :: ORA-01652 / Unable To Extend Temp Segment By 128 In Tablespace TEMP
Mar 7, 2011I am trying to run on Oracle report via Oracle Application Concurrent job. Concurrent job is completing normal but I don't get anything on print out page. In log file of this request I see message 'MSG-01003: Errors =>ORA-01652: unable to extend temp segment by 128 in tablespace TEMP'. I almost doubled the TEMP tablespace in size but still I am not able to get rid of this error message.
View 1 Replies View RelatedSQL & PL/SQL :: ORA-01652 / Unable To Extend Temp Segment By 128 In Tablespace TEMP
Apr 19, 2010The below query throws an error as mention below
My PGA_AGREGATOR_TARGET = 2GB
below query is given below.
RowsPlan
1SELECT STATEMENT
1 HASH JOIN
1 MERGE JOIN CARTESIAN
1 TABLE ACCESS BY INDEX ROWID WAT_SOURCE_DATA
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE INDX_WAT_SRC_DATA_BIT
[code]....
Error Message : ORA-01652:unable to extend temp segment by 128 in tablespace TEMP
Query :
SELECT OR004.wat_id "WAT_ID",
SYSDATE "DATE_FIRST_IDENTIFIED",
SYSDATE "DATE_LAST_IDENTIFIED",
'OR-004' "RULE_REFNO",
'RISK' "RULE_TYPE",
OR004.workspace_id "WORKSPACE_ID",
OR004.workspace_name "WORKSPACE_NAME",
[code]....
Unable To Extend Temp Segment By 128 In Tablespace TEMP
Apr 6, 2009this huge report that uses inline views. I keep getting the following error message when running the script through toad. I was thinking about using the USE_HASH hints. The sql optimizer we use is very buggy in Toad. I'm using oracle database version 10.2.0.3.
I can upload explain plan if needed.
SELECT 'Project Number^Project Start Date^Project End Date^Status^Project Manager^Task Number^'||
'Task Start Date^Task Completion Date^Task Manager^Award Number^Award Short Name^Project Organization^'||
'Task Organization^Expense Code^OMB Code^Revenue Line^Burden Rate^Burden Structure^Site^Sponsor^Type^Customer^'||
'Award Type^Award Purpose^Federal Flow Thru Code^IDC Schedule Name^Total Expenditure^Direct Charges^'||
'Indirect Charges^Cost Share Charges^Total Commitments^Direct Commitments^Indirect Commitments^Cost Share Commitments^'||
[Code]...
TEMP - Unable To Extend Temp Segment By 128 In Tablespace
Jul 16, 2013I have a TEMP tablespace with autoextend on next 10M and maxsize 5120M, now my tablespace is 99.98% full. Am getting ORA-1652: unable to extend temp segment by 128 in tablespace temp error, can i use the method to increase the maxsize value to 10240M.
View 2 Replies View RelatedSQL & PL/SQL :: Global Temporary Table
Feb 20, 2010What is the best option for GLOBAL TEMPORARY TABLE
1) option create GLOBAL TEMPORARY TABLE with ON COMMIT DELETE ROWS. and wheverever this is used for calculation commit at the end of porcedure.
CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
)ON COMMIT DELETE ROWS;
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
as
begin
[Code]....
2) create GLOBAL TEMPORARY TABLE without ON COMMIT DELETE ROWS and wheverever this is used use delete from Temp table /Truncate table and then user it.
CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
);
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
[Code]....
Create Temp Table From Existing Table
Sep 2, 2010I want to create temp table, for this i am using:
CODEcreate global temporary table help_temp
as
select * from help;
but this is creating only the table structure, not copying the table data.
Alternatives For Global Temporary Table
Mar 12, 2013I have created global temporary tables to be used in my stored procedure, in order to view reports which i created in JASPER. Since global temporary tables are session based, when multiple users are trying to generate the report, every user is getting inconsistent data.
To make it clear, what i meant is if a user A tries to view a report with some filter criteria and simultaneously user B is trying to generate the same report with another filter criteria, User A is getting User B's report data and User B is getting User A's report data. How can we avoid this problem?
SQL & PL/SQL :: Drop Global Temporary Table?
Dec 6, 2011how to drop global temporary table?
while droping global temporary table we are getting below error
"ORA-14452: attempt to create, alter or drop an index on temporary table already in use"
SQL & PL/SQL :: Global Temporary Table DDL In Schema
Dec 21, 2011How to allow only "CREATE GLOBAL TEMPORARY TABLE" DDL in a schema. I have to restrict all DDLs performing by a particular schema except GT Table.
View 8 Replies View RelatedSQL & PL/SQL :: Global Temporary Table And JAVA?
Dec 3, 2011I am using a global temporary table in which place data from a few different queries.
It then select it out into a cursor.
This procedure works fine in PL.SQL Developer and Toad. It doesn't have to be adjusted.
Java has a problem though, as the data is gone when the Java call attempts to acquire it. This is due to session pooling I suppose.
So, my question is somewhat composite.
Is there a setting in Java (JDeveloper) that I could overcome this with? Perhaps a momentary "Hold" on a session?
PL/SQL :: Not Able To Update Global Temporary Table
Oct 17, 2012create or replace procedure p_populate_gtt
as
begin
insert into gtt
select last_name,first_name,null from funcdemo where rownum <51;
update gtt set vote=100
where ln ='Tim';
end;
/
gtt is my global temp table. i am updating vote column which is null to 100.But i am not able to update it
SQL & PL/SQL :: Option For Temp Table?
Feb 18, 2010What is the Best option for SQL/sybase server Temp table to use/for data manipulation (Insert/update/delete) inside the procedure in oracle Other then global temporary table.
Since we are porting from SQL/Sybase to Oracle we don't want to Create too many global temporary table.
SQL & PL/SQL :: Can Lock Data In Global Temporary Table
Nov 15, 2011Can we lock data in global temporary table?
View 4 Replies View RelatedSQL & PL/SQL :: Global Temporary Table And Autonomous Transaction?
Jul 15, 2013The actual flow, works on this way:
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table using another procedure Named X that Merge the data from Global Temporary against the Normal Table (inserting if not exist and updating some fields if exist).
(X isn´t important on the new flow)
Now, i need to add some steps on the normal flow:
The Procedure A extracts and filter some data from the DW, this data is stored on the Global Temporary Table. Another Procedure, named B, use the data from the Global Temporary Table and store it on a normal table. Using the Data from Global Temporary Teble i must to Store some fields on another normal table, for this i use another Procedure named C that merge the data from Global Temporary Table against the data from normal table, and i must to commit at this point. X Merge the data from Global Temporary Table and the data from the Normal table con the procedure "C" against another Normal Table (inserting if not exist and updating if exist).
The issue that i´m expecting is that i can´t use "C" for merge and commit, because this truncate the data on the global temporary table. I can´t change the on commit delete rows option, because another procedures are using this Global Temporary Table on production.
Before you ask, i try using AUTONOMOUS_TRANSACTION on "C" and didn´t works because "C" can´t found data on the Global Temporary table ( i use a select count on "C" from Global Temporary), this is because The Autonomous_Transaction (i think). So, what i can do? I tried to trace the session Number on C and A and with the AT is the same ( so isn´t session change problem).
I need:Commit on the Procedure "C" without Truncating Global Temporary Table because i need this data for X.
SQL & PL/SQL :: Executing A Procedure Containing A Global Temporary Table
Mar 3, 2010create or replace procedure test
as
stmt varchar2(2000);
begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';
stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards';
execute immediate stmt;
end;
-- when am trying to execute this
begin
test;
end;
-- showing ora-01031, insufficient privileges.
Temp Table Within A Stored Procedure
Jul 15, 2013I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table. I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
[code]........
SQL & PL/SQL :: Create Temp Table From A File
Aug 22, 2011I'm having some trouble dealing with the PL/SQL coding. At the moment, I have a file (set of data) that is read using java. An array is created (line by line) and is passed into a temp. table, which is then called by the plsql (call PKG_xxx.PRC_xxx...) script/command for processing. Is there any way I could directly get the file loaded into plsql (into a temp. table maybe?)
The data are just numbers and letters. I already got the PL/SQL part to select the set I want for different cases. All I need is to get the file (directly or indirectly) for the procedure without going through java.
SQL & PL/SQL :: Creation Of Temp Table Within Procedure
Aug 17, 2010I need to create a temp table within a stored procedure. I want to apply some logic on the table, and then delete it when it is completed in runtime -- all inside the stored procedure.
View 13 Replies View RelatedSQL & PL/SQL :: Change The Temp Table Status?
Jun 21, 2011In one of the databases, we have created more temp tables as on commit preserve rows. but i want to change the on commit preserve rows to on commit delete rows as per application requirements.I have searched the google but i didnt find any alter scripts for it.?
View 1 Replies View RelatedSQL & PL/SQL :: Temp Table Within Stored Procedure
Jul 15, 2013I have been developing in MS SQL for about 15. So I'm still getting use to the syntax and features within Oracle.I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
AAAA.1D1D
[code].....
SQL & PL/SQL :: Can Use Temp Table In Function Which Uses Ref Cursor
Dec 6, 2012example to use temp table in function with only ref cursor.
I need to use for the reporting purpose.
SQL & PL/SQL :: Replace Temp Table With Collection?
Nov 4, 2011how I can replace gtt in the following procedure with an Oracle Collection?
gtt or Collection is more efficient?
PROCEDURE report_gen (
i_table IN NUMBER
)
IS
BEGIN
create global temporary table test_gtt (tid NUMBER(15));
[code].....
SQL & PL/SQL :: Make A Trigger On Temp Table?
Dec 27, 2011 i am trying to make a trigger on temp table(emp_rec_temp), whose data will be insert into master(emp_rec) table. But not yet get the success.
create or replace trigger trig_emp_find after insert
on emp_rec_temp for each row
declare
V_STR VARCHAR2(25);
j number;
[code]....
PL/SQL :: Temp Tablespace Space Due To GTT Table
Sep 3, 2012We are using a GTT table to store the summarize data and display it on same screen(10g 10.2.0.5). Now we are facing temporary tablespace space issue very frequently as our client do not enable the auto extend on for temp table space. We have analyzed the AWR and came to know that there are 900000 inserts per hour on an average. Client DBA Claims that there are sessions(1or 2) which inserts the data in temporary table continuously for 2-3 days.
According to him one session is running from 28th Aug and problem comes on 2nd Sep and after killing the problematic session the application will work fine. Generally this problems come on weekend. I have discussed with our dev team and as per them there is no session leakage issue.
following is the insert statement:
INSERT INTO DT_CA_STNDALN_DETAILS_TMP (ORG_ID,BA_PRODTYPE_ID,MAX
TENOR,GROSSLIMIT,GROSS_UTILISATION,HAS_MDR,HAS_CLUSTER,SIGN_IN
D,GROSS_AVAILABILITY,COLLATERAL,NET_UTILISATION,DT_CA_STNDALN_DE
TAILS_TMP_VER,DM_LSTUPDDT,NET_AVAILABILITY) VALUES (:1,:2,:3,:4,
:5,:6,:7,:8,:9,:10,:11,:12,sysdate,:13) ;