Server Utilities :: How To Export Global Temporary Tables

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


ADVERTISEMENT

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 View Related

Server Utilities :: Export Multiple Tables Only?

Jul 10, 2012

send me the command for exporting multiple tables(1000+) in Linux env. 9i db, i know we can do using spool command but dont know exactly how to put it. i know using Datapump but this is 9i.

View 7 Replies View Related

Server Utilities :: How To Export User Without Data Of Few Tables

Jul 29, 2010

I need to export a user with all the tables. But I need to export data into only few tables, need to omit the data of few tables.

Ex I dont want to export data of Audit tables with AU prefix.

View 7 Replies View Related

Server Utilities :: Tables Moved Using Export And Import?

Jun 9, 2011

I was told to move 8 tables along with constraints,indexes,grants,rows,triggers from one database to another database.I did export and import for that.The command i used was

exp p5/chevuri@db3.SBC.COM file=C:alaexp.dmp log=C:alaexp.log
tables= ('tab1','tab2','tab3','tab4','tab5','tab6','tab7') rows=y indexes=y grants=y
constraints=y triggers=y direct=y

Below is the export output log.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 -
64bit Production With the Partitioning, OLAP, Data Mining and Real
Application Testing options Export done in WE8MSWIN1252 character set
and AL16UTF16 NCHAR

character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Direct Path ...
. . exporting table tab1 12 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.

[code]...

Here is the import output log
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via direct path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing JAM's objects into JAM
.
[code]...

Everything got imported successfully . Still i have a doubt in export and import command, whether the command that i used for export and import was correct or if there is anything need to be added in command.

View 4 Replies View Related

Server Utilities :: Skip Empty Tables While Export?

Sep 22, 2011

How can we skip empty tables when we are doing export.

View 7 Replies View Related

Server Utilities :: How To Export Data Based On Join Between 3 Tables

Jan 10, 2011

How to export a data, which is a join of three tables.Will Export or dbms_datapump supports for above scenario.

Database: DB1
Tables: T1, T2 & T3
Select: t1.*,t2.*,t3.*
Join: t1.c1=t2.c1 and t2.c1=t3.c1

View 2 Replies View Related

Server Utilities :: Export / Import Tables Based On Date Condition?

Jul 26, 2012

i'm trying to do an export/import process using command prompt and the idea is export a records based on the date condition. and the date will be the parameter. my code is like this:

exp <username>/<password>@<database> file=<table_name>.dmp tables=<source_table> query="where <date> between &start_date AND &end_date";

is it possible to do like this, that it should prompt you to enter the start and end date?

then my import script:

imp <username>/<password>@<database> dumpfile=<table_name>.dmp tables=<target_table>;

the idea is get only the records from ProdDB based on the date condition, and append it to the MISDB.

View 12 Replies View Related

SQL & PL/SQL :: Global Temporary Table

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

Alternatives For Global Temporary Table

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

SQL & PL/SQL :: Drop Global Temporary Table?

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

SQL & PL/SQL :: Global Temporary Table DDL In Schema

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

SQL & PL/SQL :: Global Temporary Table And JAVA?

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

PL/SQL :: Not Able To Update Global Temporary Table

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

SQL & PL/SQL :: Can Lock Data In Global Temporary Table

Nov 15, 2011

Can we lock data in global temporary table?

View 4 Replies View Related

SQL & PL/SQL :: Global Temporary Table And Autonomous Transaction?

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

SQL & PL/SQL :: Executing A Procedure Containing A Global Temporary Table

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

Forms :: How To Insert And Update From Global Temporary Table

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

SQL & PL/SQL :: Less Number Of Rows Inserted In Global Temporary Table

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

SQL & PL/SQL :: Mutating Trigger Error Using Global Temporary Table?

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

SQL & PL/SQL :: Partial Data Inserted When Reading From Global Temporary Table?

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

Server Utilities :: Append Tables Content To Existing Tables?

Nov 9, 2010

problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.

we have found something called table_exist_action=append in impdp.

View 2 Replies View Related

Export/Import/SQL Loader :: Why Do Export-import Require Temporary Tablespace

Aug 9, 2012

Why do export-import require temporary tablespace? Since export-import do behave like DMLs, when does temporary tablespace be needed by datapump utility?

View 2 Replies View Related

Server Utilities :: Export Schemas From 10g To 11g?

Apr 27, 2011

I am trying to export schemas from 10g to 11g. The NLS_CHARACTERSET for 10g is WE8ISO8859P1 and the NLS_CHARACTERSET for 11g is WE8MSWIN1252. Is it fine or do I need to change the character set, so that I will be able to successfully do the export/import?

View -1 Replies View Related

Server Utilities :: Export Error?

Nov 28, 2007

i am having a problem when trying to export my DB,i could run an import fine,i have ran the catalog.sql,catproc.sql,catexp.sql and utlrp.sql again.Is it because the client and DB are different?How can i solve this problem

exp usr/pass file=exp_full.dmp log=exp_full.log full=y consistent=y
Export: Release 8.1.7.0.0 - Production on Wed Nov 28 13:40:04 2007
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option

[code]...

View 14 Replies View Related

Querying Records From Temporary Tables

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

Server Utilities :: Export Sequence Using Expdp

Apr 13, 2011

I would like to export specific tables(not entire schema) including metadata. I am using a parameter file for expdp.

Tables=emp,dept

Does this also include all metadata or should i also add the below Include in the parfile ?

INCLUDE =Indexes,Sequences,Procedures,Views

View 3 Replies View Related

Server Utilities :: Export / Import Errors?

Apr 26, 2011

I was asked to do export/import of some schemas from 10g(linux) to 11g(AIX) using original expor/import method. I did not consider the character set and started doing export and import. while exporting, I get questionable statistics error in export log file. In the import log, I see the error like CREATE DATABASE LINK "xxxxxxxxxxxxx" CONNECT TO "xxxx" IDENTIFIED BY...

What can be done with these errors?

View 4 Replies View Related

Server Utilities :: Export Data From Excel To 10g?

Mar 4, 2010

i need to export master data in excel sheets to our database and we use toad too. How i can export the data with the use of macros in excel. how i can export data from excel to oracle.

View 6 Replies View Related

Server Utilities :: Import / Export Log File

May 11, 2010

Whats is the usage of log file in Import/export .If i use following command ,it exports successfully

exp scott/tiger file=check.dmp log=empc.log tables=emp

and if i remove .log from here it will also export successfully So why do we use .log in import/export.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved