Script example: create database

CREATE DATABASE "mydb"
    MAXINSTANCES 8
    MAXLOGHISTORY 1
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
DATAFILE '/u02/oradata/mydb/system01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT  32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL 
SYSAUX DATAFILE '/u02/oradata/mydb/sysaux01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED 
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/mydb/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u02/oradata/mydb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/u03/oradata/mydb/redo01.log') SIZE 2048M,
             GROUP 2 ('/u03/oradata/mydb/redo02.log') SIZE 2048M,
             GROUP 3 ('/u03/oradata/mydb/redo03.log') SIZE 2048M
USER SYS IDENTIFIED BY "&&sysPassword"
USER SYSTEM IDENTIFIED BY "&&systemPassword";

Note 1:
You can exchange "SMALLFILE" with "BIGFILE" for any tablespace, as long as they are locally managed with automatic segment space management.

There are three exceptions to this rule: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace does not have to use automatic segment space management; they *must* use manual segment space management but can still be created as BIGFILE. In one of my databases, I have the following setup:

SELECT tablespace_name,bigfile,extent_management,segment_space_management 
FROM dba_tablespaces;

TABLESPACE_NAME                          BIGFILE   EXTENT_MANAGEMENT              SEGMENT_SPACE_MANA
---------------------------------------- --------- ------------------------------ ------------------
SYSTEM                                   NO        LOCAL                          MANUAL
SYSAUX                                   NO        LOCAL                          AUTO
UNDOTBS1                                 YES       LOCAL                          MANUAL
TEMP                                     YES       LOCAL                          MANUAL
USERS                                    NO        LOCAL                          AUTO

Note 2:
The NATIONAL CHARACTER SET clause specifies the "national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. Valid values are AL16UTF16 and UTF8. The default is AL16UTF16."

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>