Tag: oracle

CREATE OR REPLACE FUNCTION f_getTopOrg

( orgID IN MYSCHEMA.t_organization.organizationid%TYPE ) 

RETURN MYSCHEMA.t_organization.organizationid%TYPE 
IS

    pOrgID MYSCHEMA.t_organization.organizationid%TYPE;

    cOrgID MYSCHEMA.t_organization.organizationid%TYPE;
BEGIN

    pOrgID := orgID;

    WHILE pOrgID IS NOT NULL

     LOOP

       cOrgID := pOrgID;

       SELECT A.parentorganizationid

         INTO pOrgID

         FROM MYSCHEMA.t_organization A

        WHERE A.organizationid = cOrgID;

     END LOOP;

    RETURN(cOrgID);

END;

ALTER USER APPLICATION_USER ACCOUNT LOCK;

  SELECT ‘alter system kill session ”’ || sid

      || ‘,’ || serial# || ”’;     ‘ || sql_id death

    FROM v$session

   WHERE username = ‘APPLICATION_USER’;

Normally, it is simplest to drop and add the user. This is the preferred method if you have system or sysdba access to the database.

If you don’t have system level access, and want to scrub your schema, the following sql will produce a series of drop statments, which can then be executed. Note: be sure you are connected as the schema owner, would be tragic to drop system tables….

spool dropall.sql
select ‘drop ‘||object_type||’ ‘|| object_name|| DECODE(OBJECT_TYPE,’TABLE’,’ CASCADE CONSTRAINTS;’,’;’)
from user_objects
spool off

Then, can purge the recycle bin to really clean things up:

purge recyclebin;

This will produce a list of drop statements. Not all of them will execute – if you drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with:

select * from user_objects

Here’s a way to advance a sequence using an anonymous PL/SQL block:

DECLARE
mn integer;
mx integer;
BEGIN
SELECT MYPROJ.personnelid_seq.CURRVAL INTO mn FROM DUAL;
SELECT max(personnelid) INTO mx FROM MYPROJ.personnel;
WHILE mn

Listner commands at command prompt:

lsnrctl status
lsnrctl stop
lsnrctl start

Connect to Oracle

SET ORACLE_SID={SID}

sqlplus / AS SYSDBA

or

sqlplus /nolog
SQL>connect / AS SYSDBA

Shutdown

SQL>shutdown immediate

Startup

SQL>startup

To Check and make sure that the SID is correct. The SIDs that are currently registered with the listener can be obtained by typing:
LSNRCTL SERVICES

These SIDs correspond to SID_NAMEs in TNSNAMES.ORA or DB_NAME in the initialisation file.

From a client with the SID registered in the tnsnames.ora, you can do TNSPING and see if the listener responds:

c:>tnsping DEV01

The listener is a separate process that resides on the server. It receives incoming client connection requests and manages the traffic of these requests to the server. A listener is configured with one or more listening protocol addresses and service information about the destination service. Protocol addresses are configured in the listener configuration file, listener.ora. Service information may or may not be configured in the listener.ora file. The listener must be running on the machine where the database resides. The listener can be started by using the Administrative ToolsServices:

Right click on the OracleTNSListener and select START.

To stop the listener, right click and select STOP.

The listener can also be started at a DOS prompt:

Load the listener:
C:>lsnrctl

LSNRCTL for 32-bit Windows: Version 8.1.7.0.0 – Production on 02-APR-2003 08:45:21

(c) Copyright 1998 Oracle Corporation. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

LSNRCTL> LSNRCTL> help

The following operations are available

An asterisk (*) denotes a modifier or extended command:

start
stop
status

services
version
reload

save_config
trace
dbsnmp_start

dbsnmp_stop
dbsnmp_status
change_password

quit
exit
set*

show*

Show the Listener status:
LSNRCTL> status

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

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

32-bit Windows Error: 2: No such file or directory

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=GW0020480835)(PORT=1521)))

TNS-12541: TNS:no listener

TNS-12560: TNS:protocol adapter error

TNS-00511: No listener

32-bit Windows Error: 61: Unknown error

Start the Listener:
LSNRCTL> start

Starting tnslsnr: please wait…

TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 – Production

System parameter file is D:OracleOra81networkadminlistener.ora

Log messages written to D:OracleOra81networkloglistener.log

Trace information written to D:OracleOra81networktracelistener.trc

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=.pipeEXTPROC0ipc)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=GW0020480835.xxx.xx.xx.xxx)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=GW0020480835.xxx.xx.xx.xxx)(PORT=2481))(PROTOCOL_STACK=(PRESENTATION=GIOP)(SESSION=RAW)))

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

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 8.1.7.0.0 – Production

Start Date 02-APR-2003 08:49:13

Uptime 0 days 0 hr. 0 min. 2 sec

Trace Level user

Security OFF

SNMP OFF

Listener Parameter File D:OracleOra81networkadminlistener.ora

Listener Log File D:OracleOra81networkloglistener.log

Listener Trace File D:OracleOra81networktracelistener.trc

Services Summary…

AEF has 1 service handler(s)

CWSS has 1 service handler(s)

Develop has 1 service handler(s)

MyWorkDB has 1 service handler(s)

OEMREPO has 1 service handler(s)

PLSExtProc has 1 service handler(s)

Wanda has 1 service handler(s)

afportal has 1 service handler(s)

The command completed successfully

Stop the Listener:
LSNRCTL> stop

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

The command completed successfully

LSNRCTL>

Exit LSNRCTL
LSNRCTL> exit

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a SID for an instance (usually a database instance) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action: – Wait a moment and try to connect a second time.

– Check which instances are currently known by the listener by executing: lsnrctl services

– Check that the SID parameter in the connect descriptor specifies an instance known by the listener.

– Check for an event in the listener.log file.

ALTER USER ACCOUNT LOCK
/

ALTER USER ACCOUNT UNLOCK
/

Also, you can revoke his create session priviledge. This will prevent that user from logging in, but all his objects will still be available.

Identifying Invalid Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query:
COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = ‘INVALID’
ORDER BY owner, object_type, object_name;

With this information you can decide which of the following recompilation methods is suitable for you.

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types:

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

Notice that the package body is compiled in the same way as the package specification, with the addition of the word “BODY” at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations:

EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PROCEDURE’, ‘MY_SCHEMA’, ‘MY_PROCEDURE’);
EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);
EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);

This method is limited to PL/SQL objects, so it is not applicable for views.

Custom Script

In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, ‘PACKAGE’, 1,
‘PACKAGE BODY’, 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)
AND status != ‘VALID’
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = ‘PACKAGE’ THEN
EXECUTE IMMEDIATE ‘ALTER ‘ || cur_rec.object_type ||
‘ “‘ || cur_rec.owner || ‘”.”‘ || cur_rec.object_name || ‘” COMPILE’;
ElSE
EXECUTE IMMEDIATE ‘ALTER PACKAGE “‘ || cur_rec.owner ||
‘”.”‘ || cur_rec.object_name || ‘” COMPILE BODY’;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||
‘ : ‘ || cur_rec.object_name);
END;
END LOOP;
END;
/

This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.

DBMS_UTILITY.compile_schema

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus:

EXEC DBMS_UTILITY.compile_schema(schema => ‘SCOTT’);

UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below:

PROCEDURE RECOMP_SERIAL(
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);
PROCEDURE RECOMP_PARALLEL(
threads IN PLS_INTEGER DEFAULT NULL,
schema IN VARCHAR2 DEFAULT NULL,
flags IN PLS_INTEGER DEFAULT 0);

The usage notes for the parameters are listed below:

schema – The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads – The number of threads used in a parallel operation. If NULL the value of the “job_queue_processes” parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags – Used for internal diagnostics and testing only.

The following examples show how these procedures care used:

— Schema level.
EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);
EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);

— Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

— Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);

There are a number of restrictions associated with the use of this package including:

Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.

The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.

The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Runnig DDL operations at the same time as this package may result in deadlocks.

utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of “0”. The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
0 – The level of parallelism is derived based on the CPU_COUNT parameter.
1 – The recompilation is run serially, one object at a time.
N – The recompilation is run in parallel with “N” number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

For further information see:
DBMS_UTILITY.compile_schema
UTL_RECOMP

http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php

Oracle Architecture

The Oracle Instance

The memory structures and server processes that do the work in the database.

The System Global Area is a shared block of memory for Oracle’s use. At a minimum contains:

– Redo Log buffer: short term storage for redo information so it can be written to the redo logs.

– Shared Pool: further broken down into the library cache, which holds recently parsed and executed code, and the data dictionary cache, which store recently used object definitions.

– Database buffer cache: Oracle’s work area for executing SQL.

The instance also houses the processes

– System Monitor: Opening database, maintain connection between instance and database

– Database Writer: writes to the database files (writes as little as possible. Minimizing disk I/O for performance)

– Process Monitor: Monitors user sessions

– Log Writer: writes to the redo logs (writes as close to real time as possible. Ideally save all changes.)

– Checkpoint: ensure instance is synchronized with the database from time to time.

– Archiver: writes archived redo logs

The Oracle Database

The database refers to the physical files on the os that contain the data and data dictionary. At the minimum the database requires datafiles, control files, and redo logs.

Parameter File: Holds the parameters to start the instance

Password File: Encrypted file that holds the sysdba password. Allows sys to log on regardless of the state of the database.

Datafiles: Core of the database, the files that hold the data.

Control Files: Holds all the parameters used to connect the instance and database. For Example, pointers to the rest of the database (redo logs, datafiles…) and various data to maintain database integrity (scn and timestamp). Often multiplexed to allow recovery from file corruption.

Redo Log: maintains all changes made to the data over a given period of time or until the log is full. Often multiplexed to allow recovery from file corruption.

Archived Redo Logs: Copies of filled redo logs kept for recovery purposes.

(Special thanks to “Josh” for this information)

Disable PK constraint.
alter table TBL1 disable constraint PK_TBL1 ;

Delete PK index.
alter table TBL1 drop index PK_TBL1 ;

Create PK index.
create unique index “PK_TBL1” on “TBL1” (“INSPECTORID”, “DUTYID”, “INSPID”)
tablespace “TBLSPCINDX”
pctfree 10 initrans 2 maxtrans 255
storage
(
initial 64K
next 0K
minextents 1
maxextents 2147483645
pctincrease 0
freelists 1
freelist groups 1
)
nologging;

Enable PK constraint.
alter table “TBL1” enable constraint “PK_TBL1” ;