Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ Oracle 11g can not export a variety of empty table solution     - Linux AS4 VPN server in conjunction with a firewall perfect (Linux)

- Linux operating system buffer overflow attacks Countermeasures (Linux)

- Mount and unloading disks under Linux (Linux)

- Tomcat itself through simple movement separation (Server)

- Python-- for anomalies and reflection of objects articles (Programming)

- Upload the project to GitHub, synchronous remote repository Github (Linux)

- MySQL simple operation notes under Linux (Database)

- Oracle to read and modify the data block process (Database)

- JavaScript subarray Deduplication (Programming)

- Analysis of potential problems through custom Orabbix monitoring Oracle (Database)

- How to become a better Node.js developers in 2016 (Programming)

- Linux source code analysis tool (Linux)

- Ubuntu install perfectly handsome terminal Guake 0.8.1 (Linux)

- Ten linux version of the system (Linux)

- C language programming entry - Common operators (Programming)

- Linux keyboard recording script (Linux)

- Copy and paste in Linux terminal and Vim (Linux)

- How to install the Linux text editor Atom 0.124.0 (Linux)

- Use Bash script write CVS version control (Server)

- MySQL to manage multiple instances of method (Database)

  Oracle 11g can not export a variety of empty table solution
  Add Date : 2018-11-21      
  Oracle 11g empty table can not export the problem:

Data found Oracle 11g has a new feature: Added a parameter "deferred_segment_creation" meaning that the segment delay to create, the default is true.

What does that mean?

Deferred_segment_creation, that is, when the establishment of the table, the table will not immediately allocate extent, which is not accounted for data space, that is, the table does not allocate segments to save space. The data structure is recorded directly in the dictionary. And only when there is real data when the allocation of space. This approach is very effective for deploying tens of thousands of tables as large as SAP. The default is turned on for the ture, need to close alter system set deferred_segment_creation = false;

In the system table user_tables can also be seen in the segment_treated field is "NO" or "YES" indicates that a table is allocated segment.

With the following SQL statement query, you can find the table does not export its segment_created field value is 'NO'.

1 Select segment_created, table_name from user_tableswhere segment_created = 'NO';


1, the most primitive of the most stupid way (not recommended): insert his party, and then rollback or deleted on the creation of a segment.

The method is to insert the data in the empty table, and then delete, then segment. When exporting, you can export an empty table.

2, set deferred_segment_creation parameters:

Set the deferred_segment_creation parameter to FALSE to disable "segment deferred creation" (that is, to create segments directly), whether segmented or non-empty.

In sqlplus, the implementation of the following order:

SQL> alter system setdeferred_segment_creation = false;


SQL> show parameterdeferred_segment_creation;

Note: This value is set only after the new table has an effect on the previously established empty table (already exists) does not work, still can not export.

And to restart the database, so that parameters take effect.

3, the use of ALLOCATE EXTENT, can be derived before the already empty table.

Use ALLOCATE EXTENT database object can be assigned to each table Extent (note for each table, that is, a table requires a SQL code), but if each table to write a statement, then too much trouble, in order to facilitate our Use the SQL command to spell out the alter statement for each table.

Build SQL commands to allocate space to empty tables.

Queries the current user under all the empty table (a user corresponds to a default table space). The commands are as follows:

SQL> select table_name from user_tableswhere NUM_ROWS = 0;

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

SQL> Select 'alter table' || table_name || 'allocate extent;

The above code can generate batch SQL statements to modify the extent of the table (the number of empty tables on the number of generated), we only need to generate all of the sql code to execute, you can already exist for each table to allocate segment, OK the.

4, the implementation of the allocation of space on the SQL table space orders.

Alter table WJDCPERSON allocate extent;

Alter table VERSION_NUMBER allocate extent;

5, the implementation of the above command, and then export the way to export the database, you can complete the export, including the empty table database.
- CentOS 7.0 Automatic installation CD-ROM production Comments (Linux)
- Linux Network Programming - raw socket programming (Programming)
- jQuery plugin dynamic label generation (Linux)
- APT-mirror using a four-step configuration Ubuntu local depot (Linux)
- Oracle create user authorization and in PLSQL (Database)
- Debian 7.6 install Nvidia graphics driver (Linux)
- Setting up Linux machine through a proxy firewall (Linux)
- Analysis JavaBean (Programming)
- Boot-Repair Tool - repair of frequent start-up problems (Linux)
- Json data with double backslashes to a single backslash Json data processing (Programming)
- How to install Eclipse Luna IDE on CentOS 7 / RHEL 7 (Linux)
- How to use Aptik to backup and restore Apps/PPAs under ubuntu (Linux)
- Use cmake to compile and install MySQL 5.5 (Database)
- Cool Android realization SVG animation (Programming)
- ASM required disk format process in Solaris platforms (Linux)
- Oracle database file path changes (Database)
- Linux system started to learn: how to view the Linux thread of a process (Linux)
- Linux CPU Monitoring Index (Linux)
- How to install Git client in Ubuntu (Linux)
- Spring declarative transaction management (Programming)
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.