Month: June 2011

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

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

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

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.