begin
quest_spc_find_long.execute (schema=>'SCHEMA', include_sys=>'NO', tablename=>'TABLENAME');
end;
/
The Find Long LONGs procedure can scan an entire database, a certain schema, or a certain table. You specify what to scan using the parameters described below. When no parameters are used, the procedure scans the entire database (with the exception of SYS and SYSTEM objects):
To populate the Find Long LONGs table
Run the Find Long LONGs Execute procedure for all tables in a database, for a certain schema, or for a certain table using parameters as follows:
To scan all tables in a database, run the procedure using no parameters or all parameters (if you don’t use any parameters, SYS and SYSTEM tables are excluded by default):
begin
quest_spc_find_long.execute;
end;
/
or
begin
quest_spc_find_long.execute (schema=>'ALL', include_sys=>'NO', tablename=>'ALL');
end;
/
To scan all tables in a certain schema, run the procedure using the schema parameter:
begin
quest_spc_find_long.execute (schema => 'SCHEMA');
end;
/
To scan all tables with a certain name, run the procedure using only the tablename parameter:
begin
quest_spc_find_long.execute (tablename=> 'TABLENAME');
end;
/
To scan a certain table in a certain schema, run the procedure using the schema and tablename parameters:
begin
quest_spc_find_long.execute (schema=>'SCHEMA', tablename=> 'TABLENAME');
end;
/
Tip: To include SYS and SYSTEM tables in a scan, run the procedure with the include_sys parameter set to YES as follows: include_sys=>'YES'
To provide you with feedback on the results of a scan, the Find Long LONGs procedure includes several messages. When a messages applies, it is displayed at the bottom of the SQL Editor after a scan is complete.
When a scan finds one or more tables with long LONGs, the following message alerts you to this and recommends that you check the Find Long LONGs table to identify these tables:
“ORA-20120: Longs that cannot be reorganized with PL/SQL found. Check QUEST_SPC_LONG_TABLES to see which tables cannot be reorganized using PL/SQL. These tables will have a YES value in the MUST_USE_FASTCOPY column. Fastcopy (or DBMS_DataPump) needs to be used.”
When a scan does not find any tables with long LONGs, the following message is displayed:
“ORA-20121: No tables with long LONG columns found”
When one or more tables is not accessible during a scan, the following message alerts you to this (a table is not accessible if its tablespace is offline):
“ORA-20122: Table(s) not accessible to scan”
Query the Find Long LONGs table
You can query the Find Long LONGs table to identify tables that contain long LONGs. This information is displayed in the MUST_USE_FASTCOPY results column in the Results tab of the SQL Editor:
- YES is displayed for tables with LONG or LONG RAW columns that are larger than 32,760 bytes (long LONGs). These tables must be reorganized with FastCopy (or DBMS_DataPump). (YES is also displayed for empty tables.)
- NO is displayed for tables with LONG or LONG RAW columns that are 32,760 bytes or less in size. These tables can be reorganized with either FastCopy/DBMS_DataPump or SQL (PL/SQL is used).
Please note that the Find Long LONGs table only contains data on tables that have been scanned with the Find Long LONGs procedure.
Note: To identify tables with long LONGs from reorg wizards, check the Long LONG Columns field in Customize Object Allocations windows. This field displays Yes for tables that contain long LONGs, No for tables that do not contain long LONGs, and Unknown for tables that contain LONGs but have not been scanned with the Find Long LONGs procedure. The information displayed is based on data in the Find Long LONGs table.
How to query the Find Long LONGs table
- Select File | New Script to open the SQL Editor.
Query the QUEST_SPC_LONG_TABLES table with a SELECT statement as follows:
To find all records in the Find Long LONGs table, run:
Select * from quest_spc_long_tables;
To find records only for tables that contain LONGs > 32K, run:
Select * from quest_spc_long_tables where MUST_USE_FASTCOPY = ‘YES’;
To find records only for tables that contain LONGS < 32K, run:
Select * from quest_spc_long_tables where MUST_USE_FASTCOPY = ‘NO’;
View results of the query in the Result tab of the SQL Editor. The MUST_USE_FASTCOPY column identifies which tables contain long LONGs:
- YES is displayed for tables with LONG or LONG RAW columns that are larger than 32,760 bytes (long LONGs). These tables must be reorganized with FastCopy (or DBMS_DataPump). (YES is also displayed for empty tables.)
- NO is displayed for tables with LONG or LONG RAW columns that are 32,760 bytes or less in size. These tables can be reorganized with either FastCopy/DBMS_DataPump or SQL (PL/SQL is used).
Defragment Tablespaces and Coalesce Free Space
Defragment Tablespaces
Options for defragmenting a tablespace allow you to merge free extents that are scattered throughout a tablespace. These include free extents that are next to each other and free extents that are separated by data extents. After a tablespace is defragmented, all free space is contiguous and coalesced into larger extents.
Tip: To check the degree of fragmentation in a tablespace, use a Datafile Map. See Monitor Datafile Storage for more information.
The sections that follow cover how to defragment a tablespace during a standard reorganization with Reorg Manager. The basic steps are to include all tablespace objects and select an interim tablespace as the target tablespace. Please note that a defragment option is not displayed in the Reorg Manager interface.
Selecting all objects and the interim option causes a tablespace to be defragmented as follows:
- All data is moved out of the tablespace on a temporary basis.
- All objects in the tablespace are dropped.
- All free space in the tablespace is coalesced.
- All objects in the tablespace are recreated and repopulated with data.
Note: Be aware that if objects remain in a tablespace during reorganization, the tablespace is not completely defragmented. Also note that it should not be dropped and recreated. See Empty a Tablespace Before You Defragment for more information.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
