Why aren't other schemas' procedures showing in TOADs schema browser?

Problem: User SCOTT has been granted SELECT on a number of tables belonging to another schema, OBM.
They all show up neatly in TOADs schema browser.

However, procedures and sequences are not visible. Why?

Answer: lack of privileges.

Solution: Try granting the following as user sys:

GRANT SELECT ON OBM.sequence1 to SCOTT;
GRANT DEBUG ON OBM.proceure1 to SCOTT;

Refresh the schema browser - voila!

To generate a list of objects with grant statements:

SELECT 'GRANT DEBUG ON ' || OWNER || '.' || OBJECT_NAME || ' TO SCOTT;' 
FROM DBA_PROCEDURES 
WHERE OWNER = 'OBM' 
AND OBJECT_TYPE='PROCEDURE';

SELECT 'GRANT SELECT ON ' || SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ' TO SCOTT;' 
FROM DBA_SEQUENCES 
WHERE SEQUENCE_OWNER = 'OBM';

Alternatively, if acceptable in your environment you could grant select and debug on all sequences and procedures, respectively:

GRANT SELECT ANY SEQUENCE TO SCOTT;
GRANT DEBUG ANY PROCEDURE TO SCOTT;

How to find hidden parameters in the database

set lines 200
col description format a70
col value format a20
col name format a30
SELECT name,value,description from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2  WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE';
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';

If desirable, you can generate a "reset" script, which preserves your hidden parameters, like this:

set lines 200
set pages 0
set feedback off
set verify off
set echo off
set heading off
set trimspool on
spool original_hidden_params.sql
select '-- original hidden parameter values' from dual;
select 'alter system set "' || name || '"=' || value || ' scope=spfile;' from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
select 'exit' from dual;
spool off
spool reset_hidden_params.sql
select '-- reset hidden parameter' from dual;
select 'alter system reset "' || name || '" scope=spfile;' from SYS.V$PARAMETER WHERE name  LIKE '\_%' ESCAPE '\';
select 'exit' from dual;
exit

To set a hidden parameter in Your instance, see this post.

How to install Oracle Text

Based on "Manual Installation, Deinstallation and Verification of Oracle Text 11gR2 (Doc ID 970473.1)" from My Oracle Support

1. Install the CTXSYS schema:

SQL> connect SYS/password as SYSDBA
SQL> spool text_install.txt
SQL> @?/ctx/admin/catctx.sql change_on_install SYSAUX TEMP NOLOCK

2. Install the language-specific default preferences.
There is script which creates language-specific default preferences for every language Oracle Text supports in $O_H/ctx/admin/defaults Directory

Grep for your language, and you'll find your script easily:

myserver>grep Norwegian *
drdefn.sql:Rem      default preference for Norwegian

Execute it as follows:

SQL> connect "CTXSYS"/"change_on_install"
SQL> @?/ctx/admin/defaults/drdefn.sql "NORWEGIAN";
SQL> connect SYS/password as SYSDBA
SQL> alter user ctxsys account lock password expire;
SQL> spool off

3. Verify your installation by running:

connect SYS/password as SYSDBA

set pages 1000
col object_name format a40
col object_type format a20
col comp_name format a30
column library_name format a8
column file_spec format a60 wrap
spool text_install_verification.log

-- check on setup
select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';
select * from ctxsys.ctx_version;
select substr(ctxsys.dri_version,1,10) VER_CODE from dual;

select count(*) from dba_objects where owner='CTXSYS';

-- Get a summary count
select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type;

-- Any invalid objects
select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name;

spool off

Valid output depends on your Oracle version, but for 11.2.0.3 it is:

COMP_NAME                      STATUS                                       VERSION
------------------------------ -------------------------------------------- ----------------------------------------
Oracle Text                    VALID                                        11.2.0.3.0
 

VER_DICT                                 VER_CODE
---------------------------------------- ----------------------------------------
11.2.0.3.0                               11.2.0.3.0
 

VER_CODE
----------------------------------------
11.2.0.3.0


  COUNT(*)
----------
       388


OBJECT_TYPE            COUNT(*)
-------------------- ----------
INDEX                        63
TYPE BODY                     6
INDEXTYPE                     4
PROCEDURE                     2
TYPE                         35
TABLE                        50
VIEW                         77
FUNCTION                      2
LIBRARY                       1
PACKAGE BODY                 63
OPERATOR                      6
PACKAGE                      74
LOB                           2
SEQUENCE                      3

How to create the PLUSTRACE role in order to use the AUTOTRACE feature in sqlplus

Create the PLAN_TABLE in the schema you want to use it for (in this example, SCOTT):

CONNECT SCOTT 
@$ORACLE_HOME/rdbms/admin/utlxplan.sql 

Create the PLUSTRACE role:

CONNECT / AS SYSDBA 
@$ORACLE_HOME/sqlplus/admin/plustrce.sql 

Grant the PLUSTRACE role to SCOTT:

CONNECT / AS SYSDBA 
grant plustrace to scott;

You are now able to use the autotrace feature of sqlplus for user SCOTT.

Source: Oracle Documentation

How to display the currently used ORACLE_HOME from sqlplus

Thanks to my former colleague Laurent Schneider for pointing out a way to display the currently used ORACLE_HOME from within sqlplus:

set lines 200
col oracle_home format a40
set autoprint on
var oracle_home varchar2(255)
exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME);

PL/SQL procedure successfully completed.

ORACLE_HOME
------------------------------

/u01/oracle/product/11204

Note: To automatically display the value of a bind variable created with VARIABLE, use the SET AUTOPRINT command.
ON or OFF controls whether SQL*Plus automatically displays bind variables (referenced in a successful PL/SQL block or used in an EXECUTE command).

Source: Oracle Documentation

How to solve ORA-27125: unable to create shared memory segment on AIX

When starting an Oracle instance on a new AIX server, the following error may occur:

ORA-27125: unable to create shared memory segment
IBM AIX RISC System/6000 Error: 1: Not owner

The situation is resolved by setting "capabilities" on the oracle operating system user, as follows:

[root@server1] lsuser -a capabilities oracle
oracle
[root@tsu0emdb02] chuser capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle
[root@tsu0emdb02] lsuser -a capabilities oracle
oracle capabilities=CAP_BYPASS_RAC_VMM,CAP_PROPAGATE

You can now start your instance:

server1:testdb1>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 12 12:10:04 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2255784 bytes
Variable Size            1392510040 bytes
Database Buffers         3942645760 bytes
Redo Buffers                7319552 bytes
SQL>

What constitute "default auditing" under traditional auditing?

From the Oracle Documentation:

"When you use Database Configuration Assistant (DBCA) to create a new database, Oracle Database configures the database to audit the most commonly used security-relevant SQL statements and privileges"

and

"If you manually create a database, then you should run the secconf.sql script to apply the default audit settings to your database"

Oracle Database audits the following privileges by default:

ALTER ANY PROCEDURE

CREATE ANY LIBRARY

DROP ANY TABLE

ALTER ANY TABLE

CREATE ANY PROCEDURE

DROP PROFILE

ALTER DATABASE

CREATE ANY TABLE

DROP USER

ALTER PROFILE

CREATE EXTERNAL JOB

EXEMPT ACCESS POLICY

ALTER SYSTEM

CREATE PUBLIC DATABASE LINK

GRANT ANY OBJECT PRIVILEGE

ALTER USER

CREATE SESSION

GRANT ANY PRIVILEGE

AUDIT SYSTEM

CREATE USER

GRANT ANY ROLE

CREATE ANY JOB

DROP ANY PROCEDURE

Oracle Database audits the following SQL shortcuts by default:

ROLE

SYSTEM AUDIT

PUBLIC SYNONYM

DATABASE LINK

PROFILE

SYSTEM GRANT

Remember that secconf.sql turns on audting regardless of your database using Unified Auditing or Traditional Auditing.

How?

When executed manually, the DBA is prompted for which type of auditing style that should be considered:

sqlplus / as sysdba @secconf.sql

Session altered.


Profile altered.

Do you wish to configure 11g style Audit Configuration OR
Do you wish to configure 12c Unified Audit Policies?
Enter RDBMS_11G for former or RDBMS_UNIAUD for latter
Enter value for 1: RDBMS_11G
old   7:   USER_CHOICE := '&1';
new   7:   USER_CHOICE := 'RDBMS_11G';

What I have found, is that if you intend to harden your Traditional Auditing policies by executing the script in a database where the Unified Auditing polices exist but is disabled, or a database running in "Mixed mode" auditing, the script will fail:

DECLARE
*
ERROR at line 1:
ORA-46358: Audit policy ORA_ACCOUNT_MGMT already exists.
ORA-06512: at line 9

A simple workaround in such a case is to simply comment out the code that is irrelevant to your desired type of auditing, and rerun the script.

You can disable default auditing if you wish, see the section "Disabling and Enabling Default Audit Settings"

To check whether or not default auditing has been actived, you can query the view DBA_PRIV_AUDIT_OPTS, which describes current system privileges being audited across the system and by user. If the column USERNAME is NULL, you have introduced system-wide auditing.