Oracle9i

2.1.1 PL/SQL Gateway

In Appendix A, "Oracle9i Components," in Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems, PL/SQL Gateway is listed as a supported product. It has been desupported for this release.

2.1.2 Oracle Messaging Gateway

The Oracle Messaging Gateway section of Chapter 4, Post-Installation, pages 4-24 to 4-26 in Oracle9i Installation Guide Release 2 (9.2.0.1.0) for UNIX Systems, has examples that are incorrect.

In this section, make the following corrections:

• Remove all mentions of $ORACLE_HOME/mgw/lib and $ORACLE_HOME/mgw/lib32 in the examples.Replace all references to LD_LIBRARY_PATH_32 with LD_LIBRARY_PATH.Substitute all references to $ORACLE_HOME in the examples with the actual directory path.

3.5.1 SYS and SYSTEM Password Change Requirement

If you use Database Configuration Assistant to create a database, be aware that you will be required to change the SYS and SYSTEM passwords at the end of the configuration process. This is a new security procedure designed to protect access to your data.

3.6.1 Upgrading to Oracle9i Release 2 (9.2.0.1.0)

This section provides information about upgrade options for different Oracle versions.

Upgrading directly during installation is possible for the following Oracle RDBMS version:

• Oracle8i version 8.1.7 (32-bit) on AIX5L

Upgrading directly during installation is possible for the following Oracle RDBMS version if you upgrade the OS from AIX 4.3.3 to AIX 5L:

• Oracle8i version 8.1.7 (32-bit) on AIX 4.3.3

Upgrading directly during installation is possible if you follow the instructions for upgrading the OS first in the section, "Upgrading OS from 4.3.3 to 5L with Oracle8i version 8.1.7 (64-bit)"

• Oracle8i version 8.1.7 (64-bit) on AIX 4.3.3

You must first upgrade the following RDBMS versions to an intermediate Oracle version before upgrading to Oracle9i release 2 (9.2.0.1.0):

• Oracle8 release 8.0.6 (32-bit and 64-bit) on AIX 4.3.3: Upgrade either to Oracle9i release 2 (9.2.0.1.0) on AIX 4.3.3, and then follow the procedure in the section, "Upgrading from 4.3.3 to 5L with Oracle9i", following this option overview, or upgrade to Oracle8i version 8.1.7 (32-bit or 64-bit) on AIX 4.3.3. If you upgrade to Oracle8i version 8.1.7 (64-bit), then you must next follow the procedure in the section "Upgrading OS from 4.3.3 to 5L with Oracle8i version 8.1.7 (64-bit)" following this option overview.

• Oracle8i release 3 (8.1.7) (32-bit) on AIX 4.3.3: Upgrade to one of the following:

  • Oracle9i release 2 (9.2.0.1.0) on AIX 4.3.3, and then follow the procedure in the section "Upgrading from 4.3.3 to 5L with Oracle9i" following this option overview,

  • Oracle8i version 8.1.7 (64-bit) on AIX 4.3.3, and then follow the procedure in the section "Upgrading OS from 4.3.3 to 5L with Oracle8i version 8.1.7 (64-bit)" following this option overview

  • Upgrade directly during installation as mentioned in the preceding section.

• Oracle9i release 1 (9.0.1) (64-bit) on AIX 4.3.3: Upgrade to Oracle9i release 2 (9.2.0.1.0) on AIX 4.3.3, and then follow the procedure in the section "Upgrading from 4.3.3 to 5L with Oracle9i" following this option overview.

Upgrading directly from Oracle8i version 8.1.7 (64-bit) on AIX 4.3.3 to Oracle9i version 9.2.0.1.0 on AIX 5L during installation is supported if you upgraded the OS to AIX 5L before installation. Complete the following procedure prior to upgrading the operating system from AIX 4.3.3 to AIX 5L:

1. Perform complete backups of the existing 8.1.7.x.x (64-bit) database on AIX 4.3.3 to protect against any failures in the upgrade.

2. Add space to your SYSTEM tablespace and to the tablespaces where you store rollback segments, if necessary. Oracle9i Database Migration Release 2 (9.2) details upgrade requirements.

3. Perform a clean Oracle database shutdown.

4. Upgrade the AIX 4.3.3 operating system to AIX 5L, using the instructions in AIX 5L Version 5.1 Installation Guide.

   
   

   Note: After you complete this step and you are running on AIX5L, if you previously used Oracle8i release 3 (8.1.7) (64-bit), then you will no longer be able to start the RDBMS. You must first complete the migration of the Oracle8i RDBMS to Oracle9i release 2 (9.2.0.1.0).

   
   

5. Install the Oracle9i release 2 (9.2.0.1.0) software for AIX 5L in a new ORACLE_HOME .

6. Copy your existing initsid.ora file to the new ORACLE_HOME. Change any AIX 4.3.3 ORACLE_HOME path references to use the new AIX 5L ORACLE_HOME path. For information on how to adjust your parameter file for use with Oracle9i release 2 (9.2.0.1.0), refer to Appendix A, "Changes to Initialization Parameters and the Data Dictionary" in Oracle9i Database Migration Release 2 (9.2) manual.

7. Upgrade the database manually by following the procedures in Oracle9i Database Migration Release 2 (9.2) manual.

8. Copy the SQL*Net *.ora files from the old $ORACLE_HOME/network/admin directory to the new one. You have to adjust ORACLE_HOME in the listener.ora file.

If you choose to upgrade to Oracle9i release 2 (9.2.0.1.0) on AIX 4.3.3, then use the 4.3.3 documentation to upgrade to Oracle9i release 2 (9.2.0.1.0) on AIX 4.3.3. When you complete this upgrade, then use the following procedure to upgrade to Oracle9i release 2 (9.2.0.1.0) on AIX5L:

1. Perform complete backups of the existing 9.2.0.x.x database on AIX 4.3.3 to protect against any failures in the upgrade.

2. In order to create the control file after the upgrade, save control file information to a trace file, using the following SQL command:

   SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
   
   

   Entering the preceding command saves the control file information under the UDUMP directory. Database names, paths, and parameter values will vary, depending on your system values. The following is an example of how the information should appear:

   CREATE CONTROLFILE REUSE DATABASE "SAMPLE" NORESETLOGS
   NOARCHIVELOG
     MAXLOGFILES 32
     MAXLOGMEMBERS 2
     MAXDATAFILES 32
     MAXINSTANCES 1
     MAXLOGHISTORY  112
   LOGFILE
     GROUP1 ’/aix433_path/oracle/dbs/t_log1.f SIZE 25M,
     GROUP2 ’/aix433_path/oracle/dbs/t_log2.f’ SIZE 25M
   DATAFILE
     ’/aix433_path/oracle/dbs/t_DB1.F’
   CHARACTER SET WE8DEC;
   
   

3. Perform a clean Oracle database shutdown.

4. Upgrade the AIX 4.3.3 operating system to AIX 5L, using the instructions in AIX 5L Version 5.1 Installation Guide.

   
   

   Note: Do not attempt to use Oracle9i for AIX 4.3.3 after booting the system with AIX 5.1. Database files created with Oracle9i release 2 (9.2.0.1.0) on AIX 4.3.3 are compatible with Oracle9i release 2 (9.2.0.1.0) databases on AIX 5L. If you need to transfer data from raw devices on 4.3.3 to 5L, then refer to the section following this one, "Moving Database Files on Raw Devices from AIX 4.3.3 to AIX 5L Systems with the Offset Utility."

   
   

5. Install the Oracle9i release 2 (9.2.0.1.0) software for AIX 5L in a new ORACLE_HOME.

6. Copy your existing initsid.ora file to the new ORACLE_HOME. Change any AIX 4.3.3 ORACLE_HOME path references to use the new AIX 5L ORACLE_HOME path.

7. Start up the database, using SQL commands similar to the following syntax example (Database names, paths, and parameter values will vary, depending on your system values):

   STARTUP NOMOUNT;
   CREATE CONTROLFILE REUSE DATABASE "SAMPLE" NORESETLOGS
     MAXLOGFILES 32
     MAXLOGMEMBERS 2
     MAXDATAFILES 32
     MAXINSTANCES 1
     MAXLOGHISTORY 112
   LOGFILE
     GROUP 1 ’/aix51_path/oracle/dbs/t_log1.f’ size 25M
     GROUP 2 ’/aix51_path/oracle/dbs/t_log2.f’ size 25M
   DATAFILE
     ’aix5l_path/oracle/dbs/t_dbl.f
   CHARACTER SET WE8DEC;
   ALTAR DATABASE OPEN
   

   
   

   Note: At this point, the natively compiled Java objects in the migrated database are invalid. Refer to the section "Steps to Update Natively Compiled Java Objects in the Database" for the procedure to revalidate these objects.

   
   

8. Perform a clean shutdown of the database.

9. Perform complete backups of the Oracle9i release 2 (9.2.0.1.0) for AIX 5L database.

3.6.2 Moving Database Files on Raw Devices from AIX 4.3.3 to AIX 5L Systems with the Offset Utility

Use this section if you change the disk layout, or if you move datafiles from one system to another.

To move database files from an AIX 4.3.3 raw device, you must find the control block size of the device, and you must locate "offset" bytes, which indicate the location of Oracle data on the device.

To assist in this task, Oracle Corporation ships the offset utility with Oracle9i release 2 (9.2.0.1.0).

If you use the offset utility, then complete the following steps:

1. Run $ORACLE_HOME/bin/offset utility on the AIX 4.3.3 database file (the "source" file).

2. Copy the database data on the source file to a temporary location, such as an NFS filesystem, which is accessible to the AIX 5L systems or cluster nodes, using the following command syntax:

   dd if=source_file of=temp_path bs=value skip=offset_value_source
   
   

   where:

   • The variable source_file represents the full path of the database source file

   • The variable temp_path represents the full path to the temporary location of the source file

   • The variable value is the block size value

   • The variable offset_value_source represents the value returned by the offset program for the database source file, divided by the bs value.

3. Run $ORACLE_HOME/bin/offset on the destination database file, using the following command syntax:

   offset dest_file
   
   

   where the variable dest_file represents the full path of the destination file on the AIX 5L node (the "target" file)

4. Copy the database data from the source file on the temporary location to the target file, using the following command syntax:

   dd if=temp_path of=dest_file bs=value seek=offset_value_target
   
   

   where:

   • The variable temp_path represents the full path to the temporary location where the source file is placed

   • The variable dest_file represents the full path to the target file

   • The variable value is the block size value

   • The variable offset_value_target represents the value returned by the offset program for the database target file.

3.6.3 Steps to Update Natively Compiled Java Objects in the Database

In order to use natively compiled Java objects for AIX5L, use the following procedure to update compiled Java objects in the database:

1. Connect to the database as the user SYSTEM

2. Run the following command

   SQL> create or replace java system;
   

3.6.4 Migrating Agent Repository Files from AIX 4.3.3 to AIX 5L

This release does not support migrating agent files from AIX 4.3.3 (32-bit) to AIX 5L (64-bit) using the nmumigr8 utility. Oracle Corporation assigned Oracle Bug identification number 2237433 to track this problem.

4.1.1 Oracle9i NCHAR Datatypes

Oracle9i release 2 (9.2.0.1.0) limits the SQL NCHAR datatypes to the Unicode character set encoding (UTF8 and AL16UTF16). Alternative character sets such as the fixed-width Asian character set JA16SJISFIXED in Oracle8i are no longer supported.

To migrate existing NCHAR, NVARCHAR, and NCLOB columns, export and import NCHAR columns, complete the following steps:

1. Export all SQL NCHAR columns from Oracle8i.

2. Drop the SQL NCHAR columns.

3. Migrate the database to Oracle9i.

4. Import the SQL NCHAR columns in to Oracle9i.

4.1.2 AL24UTFFSS Character Set

Oracle9i release 2 (9.2.0.1.0) does not support the Unicode character set AL24UTFFSS introduced in Oracle7. This character set was based on the Unicode standard 1.1, which is now obsolete.

Oracle9i release 2 (9.2.0.1.0) supports the Unicode database character sets AL32UTF8 and UTF8. These database character sets include the Unicode enhancements based on the Unicode standard 3.0.

To migrate the existing AL24UTFFSS database, upgrade your database character set to UTF8 before upgrading to Oracle9i. Oracle Corporation recommends that you use the Character Set Scanner for data analysis before attempting to migrate your existing database character set.

4.1.3 Character Set Scanner

Set the LIBPATH variable to include the $ORACLE_HOME/lib directory before running the Character Set Scanner (csscan) from the $ORACLE_HOME directory. If you do not correctly set the LIBPATH variable, then the csscan utility will fail.

4.4.1 Starting Up OID Server

By default, the OID server is started on port 389. If this port is unavailable, then OID server is started on a different port, which is logged in the following file:

$ORACLE_HOME/ldap/install/oidca.out

4.4.2 Custom Installation and Global Database Name

When performing a custom Oracle Internet Directory installation, do not change the global database name or the Oracle SID.

4.4.3 Upgrade from Enterprise Edition Oracle9i or Oracle8i

If you have installed in the same ORACLE_HOMEeither Oracle Internet Directory release 3.0.1.x and the complete release of Oracle9i (9.0.1) Enterprise Edition, or Oracle Internet Directory 2.1.1.x and the complete release of Oracle8i (8.1.7) Enterprise Edition, then you must first upgrade Oracle Internet Directory to the release 9.2.0.x.x version, and then upgrade as a separate step either Oracle9i Enterprise Edition release 1 (9.0.1) or Oracle8i release 3 (8.1.7) to Oracle9i Enterprise Edition release 2 (9.2.0.x.x).

4.5.1 Restrictions for Installing Real Application Clusters

If you are installing Oracle9i release 2 (9.2.0.1.0) Real Applications Clusters on a cluster that already contains an ORACLE_HOME for a previous release of Real Application Clusters, then you must run the Oracle Universal Installer from the cluster node with the oraInventory installation registry. Doing this ensures that product installation inventories are synchronized on the nodes with information about existing ORACLE_HOME directories.

4.5.2 Additional Preinstallation Requirements for Real Application Clusters

The following requirements are in addition to those documented in the installation guide:

• The user id (UID) of the oracle user needs to be less than 65536 for Real Application Cluster installs.

• You need to create the directory /var/opt/oracle. This directory path is required for the srvConfig.loc file, which the Installer creates during Real Application Clusters installation and configuration.

4.5.3 Real Application Clusters Custom Installation Requirements

Complete the following tasks if you install Oracle Real Application Clusters:

4.5.4 Additional Parameter Configuration for Real Application Clusters

To run Oracle Real Application Clusters, you must increase the UDP send buffer size to at least 32k (32768 bytes). To do this, become root and enter the following command:

$ /etc/no -udp_sendspace=32768

You must run this command after every system reboot. Review the current setting of the UDP send buffer size with the following command:

$ /etc/no -a|grep udp_sendspace

The output lists the current udp_sendspace setting.

4.5.5 Real Application Clusters and Database Upgrade Assistant

If you use Database Upgrade Assistant to upgrade an earlier Oracle database version (the "source" database) to Oracle9i release 2 (9.2.0.1.0) (the "target" database), then the upgraded database will always use the server parameter file SPFILE by default to store init.ora file parameters. If the source database also uses SPFILE (either a cluster filesystem file or a shared raw device), then the upgraded target database also uses the same SPFILE.

If the source database does not use an SPFILE, then the target database uses a default server parameter file, spfile.ora, which is located in the $ORACLE_HOME/dbs/ directory.

If your platform does not support a cluster file system, then you must move the SPFILE to a shared raw device, using the following procedure:

1. Create an SPFILE with the following commands:

   $ sqlplus "/ as sysdba"
   SQL> create pfile='?/dbs/initdbname.ora' from spfile='?/dbs/spfile.ora';
   SQL> create spfile='/dev/oracle_dg/dbname_spfile' frompfile='?/dbs/initdbname.ora';
   SQL> exit;
   
   

   where the variable dbname is the name of your cluster database.

2. Go to the $ORACLE_HOME/dbs directory using the following command:

   $ cd $ORACLE_HOME/dbs
   
   

3. Create an $ORACLE_HOME/dbs/initsid.ora file, where sid is the system identifier of the instance on the node. The initsid.ora file must contain the following line:

   SPFILE='/dev/oracle_dg/dbname_spfile'
   
   

4. Copy the initsid.ora file to the remote nodes on which the cluster database has an instance using the following commands:

   $ rcp initsid.ora nodex:$ORACLE_HOME/dbs/initsidx.ora
   
   

   where the variable sidx is the system identifier of the instance on node x. Repeat the preceding rcp command for each member node of the cluster database.

5. Restart the cluster database using the following command syntax:

   $ srvctl stop database -d dbname
   $ srvctl start database -d dbname
   
   

4.5.6 Real Application Clusters and Database Configuration Assistant

The following section provides information on using Database Configuration Assistant (DBCA) to create a Real Application Clusters database.

4.5.7 Placing Datafiles On a Shared Non-OFA Cluster Configuration

If your ORACLE_HOME directory is not on a shared cluster filesystem partition, but you want to place datafiles, controlfiles, redo log files, or other database files on a shared cluster filesystem partition, then invoke DBCA using the following syntax to create the cluster database:

$ dbca -datafileDestination pathname

where the variable pathname is the location where you want files to be placed.

For example, to place datafiles in the path /ora/oradata, give the following command:

$ dbca -datafileDestination /ora/oradata

4.5.8 Real Application Clusters Instance Management

After you created a cluster database using DBCA, SYSDBA privileges are revoked for all users. As SYSDBA, you must grant SYSDBA privileges explicitly to the database user account that you plan to use for adding or deleting an instance to or from the cluster database.

For example, to grant SYSDBA privileges to the administrative user SYS, issue the following commands:

$ sqlplus "/ as sysdba"
SQL> grant sysdba to sys;
SQL> exit;

4.5.9 Oracle Real Application Clusters and the IBM SP System

To ensure optimal performance of Oracle Real Application Clusters on an RS/6000 node cluster using IBM’s SP (Scalable POWERparallel) System, follow IBM's tuning instructions, which are available at the following Web sites:

http://www.rs6000.ibm.com/support/sp/perf

http://www.ibm.com/servers/eserver/pseries/library/wp_clustering.html

4.5.10 IBM GPFS and Real Application Clusters

The following is a list of additional system requirements to use General Parallel File System (GPFS) with Real Application Clusters:

• GPFS version 1.5

• IBM patch PTF 12 and IBM patch IY34917

  OR

  IBM patch PTF 13

Patch fix IY34917 can be downloaded from the following Web site:

http://techsupport.services.ibm.com/server/aix.CAPARdb

In addition, IY34917 is available through the following methods:

• Downloading from other regular fix distribution websites.

• Calling 1-800-CALLAIX, and requesting e-fix IY34917.

4.5.11 Oracle Real Application Clusters and Fault-Tolerant IPC

Oracle9i release 2 (9.2.0.1.0) supports the use of fault-tolerant interprocess communication (FT-IPC) with Real Application Clusters (RAC) installations on AIX 5L.

To use FT-IPC with RAC, complete the following tasks:

1. Install APAR patch IY31733, and IY30983 on all nodes of the cluster. Obtain the patch from the following Web site:

   http://techsupport.services.ibm.com/server/aix.fdc51
   
   

2. Run the following command:

   $ /usr/bin/odmget HACMPnode
   
   

   Check the values assigned to the parameters node_id and node_handle.

   If the parameter values are the same, then your system is configured to use FT-IPC with RAC. If the parameter values are different, then further configuration is required. Refer to Oracle bug number 2438232 for additional configuration requirements.

4.6.1 Determining Whether to Use PL/SQL Native Compilation

You must first determine if you will increase performance by enabling PL/SQL native compilation.

For each program unit, interpreted PL/SQL is compiled into machine-readable m-code, which is stored in the database, and interpreted at runtime.

For PL/SQL statements using PL/SQL native compilation, Oracle9i takes PL/SQL statements and generates corresponding C-code. Oracle9i then uses the makefile, $ORACLE_HOME/plsql/spnc_makefile.mk, and the supported operating system C compiler, linker, and make utilities, to compile and link the resulting C-code into shared libraries, which are stored externally from the database. These shared library files are then loaded and run when the corresponding PL/SQL statement is invoked at runtime. In accordance with OFA recommendations, the shared libraries should be stored near the data files.

C-code runs faster than PL/SQL, but it takes longer to compile than m-code.

PL/SQL native compilation provides the greatest performance gains for computation-intensive procedural operations. Examples of such operations are data warehouse applications, and applications with extensive server-side transformations of data for display. In such cases, expect speed increases of up to 30%.

For PL/SQL program units that merely invoke SQL statements, and do not implement significant procedural logic, the performance benefits of native compilation will be small. However, natively compiled PL/SQL will always be at least as fast as the corresponding interpreted code.

When you have decided that you will have significant performance gains in database operations using PL/SQL native compilation, Oracle Corporation recommends that you compile the whole database as NATIVE.

4.6.2 Required Parameters for PL/SQL Native Compilation

In all circumstances, whether you intend to compile a database as NATIVE, or you intend to compile individual PL/SQL units at the session level, you must set all required parameters.

SQL> alter dynamic set plsql_compiler_flags='FLAG_A, FLAG_B'

4.6.3 Setting Up Databases for PL/SQL Native Compilation

Use the procedures in this section to set up databases for PL/SQL native compilation.

select count (*) from DBA_OBJECTS 
  where object_type in ( 
    select distinct object_type from dba_stored_settings 
      where object_type not like '%BODY%' );

4.6.4 Real Application Clusters and PL/SQL Native Compilation

To use PLSQL Native Compilation in a Real Application Clusters environment on AIX-Based 5L Systems (64-bit), you need to set the PLSQL_NATIVE_LIBRARY_DIR initialization parameter to a directory that resides on a General Parallel File System (GPFS).

4.6.5 Dependencies, Invalidation and Revalidation

This release handles dependencies between database objects in the same manner as in previous Oracle RDBMS versions. If an object that a natively compiled PL/SQL program unit depends on changes, then the PL/SQL module becomes invalid. The next time you execute the same program unit, the RDBMS will attempt to revalidate the module. When you recompile a module as part of revalidation, it compiles using its stored setting (the setting in place when the module last compiled and appeared in the USER/ALL/DBA_STORED_SETTINGS data dictionary views).

The compilation process uses the stored settings only when it is recompiling as part of revalidation. If you explicitly compile a PL/SQL module through the SQL commands "create or replace" or "alter...compile", then it uses the current session setting.

Natively compiled PL/SQL program units are dependent on their implementation shared libraries. The RDBMS is unable to track deletions or location changes of these library dependencies, as the shared libraries are on the OS file system, external from the database.

If you delete or move a shared library, then you will see an ORA-06549 error. The program unit is not marked invalid, as the removal of a library is undetectable to the Oracle RDBMS until the module is executed. To recreate the missing library, you must explicitly recompile it, or recreate it from the source.

For example, if you deleted or moved the test program "Hello" in the shared library, use the following procedure to correct the problem:

$ sqlplus scott/tiger

SQL> alter session set plsql_compiler_flags='NATIVE'

Session altered

SQL> alter procedure Hello compile;

Procedure altered.

SQL> exit

$ ls /usr/app/oracle/product 9.2.0.1.0/plsql_libs

HELLO__SCOTT__0.so

If you delete a PL/SQL program unit on the Oracle RDBMS, the shared libraries on the OS file system remain; you must delete these files manually when they are no longer needed.

6.3.1 Segments and Compression Settings

To find out which database segments are using compression, log in to the database as the user SYS, and create the view all_segs with the following create or replace view statement:

SQL> create or replace view all_segs
     (owner, segment_name,
       partition_name, spare1
as
select u.name, o.name, o.subname, s.spare1
from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so,
     sys.seg$ s, sys.file$ f
where s.file# = so.header_file
   and s.block# = so.header_block
   and s.ts# = so.ts_number
   and s.ts# = ts.ts#
   and s.ts# = so.object_id
   and o.owner# = u.user#
   and s.type# = so.object_type_id
   and s.ts# = f.ts#
   and s.file# = f.relfile#
union all
select u.name, un.name, NULL, NULL
from sys.user$ u, sys.ts$ ts, sys.undo $ un, sys.seg$ s,
     sys.file$ f
where s.file# = un.file#
   and s.block# = un.block
   and s.ts# = un.ts#
   and s.ts# = ts.ts#
   and s.user# = u.user#
   and s.type# in (1, 10)
   and un.status$ != 1
   and un.ts# = f.ts#
   and un.file# = f.relfile#
union all
select u.name, to_char(f.file#)|| '.' || to_char(s.block#), NULL, NULL
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
where s.ts# = ts.ts#
   and s.user# = u.user#
   and s.type# not in (1, 5, 6, 8, 10)
   and s.ts# = f.ts#
   and s.file# = f.relfile#
/

After creating this view, you can issue queries against the view to find out whether a segment currently is compressed, as illustrated in the following examples:

• To determine if a segment is currently compressed, apply the following predicate in a query to the column spare1:

  bitand(spare1, 2048) > 0
  
  

  For example, to see if segments currently are compressed, issue a statement similar to the following:

  SQL> select * from all_segs where bitand(spare1,2048) > 0;
  
  

• To determine if a segment contains any compressed blocks, apply the following predicate in a query:

  bitand(spare1, 4096) > 0
  
  

  For example, to see which segments contain any compressed blocks, issue a statement similar to the following:

  SQL> select * from all_segs where bitand(spare1, 4096) > 0;
  
  

6.3.2 Tablespaces and Compression Settings

When you want to determine compression settings on a table space, log in as SYS, and create the view compression_ts with the following create or replace view statement:

SQL> create or replace view compression_ts (tablespace_name, flags) as select ts.name, ts.flags from
sys.ts$ ts where ts.online$ !=3;

After creating this view, you can issue queries against it to find out the compression state of tablespaces, such as determining if a tablespace is currently set as DEFAULT COMPRESS, or DEFAULT NOCOMPRESS, as illustrated in the following examples:

• To determine if a tablespace is currently set as DEFAULT COMPRESS, use the following predicate:

  bitand(flags, 64) > 0
  
  

  For example, to see which tablespaces are currently DEFAULT COMPRESS, issue a statement similar to the following:

  SQL> select * from compression_ts where bitand(flags, 64) > 0
  
  

• To determine if a tablespace is currently set as DEFAULT NOCOMPRESS, use the following predicate:

  bitand(flags, 64) == 0
  
  

  For example, to see which tablespaces are currently DEFAULT NOCOMPRESS, issue a statement similar to the following:

  select * from compression_ts where bitand(flags, 64) == 0;