How to change Oracle hidden parameters

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================
 

Oracle hidden parameters start with "_". They can not be viewed from the output of show parameter,
or by querying v$parameter unless and until they are set explicitly in init.ora.

If you want to view all the hidden parameters and their default values, the following query
could be of help,

SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_%' escape '/';

To see the listing of all hidden parameters query,

select * from SYS.X$KSPPI where substr(KSPPINM,1,1) = '_';

It is never recommended to modify Oracle hidden parameters without the assistance of Oracle Support. Changing these parameters may lead to high performance degradation and other problems in the database.

Methods to change Oracle hidden parameters:

1) If you use pfile, you can entry of the hidden parameter into initSID.ora, and restart the instance.

"_shared_pool_reserved_pct"=10

2) If you want to set for the current session, use ALTER SESSION SET ...

3) If you want to set it permanently and you are using spfile, use ALTER SYSTEM SET ...... SCOPE=BOTH

Some hidden parameters cannot be changed in MEMORY, such as _shared_pool_reserved_pct. See below:

SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /fs/u01/oracle/product/1 0.2.0.2ee/dbs/spfilemydb.ora SQL> alter system set "_shared_pool_reserved_pct"=10 scope=both; alter system set "_shared_pool_reserved_pct"=10 scope=both * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> alter system set "_shared_pool_reserved_pct"=10 scope=memory; alter system set "_shared_pool_reserved_pct"=10 scope=memory * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile; System altered.

If SCOPE=SPFILE, the instance needs to be restarted.

Database upgrading methods

There are four different upgrade methods to upgrade Oracle database to the new Oracle database release:

1) Database Upgrade Assistant (DBUA)
2) Manual Upgrade
3) Export/Import
4) Data Copying

1) DBUA (Database Upgrade Assistant)

The Database Upgrade Assistant (DBUA) interactively steps you through the upgrade process and configures the database for the new Oracle Database 10g release. The DBUA automates the upgrade process by performing all of the tasks normally performed manually. The DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs. You can then act on these recommendations. This method is very easy and user friendly. But if any error occurs it will take time to diagnose the error as the upgrade process is automatically by the upgrade assistant.

2) Manual upgrade

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database 10g release. A manual upgrade gives you finer control over the upgrade process as it is done step by step manually. So if any error occurs, it is easy to diagnose the error. While a manual upgrade gives you finer control over the upgrade process, it is more susceptible to error if any of the upgrade or pre-upgrade steps are either not followed or are performed out of order.
When manually upgrading a database, perform the following pre-upgrade steps:

• Analyze the database using the Pre-Upgrade Information Tool. The Upgrade Information Tool is SQL script that ships with the new Oracle Database 10g release, and must be run in the environment of the database being upgraded.
  The Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for the new Oracle Database 10g release.
• Prepare the new Oracle Home.
• Perform a backup of the database.

Depending on the release of the database being upgraded, you may need to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems).

3) Export/Import

The Export/Import upgrade method does not change the current database, which enables the database to remain available throughout the upgrade process. However, if a consistent snapshot of the database is required (for data integrity or other purposes), then the database must run in restricted mode or must otherwise be protected from changes during the export procedure. Because the current database can remain available, you can, for example, keep an existing production database running while the new Oracle Database 10g database is being built at the same time by Export/Import. During the upgrade, to maintain complete database consistency, changes to the data in the database cannot be permitted without the same changes to the data in the new Oracle Database.

Most importantly, the Export/Import operation results in a completely new database. Although the current database ultimately contains a copy of the specified data, the upgraded database may perform differently from the original database. For example, although Export/Import creates an identical copy of the database, other factors, such as disk placement of data and unset tuning parameters, may cause unexpected performance problems.

Upgrading an entire database by using Export/Import can take a long time, especially compared to using the DBUA or performing a manual upgrade. Therefore, you may need to schedule the upgrade during non-peak hours or make provisions for propagating to the new database any changes that are made to the current database during the upgrade.

4) Data Copying

You can copy data from one Oracle Database to another using database links. For example, you can create new tables and fill the tables with data by using the INSERT INTO statement and the CREATE TABLE ... AS statement. Copying data and Export/Import offer the same advantages for upgrading. Using either method, you can defragment data files and restructure the database by creating new tablespaces or modifying existing tables or tablespaces. In addition, you can copy only specified database objects or users.

Copying data, however, unlike Export/Import, enables the selection of specific rows of tables to be placed into the new database. Copying data is a good method for copying only part of a database table. In contrast, using Export/Import, you can copy only entire tables

Quick Reference to Auditing Information

1.       Database Audit mode

SQL> show parameter audit NAME                                 TYPE        VALUE ------------------------------------ ----------- ----------------------------- audit_file_dest                      string      /u02/oracle/admin/mydb/adump audit_sys_operations                 boolean     FALSE audit_syslog_level                   string audit_trail                          string      TRUE

2.       What Statements are being audited ?

To set audit:

AUDIT [option] [BY USER|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

To check audit result:

select * from dba_stmt_audit_opts where USER_NAME='...'; Note: AUDIT_OPTION: from table STMT_AUDIT_OPTION_MAP; SUCCESS:      'BY SESSION', 'BY ACCESS' or 'NOT SET' FAILURE:      ""

3.       What Privileges are being audited ?

To set audit:

AUDIT [option] [BY user|SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

To check audit result:

select * from dba_priv_audit_opts where USER_NAME='...'; Note: PRIVILEGE: from SYSTEM_PRIVILEGE_MAP SUCCESS:   'BY SESSION', 'BY ACCESS' or 'NOT SET' FAILURE:   ""

4.       What Objects are being audited ?

To set Auditing:

AUDIT [object_option] ON [schema].object|DEFAULT [BY SESSION|ACCESS] [WHENEVER {NOT} SUCCESSFUL]

To check audit result:

select * from dba_obj_audit_opts where owner='..' and OBJECT_NAME='...'; select * from all_def_audit_opts; Note: Values of columns (ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE FBK REA) are X/Y: -    is no option set -    X is when successful -    S set by session -    Y is when Unsuccessful -    A set by access

5.       Audit results

-    Raw results go to DBA_AUDIT_TRAIL (view on SYS.AUD$ table).

-    Main where columns are: USERNAME, TIMESTAMP, OWNER

-    For underlying results see: Select STATEMENT, TIMESTAMP, ACTION, USERID from AUD$;

6.       Auditing administrative connections

The administrative user connections (CONNECT / AS SYSDBA or CONNECT / AS SYSOPER) are always logged regardless of audit setting. On UNIX platforms these are logged to *.aud files in $ORACLE_HOME/rdbms/audit regardless of any init.ora parameter settings.

Last updated: 2009-September-23, Tuesday

TABLE_PRIVILEGE_MAP

TABLE_PRIVILEGE_MAP describes privilege (auditing option) type codes. This table can be used to map privilege (auditing option) type numbers to type names.

Column	Datatype	NULL	Description
PRIVILEGE	NUMBER	NOT NULL	Numeric privilege (auditing option) type code
NAME	VARCHAR2(40)	NOT NULL	Name of the type of privilege (auditing option)

Oracle 11.1.0.6.0

select * from table_PRIVILEGE_MAP order by privilege;

PRIVILEGE	NAME
0	ALTER
1	AUDIT
2	COMMENT
3	DELETE
4	GRANT
5	INDEX
6	INSERT
7	LOCK
8	RENAME
9	SELECT
10	UPDATE
11	REFERENCES
12	EXECUTE
16	CREATE
17	READ
18	WRITE
20	ENQUEUE
21	DEQUEUE
22	UNDER
23	ON COMMIT REFRESH
24	QUERY REWRITE
26	DEBUG
27	FLASHBACK
28	MERGE VIEW
29	USE
30	FLASHBACK ARCHIVE

SYSTEM_PRIVILEGE_MAP

SYSTEM_PRIVILEGE_MAP describes privilege type codes. This table can be used to map privilege type numbers to type names.

Column	Datatype	NULL	Description
PRIVILEGE	NUMBER	NOT NULL	Numeric privilege type code
NAME	VARCHAR2(40)	NOT NULL	Name of the type of privilege
PROPERTY	NUMBER	NOT NULL	Property flag of the privilege

Oracle 11.1.0.6.0

select * from SYSTEM_PRIVILEGE_MAP order by privilege desc;

PRIVILEGE	NAME	PROPERTY
-3	ALTER SYSTEM	0
-4	AUDIT SYSTEM	0
-5	CREATE SESSION	0
-6	ALTER SESSION	0
-7	RESTRICTED SESSION	0
-10	CREATE TABLESPACE	0
-11	ALTER TABLESPACE	0
-12	MANAGE TABLESPACE	0
-13	DROP TABLESPACE	0
-15	UNLIMITED TABLESPACE	0
-20	CREATE USER	0
-21	BECOME USER	0
-22	ALTER USER	0
-23	DROP USER	0
-30	CREATE ROLLBACK SEGMENT	0
-31	ALTER ROLLBACK SEGMENT	0
-32	DROP ROLLBACK SEGMENT	0
-40	CREATE TABLE	0
-41	CREATE ANY TABLE	0
-42	ALTER ANY TABLE	0
-43	BACKUP ANY TABLE	0
-44	DROP ANY TABLE	0
-45	LOCK ANY TABLE	0
-46	COMMENT ANY TABLE	0
-47	SELECT ANY TABLE	0
-48	INSERT ANY TABLE	0
-49	UPDATE ANY TABLE	0
-50	DELETE ANY TABLE	0
-60	CREATE CLUSTER	0
-61	CREATE ANY CLUSTER	0
-62	ALTER ANY CLUSTER	0
-63	DROP ANY CLUSTER	0
-71	CREATE ANY INDEX	0
-72	ALTER ANY INDEX	0
-73	DROP ANY INDEX	0
-80	CREATE SYNONYM	0
-81	CREATE ANY SYNONYM	0
-82	DROP ANY SYNONYM	0
-83	SYSDBA	0
-84	SYSOPER	0
-85	CREATE PUBLIC SYNONYM	0
-86	DROP PUBLIC SYNONYM	0
-90	CREATE VIEW	0
-91	CREATE ANY VIEW	0
-92	DROP ANY VIEW	0
-105	CREATE SEQUENCE	0
-106	CREATE ANY SEQUENCE	0
-107	ALTER ANY SEQUENCE	0
-108	DROP ANY SEQUENCE	0
-109	SELECT ANY SEQUENCE	0
-115	CREATE DATABASE LINK	0
-120	CREATE PUBLIC DATABASE LINK	0
-121	DROP PUBLIC DATABASE LINK	0
-125	CREATE ROLE	0
-126	DROP ANY ROLE	0
-127	GRANT ANY ROLE	0
-128	ALTER ANY ROLE	0
-130	AUDIT ANY	0
-135	ALTER DATABASE	0
-138	FORCE TRANSACTION	0
-139	FORCE ANY TRANSACTION	0
-140	CREATE PROCEDURE	0
-141	CREATE ANY PROCEDURE	0
-142	ALTER ANY PROCEDURE	0
-143	DROP ANY PROCEDURE	0
-144	EXECUTE ANY PROCEDURE	0
-151	CREATE TRIGGER	0
-152	CREATE ANY TRIGGER	0
-153	ALTER ANY TRIGGER	0
-154	DROP ANY TRIGGER	0
-160	CREATE PROFILE	0
-161	ALTER PROFILE	0
-162	DROP PROFILE	0
-163	ALTER RESOURCE COST	0
-165	ANALYZE ANY	0
-167	GRANT ANY PRIVILEGE	0
-172	CREATE MATERIALIZED VIEW	0
-173	CREATE ANY MATERIALIZED VIEW	0
-174	ALTER ANY MATERIALIZED VIEW	0
-175	DROP ANY MATERIALIZED VIEW	0
-177	CREATE ANY DIRECTORY	0
-178	DROP ANY DIRECTORY	0
-180	CREATE TYPE	0
-181	CREATE ANY TYPE	0
-182	ALTER ANY TYPE	0
-183	DROP ANY TYPE	0
-184	EXECUTE ANY TYPE	0
-186	UNDER ANY TYPE	0
-188	CREATE LIBRARY	0
-189	CREATE ANY LIBRARY	0
-190	ALTER ANY LIBRARY	0
-191	DROP ANY LIBRARY	0
-192	EXECUTE ANY LIBRARY	0
-200	CREATE OPERATOR	0
-201	CREATE ANY OPERATOR	0
-202	ALTER ANY OPERATOR	0
-203	DROP ANY OPERATOR	0
-204	EXECUTE ANY OPERATOR	0
-205	CREATE INDEXTYPE	0
-206	CREATE ANY INDEXTYPE	0
-207	ALTER ANY INDEXTYPE	0
-208	DROP ANY INDEXTYPE	0
-209	UNDER ANY VIEW	0
-210	QUERY REWRITE	0
-211	GLOBAL QUERY REWRITE	0
-212	EXECUTE ANY INDEXTYPE	0
-213	UNDER ANY TABLE	0
-214	CREATE DIMENSION	0
-215	CREATE ANY DIMENSION	0
-216	ALTER ANY DIMENSION	0
-217	DROP ANY DIMENSION	0
-218	MANAGE ANY QUEUE	1
-219	ENQUEUE ANY QUEUE	1
-220	DEQUEUE ANY QUEUE	1
-222	CREATE ANY CONTEXT	0
-223	DROP ANY CONTEXT	0
-224	CREATE ANY OUTLINE	0
-225	ALTER ANY OUTLINE	0
-226	DROP ANY OUTLINE	0
-227	ADMINISTER RESOURCE MANAGER	1
-228	ADMINISTER DATABASE TRIGGER	0
-233	MERGE ANY VIEW	0
-234	ON COMMIT REFRESH	0
-235	EXEMPT ACCESS POLICY	0
-236	RESUMABLE	0
-237	SELECT ANY DICTIONARY	0
-238	DEBUG CONNECT SESSION	0
-241	DEBUG ANY PROCEDURE	0
-243	FLASHBACK ANY TABLE	0
-244	GRANT ANY OBJECT PRIVILEGE	0
-245	CREATE EVALUATION CONTEXT	1
-246	CREATE ANY EVALUATION CONTEXT	1
-247	ALTER ANY EVALUATION CONTEXT	1
-248	DROP ANY EVALUATION CONTEXT	1
-249	EXECUTE ANY EVALUATION CONTEXT	1
-250	CREATE RULE SET	1
-251	CREATE ANY RULE SET	1
-252	ALTER ANY RULE SET	1
-253	DROP ANY RULE SET	1
-254	EXECUTE ANY RULE SET	1
-255	EXPORT FULL DATABASE	0
-256	IMPORT FULL DATABASE	0
-257	CREATE RULE	1
-258	CREATE ANY RULE	1
-259	ALTER ANY RULE	1
-260	DROP ANY RULE	1
-261	EXECUTE ANY RULE	1
-262	ANALYZE ANY DICTIONARY	0
-263	ADVISOR	0
-264	CREATE JOB	0
-265	CREATE ANY JOB	0
-266	EXECUTE ANY PROGRAM	0
-267	EXECUTE ANY CLASS	0
-268	MANAGE SCHEDULER	0
-269	SELECT ANY TRANSACTION	0
-270	DROP ANY SQL PROFILE	0
-271	ALTER ANY SQL PROFILE	0
-272	ADMINISTER SQL TUNING SET	0
-273	ADMINISTER ANY SQL TUNING SET	0
-274	CREATE ANY SQL PROFILE	0
-275	EXEMPT IDENTITY POLICY	0
-276	MANAGE FILE GROUP	1
-277	MANAGE ANY FILE GROUP	1
-278	READ ANY FILE GROUP	1
-279	CHANGE NOTIFICATION	0
-280	CREATE EXTERNAL JOB	0
-281	CREATE ANY EDITION	0
-282	DROP ANY EDITION	0
-283	ALTER ANY EDITION	0
-284	CREATE ASSEMBLY	0
-285	CREATE ANY ASSEMBLY	0
-286	ALTER ANY ASSEMBLY	0
-287	DROP ANY ASSEMBLY	0
-288	EXECUTE ANY ASSEMBLY	0
-289	EXECUTE ASSEMBLY	0
-290	CREATE MINING MODEL	0
-291	CREATE ANY MINING MODEL	0
-292	DROP ANY MINING MODEL	0
-293	SELECT ANY MINING MODEL	0
-294	ALTER ANY MINING MODEL	0
-295	COMMENT ANY MINING MODEL	0
-301	CREATE CUBE DIMENSION	0
-302	ALTER ANY CUBE DIMENSION	0
-303	CREATE ANY CUBE DIMENSION	0
-304	DELETE ANY CUBE DIMENSION	0
-305	DROP ANY CUBE DIMENSION	0
-306	INSERT ANY CUBE DIMENSION	0
-307	SELECT ANY CUBE DIMENSION	0
-308	CREATE CUBE	0
-309	ALTER ANY CUBE	0
-310	CREATE ANY CUBE	0
-311	DROP ANY CUBE	0
-312	SELECT ANY CUBE	0
-313	UPDATE ANY CUBE	0
-314	CREATE MEASURE FOLDER	0
-315	CREATE ANY MEASURE FOLDER	0
-316	DELETE ANY MEASURE FOLDER	0
-317	DROP ANY MEASURE FOLDER	0
-318	INSERT ANY MEASURE FOLDER	0
-319	CREATE CUBE BUILD PROCESS	0
-320	CREATE ANY CUBE BUILD PROCESS	0
-321	DROP ANY CUBE BUILD PROCESS	0
-322	UPDATE ANY CUBE BUILD PROCESS	0
-326	UPDATE ANY CUBE DIMENSION	0
-327	ADMINISTER SQL MANAGEMENT OBJECT	0
-350	FLASHBACK ARCHIVE ADMINISTER	0