Posted on Leave a comment

Example Function to Look up Top Level Org


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;

Posted on Leave a comment

Clear Transaction Logs

This is how to disable archiving:

C:> sqlplus sys/@dbalias as sysdba
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> archive log list
SQL> alter system switch logfile;

Run this about 10 times, delete all archived log files and then proceed.

This is how to enable archiving:

SQL> archive log list

At this point the numbers should have incremented as many as the number of switches.

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

SQL> archive log list

SQL> alter system switch logfile;

Run this a couple of times and you should see new archived log files.

SQL> archive log list

At this point the numbers should have incremented as many as the number of switches.

Exit SQLPlus and do an immediate full backup of the system.

Posted on Leave a comment

Drop all objects in schema

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

Posted on Leave a comment

Listener Commands

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.