When Oracle 11g is exported using EXP, empty tables cannot be exported, and 11 gexp

When Oracle 11g is exported using EXP, empty tables cannot be exported, and 11 gexp

I. cause:
There is a new feature in 11G. When the table has no data, no segment is allocated to save space.
1. insert a row and then roll back to generate a segment.

This method inserts data into an empty table and then deletes it, resulting in a segment. An empty table can be exported.

2. Set the deferred_segment_creation parameter.

Show parameter deferred_segment_creation

NAME TYPE VALUE

---------------------------------------------------------

Deferred_segment_creation boolean TRUE

SQL> alter system set deferred_segment_creation = false;

The system has been changed.

SQL> show parameter deferred_segment_creation

NAME TYPE VALUE

---------------------------------------------------------


Deferred_segment_creation boolean FALSE

The default value of this parameter is TRUE. When it is set to FALSE, segment is assigned to both empty tables and non-empty tables.

It should be noted that the value setting does not affect the empty tables previously imported and cannot be exported. It can only be used for the newly added tables. To export an empty table, you can only use the first method.

Ii. solution:

1. First query all empty tables under the current user

Select table_name from user_tables where NUM_ROWS = 0;

2. Use the following statement to find an empty table:

Select 'alter table' | table_name | 'allocate extent; 'from user_tables where num_rows = 0

Export the query result and execute the exported statement.

'Altertable' | TABLE_NAME | 'allocateextent ;'

-----------------------------------------------------------

Alter table AQ $ _ MEM_MC_H allocate extent;

Alter table AQ $ _ MEM_MC_G allocate extent;

Alter table AQ $ _ MEM_MC_ I allocate extent;

Alter table AQ $ _ AQ_PROP_TABLE_T allocate extent;

Alter table AQ $ _ AQ_PROP_TABLE_H allocate extent;

Alter table AQ $ _ AQ_PROP_TABLE_G allocate extent;

Alter table AQ $ _ AQ_PROP_TABLE_ I allocate extent;

Alter table AQ $ _ KUPC $ DATAPUMP_QUETAB_T allocate extent;

Alter table AQ $ _ KUPC $ DATAPUMP_QUETAB_H allocate extent;

Alter table AQ $ _ KUPC $ DATAPUMP_QUETAB_G allocate extent;

Alter table AQ $ _ KUPC $ DATAPUMP_QUETAB_ I allocate extent;

3. Then execute

Exp username/password @ database name file =/home/oracle/exp. dmp log =/home/oracle/exp_smsrun.log successful!


When ORACLE 11G is exported using EXPORT, empty tables cannot be exported.

There is a new feature in 11G. When the table has no data, no segment is allocated to save space.

Solution:
1. insert a row and then roll back to generate a segment.
This method inserts data into an empty table and then deletes it, resulting in a segment. An empty table can be exported.

2. Set the deferred_segment_creation parameter.
The default value of this parameter is TRUE. When it is set to FALSE, segment is assigned to both empty tables and non-empty tables.

Note that the value setting does not affect the existing empty tables and cannot be exported. It can only be used for the newly added tables. To export an empty table, use the first method]

I don't think it's a solution. Then I used expdp and impdp.
Create directory expdp_dir as '/data/app1/dp ';
Grant read, write on directory expdp_dir to DRGN_OWNER;

Expdp DRGN_OWNER/DRGN_OWNER DIRECTORY = expdp_dir DUMPFILE = DRGN_OWNER.dmp SCHEMAS = DRGN_OWNER logfile = DRGN_OWNERexpdp.log

Create directory impdp_dir as '/data/app1/dp ';
Grant read, write on directory impdp_dir to DRGN_OWNER;

Impdp DRGN_OWNER/DRGN_OWNER DIRECTORY = impdp_dir DUMPFILE = DRGN_OWNER.dmp logfile = DRGN_OWNER.dmpimpdp.log

The empty table has already been imported.

For DBA to create a new database, I personally suggest that you execute it immediately after an empty database is created.
Alter system set deferred_segment_creation = flase ssation = spfile;
Shutdowm immediate
Startup




[Summary]
1. I did not study the new features of 11 GB, which caused the problem to take two hours.
2. We recommend that you use the stable version 10.2.0.4, which is rejected by the customer. Currently, 11G is the mainstream version. Not every customer is conservative about using the stable version, rather than the latest version.
3. a us project has less contact with a certain ELL project. Four years ago, the implementation of the IBM P570 dual-host Oracle10g was boring.
4. I once again demonstrated that many Party A and the manufacturers are basically the same: Pig beforehand and Zhuge Liang afterwards. It is similar to the popular brick-and-mortar home in China.
5. Technology is definitely a road to failure



In Oracle 11g, exp cannot export empty tables by default. Using traditional exp, imp Migration to heterogeneous platform databases is troublesome. However, you can use expdp and impdp for migration.

Migrate the 64-bit windows 2003 Oracle11gR2 database to 64-bit Linux RedHat Enterprise 5. You can use expdp and impdp for data migration.

For example, if Windows is server A, Linux is server B, and database users are test, data of server A is migrated to server B.


On server:

1,

SQL> create directory expdp_dir as 'd: \ mzl \ backup ';

SQL> grant read, write on directory expdp_dir to test;

2. Create the Directory D: \ mzl \ backup in the windows directory

3. Export in the doscommand window:

Expdp test/test DIRECTORY = expdp_dir DUMPFILE = test. dmp logfile = testexpdp. log

Operate on server B:

4. SQL> create directory impdp_dir as '/home/oracle/impdp_dir ';

SQL> grant read, write on directory impdp_dir to test;

1. The/home/oracle/impdp_dir directory must be available in the system. The impdp_dir directory must have read and write permissions.

(Chmod 777 impdp_dir)

5. Use ftp to upload the data exported by server A to the/home/oracle/impdp_dir directory of server B.

Configure the server name of server B in server A and import data to server.

6. Export in the doscommand window:

Impdp test/test @ B _database DIRECTORY = impdp_dir DUMPFILE = test. dmp logfile = testimpdp. log

(Note the case sensitivity. If test. dmp is capitalized in linux, it must be changed to uppercase. Linux case sensitive)



This article from the Linux community website (www.linuxidc.com) original link: http://www.linuxidc.com/Linux/2011-08/41146.htm

In oracle 11g r2, we found that the traditional exp cannot export an empty table, and then query it,
Take the following steps to take notes.

Oracle 11g adds a parameter: deferred_segment_creation, which indicates that the segment delay is created. The default value is true. What does it mean?

If this parameter is set to true, you create a new table T1 without inserting data into it, and the table will not be allocated extent immediately, that is, it does not occupy data space, space is allocated only after you insert data. This saves a small amount of space.

Solution

1. After the deferred_segment_creation parameter is set to FALSE, segment is assigned to both empty and non-empty tables.

In sqlplus, run the following command:

SQL> alter system set deferred_segment_creation = false;

View:
SQL> show parameter deferred_segment_creation;


After this value is set, it only applies to the newly added tables and does not apply to the empty tables created previously.

Note that you must restart the database to make the parameters take effect.

2 instructions on using ALLOCATE EXTENT


You can use allocate extent to ALLOCATE Extent to database objects. The syntax is as follows:

-----------
Allocate extent {SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer}
-----------

You can manually allocate Extent for data tables, indexes, and materialized views.

Example of allocate extent:

ALLOCATE EXTENT
Allocate extent (SIZE integer [K | M])
Allocate extent (DATAFILE 'filename ')
Allocate extent (INSTANCE integer)
Allocate extent (SIZE integer [K | M] DATAFILE 'filename ')
Allocate extent (SIZE integer [K | M] INSTANCE integer)


The complete syntax for data table operations is as follows:

-----------
Alter table [schema.] table_name allocate extent [({SIZE integer [K | M] | DATAFILE 'filename' | INSTANCE integer})]
-----------


Therefore, you need to build the following simple SQL command:

-----------
Alter table aTabelName allocate extent
-----------



3.2 construct an SQL command to allocate space to empty tables,


Query all empty tables under the current user (one user is recommended to correspond to one default tablespace ). The command is as follows:

-----------
SQL> select table_name from user_tables where NUM_ROWS = 0;
-----------


Based on the above query, you can build a command statement for allocating space to empty tables, as follows:

-----------
SQL> Select 'alter table' | table_name | 'allocate extent; 'from user_tables where num_rows = 0
-----------


Output the SQL statements generated in batches and create C: \ createsql. SQL. The content is as follows:

-----------
Set heading off;
Set echo off;
Set feedback off;
Set termout on;
Spool C: \ allocate. SQL;
Select 'alter table' | table_name | 'allocate extent; 'from user_tables where num_rows = 0;
Spool off;
-----------


Run C: \ createsql. SQL. The command is as follows:
-----------
SQL> @ C: \ createsql. SQL;
-----------

After execution, the C: \ allocate. SQL file is obtained.

Open the file and you will see that you have obtained the SQL statement for allocating space to all empty tables.


3.4 run the SQL command to allocate space for empty tables:

Run C: \ allocate. SQL. The command is as follows:
-----------
SQL> @ C: \ allocate. SQL;
-----------

The execution is complete and the table has been changed.


3.4 execute the exp command to export all tables including empty tables.