Use of temporary tablespace during data import from expdp/impdp

 

The temporary tablespace for U1 users is tempdata, and the default temporary tablespace for the database is Temp. There is a table tm_out_b1 under Scott. If we use Scott to export the table tm_out_b1, the temporary tablespace occupied during the export process is Scott's default temporary tablespace temp.

Create temporary tablespace tempdata tempfile 'd: \ app \ Administrator \ oradata \ ora11g \ tempdata01.dbf' size 100 m;

Create user U1 identified by U1;

Grant connect, resource to U1;

Alter user U1 temporary tablespace tempdata;

SQL> select * From database_properties where property_name = 'default _ temp_tablespace ';
 
Property_name property_value description
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Default_temp_tablespace temp name of default temporary tablespace

Select username, temporary_tablespace from dba_users where username in ('u1', 'Scott ');
 
Username temporary_tablespace
------------------------------------------------------------
Scott temp
U1 tempdata

C: \ Users \ Administrator> expdp u1/U1 dumpfile = TMP. dmp directory = dir tables = Scott. tm_out_b1

Export: Release 11.2.0.1.0-production on Monday May 7 16:36:24 2012

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

Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the partitioning, OLAP, data mining and real application testing options
Start "U1". "sys_export_table_02": u1/******** dumpfile = TMP. dmp directory = dir tables = Scott. tm_out_ B
Using the blocks Method for estimation...
Processing object type table_export/table/table_data
Total estimation using the blocks method: 10.62 GB
Processing object type table_export/table
Processing object type table_export/table/index/Index
Processing object type table_export/table/constraint/Constraint
Processing object type table_export/table/index/statistics/index_statistics
Processing object type table_export/table/statistics/table_statistics
.. Export "Scott". "tm_out_b1": "t_out_ B _2012_01" 9.182 GB 51565563 rows
.. Export "Scott". "tm_out_b1": "other" 2.514 MB 13297 rows
.. Export "Scott". "tm_out_b1": "t_out_ B _2012_02" 141.7 kb 691 rows
.. Export "Scott". "tm_out_b1": "t_out_ B _2012_04" 83.10 kb 399 rows
.. Export "Scott". "tm_out_b1": "t_out_ B _2012_03" 16.54 kb 15 rows
The master table "U1". "sys_export_table_02" is successfully loaded/uninstalled"
**************************************** **************************************
The dump file set of u1.sys _ export_table_02 is:
E: \ dump \ TMP. dmp
The job "U1". "sys_export_table_02" was successfully completed at 16:39:59.

 

The temporary user tablespace of U1 is used for export by the U1 user, which does not affect the use of the temporary tablespace of the system.

SQL> select username, tablespace, blocks from V $ sort_usage;
 
Username tablespace Blocks
-----------------------------------------------------------------------
System temp 128
Scott temp 128
U1 tempdata 128
U1 tempdata 128
U1 tempdata 128
U1 tempdata 128
U1 tempdata 128
U1 tempdata 128
 
8 rows selected

 

Using Scott to export or import data will occupy the use of temporary system tablespace

SQL> select username, tablespace, blocks from V $ sort_usage;
 
Username tablespace Blocks
-----------------------------------------------------------------------
System temp 128
Scott temp 128
Scott temp 128
Scott temp 128
Scott temp 128
Scott temp 128
Scott temp 128
Scott temp 128
 
8 rows selected

 

Similarly, the import process is the same.

C: \ Users \ Administrator> impdp u1/U1 dumpfile = tmp_1.dmp directory = dir tables = Scott. tm_out_b1 remap_sche
MA = SCOTT: U1 logfile = 1.log table_exists_action = replace

Import: Release 11.2.0.1.0-production on Monday May 7 17:00:16 2012

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

Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the partitioning, OLAP, data mining and real application testing options
The master table "U1". "sys_import_table_01" is successfully loaded/uninstalled"
Start "U1". "sys_import_table_01": u1/******** dumpfile = tmp_1.dmp directory = dir tables = Scott. tm_out_b1
Remap_schema = SCOTT: U1 logfile = 1.log table_exists_action = replace
Processing object type table_export/table
Processing object type table_export/table/table_data
ORA-31693: Table Data Object "U1". "tm_out_b1": "t_out_ B _2012_01" unable to load/unload and skipped, error:
ORA-02354: An error occurred while exporting/importing data
ORA-01688: Table u1.tm _ out_b1 partition t_out_ B _2012_01 cannot be extended through 8192 (in tablespace Users)
.. Imported "U1". "tm_out_b1": "other" 2.514 MB 13297 rows
.. Imported "U1". "tm_out_b1": "t_out_ B _2012_02" 141.7 kb 691 rows
.. Imported "U1". "tm_out_b1": "t_out_ B _2012_04" 83.10 kb 399 rows
.. Imported "U1". "tm_out_b1": "t_out_ B _2012_03" 16.54 kb 15 rows
Processing object type table_export/table/index/Index
Processing object type table_export/table/constraint/Constraint
Processing object type table_export/table/index/statistics/index_statistics
Processing object type table_export/table/statistics/table_statistics
The job "U1". "sys_import_table_01" has been completed, but there is one error (completed at 17:03:14)

 

SQL> select username, tablespace, blocks * 8/1024 from V $ sort_usage;
 
Username tablespace blocks * 8/1024
--------------------------------------------------------------------------
Sys Temp 1
U1 tempdata 1
U1 tempdata 1
U1 tempdata 1
U1 tempdata 1
U1 Temp 1

We can see that U1 users use very few temporary system tablespace.