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  |
 |
Littlefoot
Messages: 21823
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')"
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 asC:\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  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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. 
Regards
Michel
|
|
|
|
|
|
|
|
| Re: How to export global temporary tables? [message #582490 is a reply to message #582451] |
Thu, 18 April 2013 14:38 |
 |
Mahesh Rajendran
Messages: 10708
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 |
|
|
|
Goto Forum:
Current Time: Mon Mar 03 16:21:06 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
