What is the package SYS.USER_LOCK?

Recenty I was migrating a database with a package that wouldn't compile because it couldn't find the package called upon in its code:

SQL> ALTER PACKAGE mypackage compile body; 

Warning: Package body altered with compilation errors.

SQL> show errors
Errors for PACKAGE BODY MVA3.MYPACKAGE:

PLS-00201: identifier 'USER_LOCK.SLEEP' must be declared

According to PSOUG, there are "four separate ways to induce a sleep into PL/SQL."

•dbms_backup_restore
•dbms_drs
•dbms_lock
•user_lock


PSOUG continues: "Note that USER_LOCK package is not part of the default Oracle installation"

Install as follows:

cd $ORACLE_HOME/rdbms/admin
sqlplus / as sysdba @userlock.sql

How to work around error from opatch "LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo"

I recently had to investigate the patch level for an Oracle 10g database prior to an upgrade of an OAS application database.

This is the error I ran into:

myserver:asdb:ora10g>opatch lsinventory
Invoking OPatch 10.2.0.4.3

 
Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Fix this by running the attachHome.sh script in $ORACLE_HOME/oui/bin.
The Oracle Universal Installer allows you to set up an Inventory from scratch, or to register an existing ORACLE_HOME in an already existing Inventory.

myserver:asdb:ora10g>cd $ORACLE_HOME/oui/bin
/u01/oracle/product/ora10g/oui/bin
myserver:asdb:ora10g>./attachHome.sh
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/oracle/oraInventory11g
'AttachHome' was successful.

Result:

myserver:asdb:ora10g>opatch lsinventory
Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/oracle/product/ora10g
Central Inventory : /u01/oracle/oraInventory11g
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.3
OUI version       : 10.2.0.4.0
OUI location      : /u01/oracle/product/ora10g/oui
Log file location : /u01/oracle/product/ora10g/cfgtoollogs/opatch/opatch2016-01-18_16-01-17PM.log

Lsinventory Output file location : /u01/oracle/product/ora10g/cfgtoollogs/opatch/lsinv/lsinventory2016-01-18_16-01-17PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (4):

Oracle Database 10g                                                  10.2.0.1.0
Oracle Database 10g Products                                         10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 2                            10.2.0.3.0
Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0
There are 4 products installed in this Oracle Home.


Interim patches (3) :

Patch  6979801      : applied on Fri Mar 06 10:47:02 CET 2009
   Created on 5 Feb 2009, 15:35:25 hrs PST8PDT
   Bugs fixed:
     6979801

Patch  6972843      : applied on Tue Oct 14 17:35:12 CEST 2008
   Created on 25 Jun 2008, 08:31:04 hrs UTC
   Bugs fixed:
     6972843

Patch  6823287      : applied on Tue Oct 14 17:26:32 CEST 2008
   Created on 30 Jun 2008, 10:27:42 hrs UTC
   Bugs fixed:
     6823287



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

OPatch succeeded.

How to use dbms_metadata to generate DDL for constraints

set long 10000
set heading off
set trimspool on
set lines 300
set longchunksize 300
spool all_mycons.sql
exec dbms_metadata.set_Transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

select dbms_metadata.get_ddl('CONSTRAINT','SYS_C006257121','SCOTT')  FROM DUAL;

-- For Foreign key constraints, use the REF_CONSTRAINT constant:
select dbms_metadata.get_ddl('REF_CONSTRAINT','MYFOREIGNKEY_FK','SCOTT')  FROM DUAL;

How to use dbms_metadata to generate DDL for a trigger

set long 10000
set heading off
set trimspool on
set lines 300
set longchunksize 300
spool mytrigger.sql
exec dbms_metadata.set_Transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);

select dbms_metadata.get_ddl('TRIGGER','MYTRIGGER','SCOTT')  FROM DUAL;

On a Linux system, what do the parameters shmall, shmmax and shmmni define?

shmall
* indicates the total amount of shared memory that the system can use at one time (measured in pages)

shmmax
* defines the maximum size in bytes of a single shared memory segment that a Linux process can allocate in its virtual address space, in bytes.
Oracle recommends that more than half of the physical memory is assigned to shmmax

shmmni
* defines the system wide maximum number of shared memory segments

How to join v$database with v$instance

Here is a useful query which displays basic information about the database, using v$database and v$instance.
Take the opportunity to calculate the uptime for the database by subtracting the value of startup_time from the current date.

col platform_name format a30
col open_mode format a20
col host_name format a20
col version format a10
col status format a20
col uptime format a30
col name format a10
set lines 300

SELECT D.NAME,D.PLATFORM_NAME,D.CREATED, D.OPEN_MODE,I.HOST_NAME,I.VERSION, I.ARCHIVER,I.STATUS,
   TO_DSINTERVAL( TO_CHAR(
                                    TO_TIMESTAMP(SYSDATE)-I.STARTUP_TIME
                                   )
                      ) "UPTIME", 
                      (SELECT ROUND(SUM(BYTES)/1024/1024/1024) FROM DBA_DATA_FILES) "DB size GB",  
                      ( SELECT ROUND(BYTES/1024/1024/1024) "mem GB" FROM V$SGAINFO WHERE NAME = 'Maximum SGA Size' ) "SGA max size GB"
  FROM V$DATABASE D INNER JOIN V$INSTANCE I 
  ON UPPER(D.NAME) = UPPER(I.INSTANCE_NAME);

Result may look like the following:

NAME	PLATFORM_NAME	CREATED	OPEN_MODE	HOST_NAME	VERSION	ARCHIVER	STATUS	UPTIME	DB size GB	SGA max size GB
proddb01	Linux x86 64-bit	10.12.2015 14:16:46	READ WRITE	prodserver01.mycompany.com	11.2.0.4.0	STARTED	OPEN	+00 13:59:55.000000	929	12

Generate "drop user" statement for schemas with and without objects

set lines 200
col owner format a20
col username format a30
col schema format a30


PROMPT ===================================================
PROMPT List of all schemas and number of Objects they own:
PROMPT ===================================================
select u.username "Schema", decode(count(*),1,0, count(*)) "Number of Objects"
from dba_users u left outer join dba_objects o
on u.username = o.owner
where u.username in (USER1','USER2','USER3')
group by u.username
order by username ASC;

set heading off
set trimspool on
set lines 200
set feedback off
spool drop_users.sql
select 'spool drop_users_SD4440.log' from dual;
select 'DROP USER ' || u.username
                    || DECODE(decode(count(*),1,0, count(*)),
                       '0',';'
                       , ' CASCADE;')
from dba_users u left outer join dba_objects o
on u.username = o.owner
where u.username in ('USER1','USER2','USER3')
group by u.username;
select 'exit' from dual;
exit