OracleTechnical

Wednesday, September 7, 2011

How to set the Oracle database listener password

This short paper shows you how you can set a password for the Oracle listener. These passwords can be set either encrypted or unencrypted. Obviously I would not recommend setting clear text passwords. Whilst in some cases these can be made reasonably secure the problem is that a password is stored in clear text in a file. For completeness I will show both methods:

Setting an Oracle listener password in clear text

The listener password can be set in clear text as follows: (It should be noted that it is possible to set more than one listener password in this way.)

Open the $ORACLE_HOME/network/admin/listener.ora file and locate the name of your listener. For instance my listener has the default name LISTENER. I can see this in the following lines:

LISTENER =

(DESCRIPTION_LIST =

It is the line starting LISTENER= . Simply add a line to this file with the following format:

PASSWORDS_{LISTENER_NAME}=somesecretpassword

i.e in my case if the listener is called LISTENER then:

PASSWORDS_LISTENER=somesecretpassword

You can also set multiple passwords as follows:

PASSWORDS_LISTENER=(somesecretpassword,anothersecret)

Setting an encrypted Oracle listener password

Anyway as I said this is not the best way to secure your listener. It should be done with an encrypted password as follows:

C:\oracle\ora90\network\admin>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 24-FEB-2004 11:27:

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener listener

Current Listener is listener

LSNRCTL> change_password

Old password:

New password:

Reenter new password:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

Password changed for listener

The command completed successfully

LSNRCTL> set password

Password:

The command completed successfully

LSNRCTL> save_config

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

Saved LISTENER configuration parameters.

Listener Parameter File C:\oracle\ora90\network\admin\listener.ora

Old Parameter File C:\oracle\ora90\network\admin\listener.bak

The command completed successfully

LSNRCTL>

You must save the configuration after setting the password otherwise it will be lost. Also you can check what was generated by looking in the listener.ora file. This is what was generated from the above commands:

#----ADDED BY TNSLSNR 24-FEB-2004 11:29:18---

PASSWORDS_LISTENER = F0354118688257FB

#--------------------------------------------

Should you want to remove the listener password do the following:

C:\oracle\ora90\network\admin>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 24-FEB-2004 11:32:

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set password

Password:

The command completed successfully

LSNRCTL> stop

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

The command completed successfully

LSNRCTL>

Edit the listener.ora file to remove the lines added above and restart the listener as follows:

C:\oracle\ora90\network\admin>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 24-FEB-2004 11:33:

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> start

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production

System parameter file is C:\oracle\ora90\network\admin\listener.ora

Log messages written to C:\oracle\ora90\network\log\listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc

)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zulia)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Produc

tion

Start Date 24-FEB-2004 11:33:38

Uptime 0 days 0 hr. 0 min. 2 sec

Trace Level off

Security OFF

SNMP OFF

Listener Parameter File C:\oracle\ora90\network\admin\listener.ora

Listener Log File C:\oracle\ora90\network\log\listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zulia)(PORT=1521)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "sans" has 1 instance(s).

Instance "sans", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

LSNRCTL>

That's it!, you should always protect your listener with a password. This is a basic security requirement. If the listener is not password protected then it can be shutdown remotely or have its configuration change or could be used to hack your server

External Tables

Introduction

The following article provides an overview of the purpose and use of External Tables. The external tables feature was first introduced in Oracle9i and is a welcome addition for many DBAs and Developers. It is often a requirement in an application to store data outside of the database but have the ability to manipulate this data inside the database *without* requiring to manually load the data into the database.

Applications for External Tables

This type of requirement is often seen in data warehouse environments during the ETL process (Extraction Transformation Loading), often seen as a complement to SQL*Loader. This new feature prevents the need for temporary tables to be created during the Extraction and Transformation phases, thus reducing space allocated and risk of abortion during the entire process. External tables can be used in just about any application in place of SQL*Loader when the external data volume is large and seldom queried.

External Tables in a Nutshell

This section of the article attempts to explain just what is an external table and what are some of its limitations. External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. By providing Oracle with metadata describing an external table, Oracle is able to expose the data in the external table as if it were data residing in a regular database table. The external data can be queried directly and in parallel using SQL.
The following is a listing of some of the limitations to external tables:

The metadata of external tables is created using the SQL CREATE TABLE ... ORGANIZATION EXTERNAL statement.
The actual data resides outside the database in OS files, hence the clause ORGANIZATION EXTERNAL organization.
The OS files are identified inside the database through a logical directory defining the OS physical directory where they are located.
The data is read only.
You cannot perform any DML operations (INSERT, UPDATE, or DELETE), nor create indexes on external tables.
You can, although, create views and synonyms for external tables.
The external table can be queried and joined directly, in parallel using the SQL statement SELECT.
The ANALYZE statement is not supported for gathering statistics for external tables. The DBMS_STATS package, although, can be used for gathering statistics for external tables.

Creating External Tables
To create an external table, simply use the ORGANIZATION EXTERNAL clause of the CREATE TABLE statement. Keep in mind though that you are not creating a table; that is, an external table does not have any extents associated with it. Rather, you are creating metadata in the data dictionary that enables you to access external data.
The following example creates an external table, then uploads the data to a database table.

The file department.dat contains the following sample data:

100, "ACCOUNTING", "BUTLER, PA"
101, "RESEARCH", "DALLAS, TX"
102, "SALES" , "CHICAGO, IL"
103, "OPERATIONS", "BOSTON, MA"
104, "IT", "PITTSBURGH, PA"
105, "ENGINEERING", "WEXFORD, PA"
106, "QA", "WEXFORD, PA"
107, "PROCESSING", "NEW YORK, NY"
108, "CUSTOMER SUPPORT", "TRANSFER, PA"
109, "HQ", "WEXFORD, PA"
110, "PRODUCTION SUPPORT", "MONTEREY, CA"
111, "DOCUMENTATION", "WEXFORD, PA"
112, "HELP DESK", "GREENVILLE, PA"
113, "AFTER HOURS SUPPORT", "SAN JOSE, CA"
114, "APPLICATION SUPPORT", "WEXFORD, PA"
115, "MARKETING", "SEASIDE, CA"
116, "NETWORKING", "WEXFORD, PA"
117, "DIRECTORS OFFICE", "WEXFORD, PA"
118, "ASSISTANTS", "WEXFORD, PA"
119, "COMMUNICATIONS", "SEATTLE, WA"
120, "REGIONAL SUPPORT", "PORTLAND, OR"
"Error", "This is a bad record that should be discarded", "ANYWHERE, CA"

You will first need to come up with the specification for the external file using a format that it similar to the control file specification in the SQL*Loader utility. The following The first steps in creating the external table is to create the directory objects for the OS directories that contain the data sources. You should also create directories for the bad record and log files. Specify the directories and grants to the directories as follows:

CONNECT / as sysdba

    -- ============================================
    -- CREATE DIRECTORY OBJECTS FOR O/S DIRECTORIES
    -- ============================================
    CREATE OR REPLACE DIRECTORY data_dir AS  '/u02/app/external_files/data';
    CREATE OR REPLACE DIRECTORY log_dir  AS  '/u02/app/external_files/log';
    CREATE OR REPLACE DIRECTORY bad_dir  AS  '/u02/app/external_files/bad';

    -- ========================================
    -- GRANT REQUIRED PRIVILEGES TO DIRECTORIES
    -- ========================================
    GRANT READ ON DIRECTORY data_dir TO scott;
    GRANT READ ON DIRECTORY log_dir TO scott;
    GRANT READ ON DIRECTORY bad_dir TO scott;

The next step is to create the definition for the external file as follows:

CONNECT scott/tiger

    CREATE TABLE department_ext (
        dept_id    NUMBER(15)
      , name       VARCHAR2(100)
      , location   VARCHAR2(100)
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY data_dir
      ACCESS PARAMETERS (
        records delimited by newline
        badfile bad_dir:'department.bad'
        logfile log_dir:'department.log'
        fields terminated by ',' optionally enclosed by '"'
        missing field values are null
        (dept_id, name, location)
      )
      LOCATION ('department.dat')
    )
    REJECT LIMIT UNLIMITED
    /

If not specified, ORACLE_LOADER is the default access driver. The access parameters, specified in the ACCESS PARAMETERS clause, are opaque to Oracle. These access parameters are defined by the access driver, and are provided to the access driver by Oracle when the external table is accessed.

NOTE: Keep in mind that the ordering of parameters in the ACCESS PARAMETERS clause do matter. In particular, the records delimited by newline must come before both the badfile and logfile parameters.

The REJECT LIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data. For parallel access, this limit applies to each parallel execution server independently. For example, if REJECT LIMIT 10 is specified, each parallel query process is allowed 10 rejections. Hence, the only precisely enforced values for REJECT LIMIT on parallel query are 0 and UNLIMITED.

Querying External Tables

When you query the external table, ORACLE_LOADER is the default access driver. Here is an example of an external query:

SQL> select * from department_ext;

   DEPT_ID NAME                      LOCATION
---------- ------------------------- ---------------
       100 ACCOUNTING                BUTLER, PA
       101 RESEARCH                  DALLAS, TX
       102 SALES                     CHICAGO, IL
       103 OPERATIONS                BOSTON, MA
       104 IT                        PITTSBURGH, PA
       105 ENGINEERING               WEXFORD, PA
       106 QA                        WEXFORD, PA
       107 PROCESSING                NEW YORK, NY
       108 CUSTOMER SUPPORT          TRANSFER, PA
       109 HQ                        WEXFORD, PA
       110 PRODUCTION SUPPORT        MONTEREY, CA
       111 DOCUMENTATION             WEXFORD, PA
       112 HELP DESK                 GREENVILLE, PA
       113 AFTER HOURS SUPPORT       SAN JOSE, CA
       114 APPLICATION SUPPORT       WEXFORD, PA
       115 MARKETING                 SEASIDE, CA
       116 NETWORKING                WEXFORD, PA
       117 DIRECTORS OFFICE          WEXFORD, PA
       118 ASSISTANTS                WEXFORD, PA
       119 COMMUNICATIONS            SEATTLE, WA
       120 REGIONAL SUPPORT          PORTLAND, OR

21 rows selected.

Dropping External Tables

For an external table, the DROP TABLE statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.

Data Dictionary Views

The following views allow you to access information about external tables.

View Name	Description
DBA_EXTERNAL_TABLES	These views list the specific attributes of external tables in the database.
DBA_EXTERNAL_LOCATIONS	These views list the data sources for external tables.

The following are example queries from the two data dictionary views mentioned above:

SQL> SELECT owner, table_name, default_directory_name, access_parameters
  2 FROM dba_external_tables;

OWNER TABLE_NAME     DEFAULT_DIRECTORY_NAME ACCESS_PARAMETERS
----- -------------- ---------------------- ---------------------------------------------------
SCOTT DEPARTMENT_EXT DATA_DIR               records delimited by newline
                                            badfile bad_dir:'department.bad'
                                            logfile log_dir:'department.log'
                                            fields terminated by ',' optionally enclosed by '"'
                                            missing field values are null
                                            (dept_id, name, location)

SQL> SELECT * FROM dba_external_locations;

OWNER TABLE_NAME     LOCATION        DIR DIRECTORY_NAME
----- -------------- --------------- --- ------------------------------
SCOTT DEPARTMENT_EXT department.dat  SYS DATA_DIR

Cluster Tables

What is a cluster table?

A cluster is a schema object that contains one or more tables that all have one or more columns in common. Rows of one or more tables that share the same value in these common columns are physically stored together within the database.

Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT statements, and UPDATE statements that modify cluster key values. Before clustering, consider its

Cluster Keys

The columns defined by the CREATE CLUSTER command make up the cluster key. These cluster columns must correspond in both datatype and size to columns in each of the clustered tables, although they need not correspond in name.

Types of Clusters

A cluster can be either an indexed cluster or a hash cluster.

Indexed Clusters

In an indexed cluster, Oracle stores together rows having the same cluster key value. Each distinct cluster key value is stored only once in each data block, regardless of the number of tables and rows in which it occurs. This saves disk space and improves performance for many operations.

You may want to use indexed clusters in the following cases:

· Your queries retrieve rows over a range of cluster key values.

· Your clustered tables may grow unpredictably.

After you create an indexed cluster, you must create an index on the cluster key before you can issue any data manipulation language (DML) statements against a table in the cluster. This index is called the cluster index.

A cluster index provides quick access to rows within a cluster based on the cluster key. If you issue a SQL statement that searches for a row in the cluster based on its cluster key value, Oracle searches the cluster index for the cluster key value and then locates the row in the cluster based on its ROWID.

Hash Clusters

In a hash cluster, Oracle stores together rows that have the same hash key value. The hash value for a row is the value returned by the cluster's hash function. When you create a hash cluster, you can either specify a hash function or use the Oracle internal hash function. Hash values are not actually stored in the cluster, although cluster key values are stored for every row in the cluster.

You may want to use hash clusters in the following cases:

· Your queries retrieve rows based on equality conditions involving all cluster key columns.

· Your clustered tables are static or you can determine the maximum number of rows and the maximum amount of space required by the cluster when you create the cluster.

The hash function provides access to rows in the table based on the cluster key value. If you issue a SQL statement that locates a row in the cluster based on its cluster key value, Oracle applies the hash function to the given cluster key value and uses the resulting hash value to locate the matching rows. Because multiple cluster key values can map to the same hash value, Oracle must also check the row's cluster key value. This process often results in less I/O than the process for the indexed cluster, because the index search is not required.

Oracle's internal hash function returns values ranging from 0 to the value of HASHKEYS - 1. If you specify a column with the HASH IS clause, the column values need not fall into this range. Oracle divides the column value by the HASHKEYS value and uses the remainder as the hash value. The hash value for null is HASHKEYS - 1. Oracle also rounds the HASHKEYS value up to the nearest prime number to obtain the actual number of hash values. This rounding reduces the likelihood of hash collisions, or multiple cluster key values having the same hash value.

You cannot create a cluster index for a hash cluster, and you need not create an index on a hash cluster key.

If you cannot fit all rows for one hash value into a data block, do not use hash clusters. Performance is very poor in this circumstance because an insert or update of a row in a hash cluster with a size exceeding the data block size fills the block and performs row chaining to contain the rest of the row.

Cluster Size

Oracle uses the value of the SIZE parameter to determine the space reserved for rows corresponding to one cluster key value or one hash value. This space then determines the maximum number of cluster or hash values stored in a data block. If the SIZE value is not a divisor of the data block size, Oracle uses the next largest divisor. If the SIZE value is larger than the data block size, Oracle uses the operating system block size, reserving at least one data block per cluster or hash value.

Oracle also considers the length of the cluster key when determining how much space to reserve for the rows having a cluster key value. Larger cluster keys require larger sizes. To see the actual size, query the KEY_SIZE column of the USER_CLUSTERS data dictionary view. This does not apply to hash clusters because hash values are not actually stored in the cluster.

Although the maximum number of cluster and hash key values per data block is fixed on a per `-cluster basis, Oracle does not reserve an equal amount of space for each cluster or hash key value. Varying this space stores data more efficiently, because the data stored per cluster or hash key value is rarely fixed.

A SIZE value smaller than the space needed by the average cluster or hash key value may require the data for one cluster key or hash key value to occupy multiple data blocks. A SIZE value much larger results in wasted space.

When you create a hash cluster, Oracle immediately allocates space for the cluster based on the values of the SIZE and HASHKEYS parameters.

Adding Tables to a Cluster

You can add tables to an existing cluster by issuing a CREATE TABLE statement with the CLUSTER clause. A cluster can contain as many as 32 tables, although the performance gains of clustering are often lost in clusters of more than four or five tables.

All tables in the cluster have the cluster's storage characteristics as specified by the PCTUSED, PCTFREE, INITRANS, MAXTRANS, TABLESPACE, and STORAGE parameters.

Example I

The following statement creates an indexed cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a cluster size of 512 bytes, and storage parameter values:

CREATE CLUSTER personnel

( department_number NUMBER(2) )

SIZE 512

STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10);

The following statements add the EMP and DEPT tables to the cluster:

CREATE TABLE emp

(empno NUMBER PRIMARY KEY,

ename VARCHAR2(10) NOT NULL

CHECK (ename = UPPER(ename)),

job VARCHAR2(9),

mgr NUMBER REFERENCES scott.emp(empno),

hiredate DATE CHECK (hiredate >= SYSDATE),

sal NUMBER(10,2) CHECK (sal > 500),

comm NUMBER(9,0) DEFAULT NULL,

deptno NUMBER(2) NOT NULL )

CLUSTER personnel (deptno);

CREATE TABLE dept

(deptno NUMBER(2),

dname VARCHAR2(9),

loc VARCHAR2(9))

CLUSTER personnel (deptno);

The following statement creates the cluster index on the cluster key of PERSONNEL:

CREATE INDEX idx_personnel ON CLUSTER personnel;

After creating the cluster index, you can insert rows into either the EMP or DEPT tables.

Example II

The following statement creates a hash cluster named PERSONNEL with the cluster key column DEPARTMENT_NUMBER, a maximum of 503 hash key values, each of size 512 bytes, and storage parameter values:

CREATE CLUSTER personnel

( department_number NUMBER )

SIZE 512 HASHKEYS 500

STORAGE (INITIAL 100K NEXT 50K PCTINCREASE 10);

Because the above statement omits the HASH IS clause, Oracle uses the internal hash function for the cluster.

Example III

The following statement creates a hash cluster named PERSONNEL with the cluster key made up of the columns HOME_AREA_CODE and HOME_PREFIX, and uses a SQL expression containing these columns for the hash function:

CREATE CLUSTER personnel

( home_area_code NUMBER,

home_prefix NUMBER )

HASHKEYS 20

HASH IS MOD(home_area_code + home_prefix, 101);

Tuesday, September 6, 2011

V$OBJECT_USAGE does not display rows


When you issue command ALTER INDEX <Schema.index> MONITORING USAGE

from SYS v$object_usage not giving any rows then the solution is mention below

OR


you run monitoring usage command from the same schema whose indexes you want

to monition.


Metalink DOC ID  160712.1

Viewing All Indexes Being Monitored Under Another User's Schema:

================================================================

V$OBJECT_USAGE does not display rows for all indexes in the database whose  usage is

being monitored.

'ALTER INDEX <index> MONITORING USAGE' places an entry in V$OBJECT_USAGE for  that

particular index to help determine if the index is being used or not.

The  V$OBJECT_USAGE view uses the username logged into database when the

'ALTER  INDEX <index> MONITORING USAGE' is issued.

This will not enable any user other than the user who issued the

'ALTER INDEX <index> MONITORING USAGE' to view if  index is being monitored or not.

The view structure may be changed slightly (see below) in order to expand its  scope

system-wide (see below) so that you may see all indexes being monitored.

For example:  Showing User Scott monitoring his Index on EMP table:

SQL>  connect scott/tiger

SQL> set LONG 30000

SQL> select text from dba_views where view_name ='V$OBJECT_USAGE';

TEXT

select   io.name, t.name,

   decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),

   decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),

   ou.start_monitoring,

   ou.end_monitoring

   from    sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou

   where    io.owner# = userenv('SCHEMAID')

   and    i.obj# = ou.obj#

   and    io.obj# = ou.obj#

   and    t.obj# = i.bo#

SQL> select index_name, table_name, uniqueness, status from user_indexes

           where table_name = 'EMP';

INDEX_NAME   TABLE_NAME     UNIQUENES STATUS     PK_EMP   EMP     UNIQUE    VALID

SQL> alter index PK_EMP monitoring usage;

 Index altered.

SQL> select * from v$object_usage;

INDEX_NAME   TABLE_NAME    MONITORING USED START_MONITORING  END_MONITORING

PK_EMP              EMP    YES        NO   10/12/2001 06:42:35

Then connect as another user to view indexes being monitored:

SQL> connect / as sysdba;

Connected.

SQL> select * from v$object_usage;

no rows selected

To be able to view them do the following:

SQL> create or replace view V$ALL_OBJECT_USAGE

 (OWNER,INDEX_NAME,TABLE_NAME,MONITORING,USED,START_MONITORING,END_MONITORING)

as

  select u.name, io.name, t.name,

  decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),

  decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),

  ou.start_monitoring,

  ou.end_monitoring

  from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou

  where i.obj# = ou.obj#

  and io.obj# = ou.obj#

  and t.obj# = i.bo#

  and u.user# = io.owner#;

View created.

SQL> select * from v$all_object_usage;

OWNER INDEX_NAME      TABLE_NAME     MON  USE   START_MONITORING   END_MONITORING

SCOTT    PK_EMP          EMP            YES  NO    10/12/2001 06:42:35