Page 322
There should be one record for each distinct FILE_NAME. If there is not, your tablespace is fragmented. The tables contained in the tablespaces will be made unavailable to the users during the export and import process. Before you rely on the export and import tablespace to defragment the tablespace, you should enter the command ALTER TABLESPACE tablespace_name COALESCE and run CHP13_1.SQL again. If two chunks of free space are adjacent to each other, this command makes them into one bigger chunk. If the command does not help, you must use Export and Import to defragment the tablespace.
Another reason to defragment a tablespace is if an object within the tablespace contains multiple extents. In most cases, you should be concerned if the object has more than five extents, after which point performance starts to get noticeably affected. By exporting the tables with COMPRESS=Y specified, Oracle calculates the size of the INITIAL extent so that it encompasses all the data into one extent. This helps to defragment the database as well. Run CHP13_2.SQL to determine which objects have more than five extents, as shown in Listing 13.2.
Listing 13.2CHP13_2.sql Lists All Objects in the Database with More than Five Extents
SQL> START CHP13_2.SQL SQL> COLUMN SEGMENT_NAME FORMAT A25 SQL> SELECT OWNER, SEGMENT_NAME, EXTENTS 2 FROM ALL_SEGMENTS 3 WHERE EXTENTS > 5 AND 4 OWNER NOT IN (`SYS','SYSTEM') 5 ORDER BY EXTENTS After running the script, the server returns all objects with more than five extents: OWNER SEGMENT_NAME EXTENTS ---------------- ---------------------- -------- DEMO EMPLOYEE 6 DEMO JOB 9 DEMO DEPARTMENT 12 SQL>Before you defragment, make sure that the people will be affected are notified because the tables within the tablespace will become unavailable for use during this process. If possible, schedule the process during a time when few people are using the tables.
To use Export and Import to defragment a tablespace, follow these steps:
Page 323
CAUTION |
If you export with COMPRESS=Y, any LOB data that is exported will not be compressed; its original INITIAL and NEXT storage parameters remain unchanged. |
The preceding method requires the knowledge of all objects contained within a tablespace. In
a more time-consuming approach, you may defragment the entire database. This is done
by exporting the entire database, dropping and re-creating the database with the
CREATE
DATABASE command, and then importing the entire export file.
Many times, it is important to move objects among schemas. This would be the case for a developer who wishes to have a set of tables to test with, but does not want to affect data in the original schema. This is also useful to copy tables among instances.
First, export all objects for a given user account. Do this by specifying the OWNER parameter of the Export utility. In the example here, you will be copying the DEMO schema into the QUE schema, as shown in Figure 13.6.
FIG. 13.6
Exporting all objects of
the DEMO user
account.
Page 324
Next, create the user account that will be the new owner, if that account does not already
exist. At this point, you can import into the new schema by specifying the
FROMUSER and TOUSER
parameters with the Import utility, as shown in Figure 13.7.
FIG. 13.7
Importing all objects of
the DEMO user account
into the QUE user
account.
If any objects being imported are in conflict with existing objects, only the conflicting objects are skipped.
TIP |
If you have BFILE datatypes, Oracle stores only pointers to the files themselves. The actual data is external to the database. If you are exporting from one database and importing into another on a different server, be sure to copy all needed external files and place them in the same directory path. Otherwise, Oracle cannot access the BFILE's associated files. |
If you want to move a subset of tables from one schema to another, or for backup purposes, you need to specify the list of objects in the TABLES parameter specification during the export. This must be used in conjunction with the FROMUSER and TOUSER for the import.
To export a table, specify the owner, followed by a period and the table name, such as Owner.Table_Name. To export a partition, specify the owner, followed by a period and the table name, followed by a colon, followed by the partition name, such as Owner.Table_Name:Partition_Name.
Figure 13.8 shows an example of exporting multiple objects and multiple object types: the PRICE table, along with two of five partitions for the EMP table (LOW_SALARY and MEDIUM_SALARY).
To import the multiple objects and object types into a different user account, import specifying the FROMUSER and TOUSER clauses. If you wish to import into the same user, use either the FULL=Y or OWNER parameter specification.
Page 325
When an object is imported, Oracle attempts to create it within the same tablespace from which it was exported. Sometimes, when an export is performed on one database, and imported into another database, the tablespaces do not always match. An object from the exported database was stored in the DBA_TOOLS tablespace, for example. In the database to which the object is being imported, there is no DBA_TOOLS tablespace.
In such a scenario, during the Import process, the Import utility attempts to create the object in the DBA_TOOLS but fails because there is no such tablespace in the target database. Import then tries to create the table into the default tablespace for the owner of the object. If there is enough space, and the owner has an appropriate quota on the tablespace, the object is imported. Otherwise an error occurs and the object is not imported.
By default, Import attempts to create objects into the same tablespace from which they were exported. If the user does not have permission to that tablespace, or that tablespace no longer exists, Oracle creates the database objects into the default tablespace for that user account. These properties may be utilized to move database objects from one tablespace to another using Export and Import. To move all objects from TABLESPACE_A to TABLESPACE_B for USER_A, follow these steps: