Home » RDBMS Server » Server Utilities » How to export global temporary tables? (Oracle 10.2.0.3.0, MS Windows Server 2003 Standard Edition SP2)
How to export global temporary tables? [message #582432] Thu, 18 April 2013 06:33 Go to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm probably doing something wrong, but I can't figure out what would that be.

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!

Google didn't help much; I found that someone on OTN forums suggested use of INCLUDE parameter:
INCLUDE=TABLE:"IN (select table_name from user_tables where temporary = 'Y')"
but that didn't do anything.

These are my GTTs:
SQL> show user
USER is "TRAFOGLED"
SQL>
SQL> select table_name from user_tables where temporary = 'Y';

TABLE_NAME
------------------------------
RPRT_NEDOSTACI
SLIKE_POPIS
EVIDENCIJA_STAVKE_ZA_PROVJERU

SQL>

Parameter file (which is supposed to export GTTs only; as I said, all other tables export correctly, I'm looking for a way to export GTTs):
DUMPFILE=trafo.dmp
DIRECTORY=ext_dir
LOGFILE=trafo_exp.log
INCLUDE=TABLE:"IN (select table_name from user_tables where temporary = 'Y')"

I'm running it as
C:\TEMP>D:\oracle\product\10.2.0\db_1\BIN\expdp trafogled/pw@ora10 parfile=trafo_exp.par

Export: Release 10.2.0.3.0 - Production on â•šetvrtak, 18 Travanj, 2013 13:23:28

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "TRAFOGLED"."SYS_EXPORT_SCHEMA_01":  trafogled/********@ora10 parfile=trafo_exp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Master table "TRAFOGLED"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TRAFOGLED.SYS_EXPORT_SCHEMA_01 is:
  C:\TEMP\TRAFO.DMP
Job "TRAFOGLED"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:23:49


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.

Does anyone have an idea?
Re: How to export global temporary tables? [message #582437 is a reply to message #582432] Thu, 18 April 2013 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59498
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works for me (although I tested it in 11.2, don't have access to 10.2 for the moment):
SQL> create global temporary table gtt (col number);

Table created.

SQL> select table_name from user_tables where temporary = 'Y';
TABLE_NAME
------------------------------
GTT

1 row selected.

C:\>expdp michel/michel directory=work_dir schemas=michel \
INCLUDE=TABLE:\"IN (select table_name from user_tables where tempoary = 'Y')\"

Export: Release 11.2.0.3.0 - Production on Jeu. Avr. 18 13:54:49 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MICHEL"."SYS_EXPORT_SCHEMA_01":  michel/******** directory=work_dir schemas=michel INCLUDE=TABLE:"IN (select table
name from user_tables where temporary = 'Y')"
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Master table "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MICHEL.SYS_EXPORT_SCHEMA_01 is:
  D:\USERS\CADOT\DOCUMENTS\TEMP\EXPDAT.DMP
Job "MICHEL"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:56:16


C:\>impdp michel/michel directory=work_dir sqlfile=dump.sql full=y

Import: Release 11.2.0.3.0 - Production on Jeu. Avr. 18 13:56:56 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MICHEL"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "MICHEL"."SYS_SQL_FILE_FULL_01":  michel/******** directory=work_dir sqlfile=dump.sql full=y
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Job "MICHEL"."SYS_SQL_FILE_FULL_01" successfully completed at 13:57:04


C:\>type D:\USERS\CADOT\DOCUMENTS\TEMP\dump.sql
-- CONNECT MICHEL
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE GLOBAL TEMPORARY TABLE "MICHEL"."GTT"
   (    "COL" NUMBER
   ) ON COMMIT DELETE ROWS ;

Don't ask me what are all those events, I don't know. Wink

Regards
Michel
Re: How to export global temporary tables? [message #582446 is a reply to message #582437] Thu, 18 April 2013 07:27 Go to previous messageGo to next message
Littlefoot
Messages: 19711
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for your time, Michel!

I did everything from scratch - dropped the target user, created it back again, exported the source schema and imported it into the target schema and everything seems to be OK. Import says
...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
<list of tables follows here>
...
I guess that the first line above created all tables (GTT included), while the second line shows how many records were imported. I was puzzled with a list of tables that contain 0 rows, but GTTs weren't listed.

As of me checking their existence, my bad - I was connected to another user (not involved into that export/import process) ... no wonder I didn't see these tables ./fa/1620/0/ .

Sorry for disturbing you for nothing.
Re: How to export global temporary tables? [message #582451 is a reply to message #582446] Thu, 18 April 2013 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59498
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not for nothing, I now know that Oracle adds a set of events and I want to know why and what they are for.

Regards
Michel
Re: How to export global temporary tables? [message #582490 is a reply to message #582451] Thu, 18 April 2013 14:38 Go to previous message
Mahesh Rajendran
Messages: 10629
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Michel,
Hope this helps. The missing ones should be documented in metalink.
oracle@ark#cat $ORACLE_HOME/rdbms/mesg/oraus.msg | egrep  '10150|10904|25475|10407|10851|22830'
10150, 00000, "import exceptions"
10407, 00000, "enable datetime TIME datatype creation"
10851, 00000, "Allow Drop command to drop queue tables"
10904, 00000, "Allow locally managed tablespaces to have user allocation"
/   mahrajag  04/04/11 - Backport bug 10150436 from main
22830, 00000, "Event enabling VARRAYs columns to be created as OCTs"
/ 25475 is reserved for debug mode

[Updated on: Thu, 18 April 2013 14:51]

Report message to a moderator

Previous Topic: How to load date filed along with time stamp using sqlldr
Next Topic: Doubts in data restore from one schema to another
Goto Forum:
  


Current Time: Thu Oct 30 12:31:09 CDT 2014

Total time taken to generate the page: 0.13804 seconds