Global Temporary Tables
Jan 17, 2011
I have two database DB1 for EBS database and DB2 for Portal database. DB2 is always up.
DB1 uses some Global Temporary tables to write and store session level information.
I have Secondary database also for DB1. Whenever DB1 is down and its secondary database base is up, my requirement is to enable write operation to these Global Temporary Tables. Since secondary database we open Read-Only mode , I can't write to these GTTs.
DB2 is always up so I want to create the copies of these GTTs in DB2 portal database. Is there any harm on doing this.
Is there any harm storing session level information of DB1 database In DB2 database through DB-Link.
View 1 Replies
ADVERTISEMENT
Apr 18, 2013
There's an Application Express application which is based on a schema named TRAFOGLED. In order to let users test new features, there's a test application (Apex has export/import capabilities; no problem about that) which is based on another schema whose name is TRAFOTEST.
I'd like to export TRAFOGLED and import it into TRAFOTEST.I'm using 10gR2 EXPDP utility with a parameter file. Everything seems to be OK, except the fact that I'm unable to export global temporary tables (GTT). How can I tell? I didn't see them after import!
These are my GTTs:
SQL> show user
USER is "TRAFOGLED"
SQL>
SQL> select table_name from user_tables where temporary = 'Y';
[code]...
C:TEMP>
No tables were exported. Certainly, I don't expect any data to be exported, but I'd be happy with CREATE TABLE statements so that I don't have to create these tables separately.
View 4 Replies
View Related
Feb 20, 2010
What 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]....
View 26 Replies
View Related
Mar 12, 2013
I 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?
View 1 Replies
View Related
Dec 6, 2011
how 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"
View 1 Replies
View Related
Dec 21, 2011
How 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 Related
Dec 3, 2011
I 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?
View 6 Replies
View Related
Oct 17, 2012
create 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
View 21 Replies
View Related
Nov 15, 2011
Can we lock data in global temporary table?
View 4 Replies
View Related
Jul 15, 2013
The 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.
View 7 Replies
View Related
Mar 3, 2010
create 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.
View 9 Replies
View Related
May 15, 2013
Global temporary Table Name:
1.MT_GBL
2.DT_GBL
3.DT2_GBL
Base Table Name:
1.MT
2.DT
3.DT2
My Steps:
1.Insert all data from global table to base table.
2.Update all data (that means retrieved all data from base table to global table and update this data). Question: How to Insert and Update from Global temporary table ??
View 4 Replies
View Related
Sep 8, 2012
how to resolve the following issue ...
insert into GTT (select query) inserting less no. of rows than that returned by the query.
The global temporary table has ON COMMIT PRESERVE ROWS.
View 3 Replies
View Related
Jun 21, 2010
How can we overcome mutating trigger error using global temporary table.
Suppose if we use the following trigger we will get mutating trigger error.
CREATE OR REPLACE TRIGGER t1
AFTER INSERT ON emp
FOR EACH ROW
DECLARE
BEGIN
UPDATE emp SET sal=sal+100;
END;
/
View 27 Replies
View Related
Jun 25, 2012
I have a complex sql query that fetches 88k records. This query uses a global temporary table which is the replica of one of our permanent tables. When I do Create table..select... using this query it inserts only fewer records. But when I make the query point to the permanent table it inserts all 88k records.
1. I tried running the select query separately using temp and perm table. Both retrieves 88k records.
2. From debugging I found that this problem occurred when we were trying to perform a left outer join on an inline view.
However this problem got resolved when I used the /*+ FIRST_ROWS */ hint.
From my limited oracle knowledge I assume that it is the problem with the query and how it is processed in the memory.
View 1 Replies
View Related
Feb 2, 2011
I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.
However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.
View 3 Replies
View Related
Jun 16, 2010
I 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)
View 10 Replies
View Related
Jul 16, 2012
Example Tables and Data:
I have 5 Courses, each course has 2 assignments.
with Courses as (
select 100 courseID from dual union all
select 200 from dual union all
select 300 from dual union all
select 400 from dual union all
select 500 from dual
), Assigns as (
[code]...
I need each assignment to get a color assigned to it according to it's courseID. So my theory is that I'd have a temporary table with a list of colors; each courseID would get assigned a color but my question is how do I assign each courseID a color without anything to join the temporary table on? Once each courseID has a color, I'd join in Assigns so that each assignment has a color associated with it according to it's courseID.
--Color table with 6 colors
with Colors as (
select 'red' color from dual union all
select 'blue' from dual union all
select 'yellow' from dual union all
select 'green' from dual union all
select 'purple' from dual union all
select 'teal' from dual
)
View 8 Replies
View Related
Aug 20, 2010
I 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..
View 3 Replies
View Related
May 10, 2011
While I was exporting ( schema level ) ,I am getting following errors :
vaughn$ exp parfile=qar808par.txt
Export: Release 11.1.0.7.0 - Production on Tue May 10 10:52:34 2011
Copyright © 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users .
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOXFIRE_TO_TSFDA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOXFIRE_TO_TSFDA
About to export TOXFIRE_TO_TSFDA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully
'Parfile' contents :
owner=TOXFIRE_TO_TSFDA
file=exp_TOXFIRE_TO_TSFDA_QAR808.dmp
consistent=y
direct=y
recordlength=65535
log=exp_TOXFIRE_TO_TSFDA_QAR808.log
What is the reason, and It is saying that temporary tablespace is empty ( temp tablespace have almost free space ) why it is saying like that? And users default temp tablespace is that tablespace only ( database have only on temp tablespace
View 1 Replies
View Related
Aug 30, 2013
creating a temporary table.i have this query mentioned below
CREATE TABLE WEBPEN AS (SELECT PNSR_PPO_NO PPO,PNSR_FILE_NO,
DECODE(F_GET_APPLN_NO(PNSR_PK),'1',PNSR_VOL_NO,F_GET_APPLN_NO(PNSR_PK)) APPLN_NO,
PNSR_FULL_NAME NAME,
TO_CHAR(PNSR_DOB,'DD/MM/YYYY') DOB,
TO_CHAR(PNSR_DOR,'DD/MM/YYYY') DOR,
F_GET_ADBK_NAME(PNSR_TO_PENSION) TREASURY,
PNSR_SPOUSE_NAME SPOUSE,
[code]....
This creates a table webpen with around 54107 rows. What i am want is every time run "select * from webpen" it should run the above query and give the result as per the values in main table M_PENSIONER ,M_PEN_DCRG_WITHHELD.
What i want is it should truncate the existing values and insert the value by running the above mentioned query .
View 6 Replies
View Related
Jun 5, 2005
I migrate procedures MS SQL Server to Oracle.In MS SQL SSERVER the use of instructions INSERT with procedure results which are in storage or dynamic instructions EXECUTE in place of VALUES clause is permissible. This construction is similar to INSERT/SELECT but we have to do with EXEC instead of SELECT. The part of EXEC should include exactly one resulted collection about the equivalent types to the types of table columns. In case of the stored procedure, we can pass on proper parameters, use the form of EXEC('string') and even call up wideranging procedures or remote control procedures from different servers. Calling up remote control procedures from different server, which place data in temporary table, and later realizing join with obtainable data, we can construct diffuse joins. For example. I want insert results stored procedures sp_configure, proc_obj in temporary table.
1)INSERT #konfig
exec sp_configure.
2)
CREATE PROCEDURE proc_test
@Object_ID int,
AS
SET XACT_ABORT ON
BEGIN TRAN
CREATE TABLE #testObjects ( Object_ID int NOT NULL )
INSERT
#testObjects
EXEC
proc_obj @Object_ID,3,1
COMMIT TRAN
RETURN(0)
go
how migrate for example code to Oracle?
View 11 Replies
View Related
Aug 8, 2013
Today I found a Temporary tablespace is corrupted, generally speaking, how we can recover a temporary tablespace?
View 3 Replies
View Related
Apr 26, 2011
Is there a neat way other than having to reformat the data in a temporary table to do the following,I've got the following content in a table:
CODECustid Type Nb Amount
1 Deposit 2 10000
1 Withdrawal 1 4000
I'd like to show the data in this manner:
CustID Deposit DepositAmount Withdrawal WithdrawalAmount
1 2 10000 1 4000
View 3 Replies
View Related
Jan 7, 2012
i have one table HR.employees
SQL> desc hr.employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL>
in this table i want to temporary disable all constraint in this employees table
View 4 Replies
View Related
Sep 26, 2011
I have created a query that display the difference in the tables and column of two users. Some of the column in the new user have been added new which were not there in the old user and some columns from the old user still exits but their data_type is changed.
Now I want to select one more column in the select statement specifying whether the column is modified or has been added.
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'ABC' AND
TABLE_NAME NOT IN (SELECT table_name FROM all_tables where owner = 'ABC'
MINUS
SELECT table_name FROM all_tables where owner = 'XYZ' )
MINUS
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'XYZ'
[code]....
View 9 Replies
View Related
Jun 24, 2010
How can we find the temporary tablespace and default tablespace names.
View 4 Replies
View Related
Jul 13, 2013
We have EBS database with version 11.1.0.7 . Our tablespace is not releasing space ,as the below query show that TEM2 is using only 3 g .
SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_freeFROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) DWHERE A.tablespace_name = D.nameGROUP by A.tablespace_name,
[Code]....
View 3 Replies
View Related
Mar 13, 2013
create table my_rows
(
my_envvarchar2(100),
anumber(2),
bnumber(2)
)
/
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
[code]....
The first row means that the value 10 represents 40% in the couple (10,20). Meaning if I have 100 rows with the couple (10,20), 40 rows will be marked with the value 10 and 60 will be marked with the value 20. To do this, I used to create a temporary table with the same structure as the my_rows table with a new column "the_value" and I used to update this new column wth a PL/SQL for loop. But I think it is doable in a signle SQL.
View 9 Replies
View Related
Jan 11, 2011
How can be create temporary objects through forms 10g?
In oracle we can create a temporary table so similarly by using forms_ddl can we create a temporary table?
Are there any other temporary objects?
View 7 Replies
View Related
