Oracle - CPU used by a database user on per session basis

CPU used by a database user on per session basis on Oracle
DECLARE
v_session_cpu NUMBER;
v_system_cpu NUMBER;
v_cpu_ratio NUMBER;
v_sid NUMBER;
v_serial NUMBER;
v_osuser v$session.osuser%type;
v_username v$session.username%type;
my_exception EXCEPTION;
CURSOR sess IS
SELECT sid,serial#,username
FROM v$session
WHERE username is not null
ORDER BY username;

BEGIN
OPEN sess;
FETCH sess into v_sid,v_serial,v_username;
IF sess%notfound THEN
RAISE no_data_found;
END IF;

LOOP
SELECT value
INTO v_session_cpu
FROM v$sesstat
WHERE sid = v_sid
AND statistic# = 12;

SELECT value
INTO v_system_cpu
FROM v$sysstat
WHERE name = 'CPU used by this session';

v_cpu_ratio := ROUND((v_session_cpu/v_system_cpu)*100,2);

dbms_output.put_line('CPU used by user '||v_username||'('||
''''||v_sid||','||v_serial||''''||') => '||v_cpu_ratio||'%' );

FETCH sess into v_sid,v_serial,v_username;
IF sess%notfound THEN
RAISE my_exception;
END IF;
END LOOP;
CLOSE sess;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No users connected');
close sess;
WHEN my_exception THEN
null;
CLOSE sess;
END;
/

How to tell which IPC resouces belongs to which instance

Chunyu Hu's Weblog - How to tell which IPC resouces belongs to which instance: It's quite common that there are more than 1 Oracle instance on 1 host. For example,we run many standby databases instances on 1 host for cost reason.

Sometimes, we fail to restart some Oracle instance because the IPC resources allocated for it are not cleared up. We can run ipcs to list all the IPC resouces on the host, but the problem is that we have to find only the IPC resources allocated to the specific instance and remove them manually before we restart the specific instance.

sysresv
Oracle provides sysresv to tell out which instance has which IPC resources. Unfortunately, we find sysresv is not reliable many times.

oradebug ipc
SQL>oradebug ipc
On Solaris, if you run this command, it will generate a trace file under the user_dump_dest directory. The trace file will list all the shared memory segment and semaphores. By checking all the in-use IPC resouces for all running Oracle instances and the result from ipcs command, you can know which IPC resouces you can safely to remove.

RMAN-10035 ORA-19502 ORA-27030

RDBMS Version: 8.1.7.2
Operating System & Version: AIX 4.3.2
Error Number (if applicable):
Product (i.e. SQL*Loader, Import, etc.): RMAN w/ TDP
Product Version:
RMAN + TDP backup failure
Hi,
I have Oracle 8.1.7.3 running on an IBM AIX 4.3.2 OS. TDP has been configured w/ RMAN for complete hotbackups. This
setup was working fine for the last 4 months before I started receiving the following error during backup. The failures occur @ haphazard intervals.
On Nov 22 I got the following error:
RMAN-00571: ==========================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03007: retryable error occurred during execution of command: backup
RMAN-07004: unhandled exception during command execution on channel e1
RMAN-10035: exception raised in RPC: ORA-19502: write error on file "/backup/ELASPROD/hbkp_ELASPROD_478596261_1135_1", blockno 14370561 (blocksize=512)
ORA-27030: skgfwrt: sbtwrite2 returned error
ORA-19511: ANS1017E (RC-50) Session rejected: TCP/IP connection failure
RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE
On Nov 23 it was
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03007: retryable error occurred during execution of command: backup
RMAN-07004: unhandled exception during command execution on channel e2
RMAN-10035: exception raised in RPC: ORA-19502: write error on file "/backup/ELASPROD/hbkp_ELASPROD_478682643_1139_1", blockno 9867009 (blocksize=512)
ORA-27030: skgfwrt: sbtwrite2 returned error
ORA-19511: ANS1301E (RC1) Server detected system error
RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE
On Dec 12 it was
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03007: retryable error occurred during execution of command: backup
RMAN-07004: unhandled exception during command execution on channel e1
RMAN-10035: exception raised in RPC: ORA-19502: write error on file "/backup/ELASPROD/archive/arch_ELASPROD_480413812_1330_1", blockno 1574913 (blocksize=512)
ORA-27030: skgfwrt: sbtwrite2 returned error
ORA-19511: ANS1017E (RC-50) Session rejected: TCP/IP connection failure
RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE
On Jan 21 it was
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03007: retryable error occurred during execution of command: backup
RMAN-07004: unhandled exception during command execution on channel e1
RMAN-10035: exception raised in RPC: ORA-19502: write error on file "/backup/ELASPROD/archive/arch_ELASPROD_483781354_1696_1", blockno 1763457 (blocksize=512)
ORA-27030: skgfwrt: sbtwrite2 returned error
ORA-19511: ANS1329S (RC29) Server out of data storage space
RMAN-10031: ORA-19624 occurred during call to DBMS_BACKUP_RESTORE.BACKUPPIECECREATE
I have another database which gets backed up after this 1 is done. i have not faced any such problems w/ the other database.
What should be done?
Thanks in advance.


From: RAMESH VENKATAKRISHNAN 21-Jan-03 18:47
Subject: Re : RMAN + TDP backup failure

Hi

Log Nov 22 and Dec 12 :-
- ORA-19511: ANS1017E (RC-50) Session rejected: TCP/IP connection failure
This might be because of the failure in tcp/ip communications when connecting to the server.
You can try playing with "IDLETIMEOUT" TSM parameter.

Log Jan 21 :-
- ORA-19511: ANS1329S (RC29) Server out of data storage space
This is because your storage pool is full

Log Nov 23 :-
- ORA-19511: ANS1301E (RC1) Server detected system error
This is a generic message. you can get more information from sbtio.log

Ramesh...







From: Oracle, Rowena Serna 23-Jan-03 16:29
Subject: Re : RMAN + TDP backup failure

Thanks to Ramesh for the suggestion, as you can see,
the errors you are seeing are media management errors and you should contact your vendor to address them.


Error: ORA-19511
Text: %s
---------------------------------------------------------------------------
Cause: An error occurred in the media management software which is linked with the Oracle server to perform backup and restore in cooperation with Recovery Manager.
Action: If the text of message 19511 does not provide enough information to resolve the problem, then you should contact the vendor of the media management software.


Regards,
Rowena Serna
Oracle Corporation

From: Jos Doldersum 16-Jun-03 14:58
Subject: Re : RMAN + TDP backup failure

I had to increase the COMMTIMEOUT parameter from 60 to 180 to solve the RC-50 problem.

Starting RMAN backup based on Archive File System Usage

Starting RMAN backup based on Archive File System Usage:
Starting RMAN Backup Based on Archive File System
by Avnish Rastogi

Often when DBAs do export/import to load data from one database to another database, it gerenartes archive logs at very high pace and fills up archive file system in couple of hours. Below script will help DBA in scheduling RMAN backup based on file system usage. Please change the location of logfile, cmdfile and RMAN catalog user/password before using this script.

##########################################################################
# Description: Purpose of this script is to kick off RMAN backup for archive logs when archive file
#
##########################################################################
#!/bin/ksh -x

if [ $# != 1 ]
then
echo 'Usage: feeSpace.ksh %Used' 1>&2
exit 1
fi

FSUsed=$1

#GET LOG ARCHIVE DEST FROM V$PARAMETER

LogArchiveDest=`$ORACLE_HOME/bin/sqlplus -s '/as sysdba' SELECT value FROM v\\$parameter WHERE name='log_archive_dest';
exit;
!`

#Define Command Variables used by RMAN

MSGLOG=/tmp/arch_RMAN.log
CMDFILE=/tmp/arch_RMAN.rcv
RMANCatalog=user/passwd@service.org

set `df -P $LogArchiveDest |grep -v Filesystem`
PercentUsed=`echo $5|cut -f1 -d '%'`
FS=$6

if [ '$PercentUsed' -gt '$FSUsed' ]
then
$ORACLE_HOME/bin/arman target / catalog $RMANCatalog cmdfile $CMDFILE msglog $MSGLOG
fi


Rman Script for kicking off archive backups in Oracle 9I

run {
BACKUP ARCHIVELOG ALL DELETE INPUT
FILESPERSET 6
FORMAT '%d_%D%M%Y.%s';
}

Rman Script for kicking off archive backups in Oracle 8I

run {
allocate channel t1 type 'SBT_TAPE';
#backup all archive logs
sql 'alter system archive log current';
backup
filesperset 5
format 'al_%s_%p_%t'
(archivelog all delete input);
sql 'alter system archive log current';
sql 'alter database backup controlfile to trace';
resync catalog;
}

SQL Server - attach all the datababses

attach all the datababses on a SQL Server:
If you are trying to attach all the files to a simillar location as the current server, you can use the script below. It will print out the script with sp_attach_db statements for all the databases. It doesn't look for secondary files and you can update the script accordingly.
--Script to attach all User databases to original location.
declare curname cursor for select dbid from sysdatabases where dbid > 4
open curname
declare @dbid int
fetch next from curname into @dbid
while @@fetch_status = 0
begin
declare @sql varchar(500)
declare @datafile varchar(100)
declare @logfile varchar(100)
select @datafile=filename from sysaltfiles where dbid = @dbid and groupid = 1
select @logfile=filename from sysaltfiles where dbid = @dbid and groupid = 0
--print @logfile
set @sql = 'sp_attach_db ' db_name(@dbid) ',''' ltrim(rtrim(@datafile)) ''',''' ltrim(rtrim(@logfile)) ''' ;'
print @sql
fetch next from curname into @dbid
end
close curname
deallocate curname

ORACLE-BASE - Oracle9i DBNEWID Utility

ORACLE-BASE - Oracle9i DBNEWID Utility

DBNEWID Utility

Changing an Oracle DBID (Database Identifier) in Oracle 8i and 9i Release 2

Changing an Oracle DBID (Database Identifier) in Oracle 8i and 9i Release 2

Oracle 9i Release 2 (9.2.0.1)

In Oracle 9i Release 2 (9.2.0.1), Oracle provides the ability to change the DBID and/or DBNAME through a new feature, the DBNEWID utility, and works outside of RMAN. Complete documentation on this utility is in the Oracle 9i Database Utilities Release 2 (9.2) part number A96642-01 manual.

This paper does not cover Oracle 9i Release 1, as I did not try the following procedures against it. The rest of this paper covers Oracle 8i.

Oracle 8i

In Oracle 8i, Oracle provides the procedure “dbms_backup_recovery.zeroDbid” for this purpose. While Oracle does not formally support it, Oracle note 174625.1 covers its usage.

If you are using RMAN and choose to use the following method based on Note 174625.1, please keep in mind Notes 105832.6 and 1062736.6 Should RMAN already know about the original and clone databases, you will need to deregister and re-register the databases with RMAN.

The basic concept is:

1. With the database in mount but NOT open mode.
2. Use dbms_backup_recover.zeroDbid to zero the DBID
3. Recreate the control files to assign a new DBID to the database.

DISCLAIMER

Neither Oracle support nor I will provide support for the following procedure. I can only say it worked for me. You perform it at your own risk. According to Oracle Support it runs the risk of rendering the database unusable, and therefore exists for the purpose of testing in a non-production environment. Never use this procedure on a production system; it should only be run on clone copies.

As always, before any major or significant changes to a database, you should perform a cold (closed) backup.
Step by Step Instructions
To Change an Oracle Database Identifier (DBID)

1. Connect to the database in SYSDBA mode.
2. Perform a clean shutdown. Do NOT abort or force the instance. This shutdown is the same as when you are going to do a cold / closed database backup. You may wish to perform a cold / closed database backup at this time, as a measure of protection.

shutdown immediate

3. Startup the database in mount mode

statup mount

4. Check the present (soon to be old) database ID (DBID)

select dbid, name

from v$database;



DBID NAME

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

578456994 SNEAKERS



Elapsed: 00:00:00.20

5. Generate the create controlfile statement into a trace file.

alter database backup controlfile to trace;

6. Generate a new DBID using the zeroDbid function of dbms_backup_restore

execute dbms_backup_restore.zeroDbid(0)

File: DBMSBKRS.SQL

NOTE: The value zero (0) is a special case, which is the case we need.



PROCEDURE zeroDbid(fno IN binary_integer);



-- This procedure is used to update the header of a datafile so that the

-- a new dbid would be calculated later on with a create controlfile

-- statement. This is necessary for the cloning of a database

-- The file to update is identified by the fno input parameter, a special

-- case is allowed when fno == 0, such that all the headers of the datafiles

-- in the control file are zeroed out.

-- Three fields in the datafile header (stored in the first block)

-- are zeroed out:

-- The field that holds the database id

-- The checksum field

-- The bit that signals that the checksum is valid

--

-- There are no return values

--

-- Input parameters:

-- fno

-- Identifies the file number of the datafile that will be modified

-- If it is zero, all datafiles listed in the controlfile are updated.

-- Output parameters:

-- NONE



7. Get another clean shutdown.

shutdown normal

8. Delete the controlfiles (Unix: rm; Windows erase).



9. Start up the database in nomount mode.

startup nomount

10. Recreate the control files modifying it for your database and possible new name (if this is a new clone). You can only use the instructions for recreating the controlfile (create controlfile).

Change from:

CREATE CONTROLFILE REUSE DATABASE "SNEAKERS" NORESETLOGS NOARCHIVELOG

Change to:

CREATE CONTROLFILE SET DATABASE RESETLOGS NOARCHIVELOG

Use only the CREATE CONTROLFILE command from this trace file. Remove all the other statements (e.g. recover, etc.)

11. Open the database.

alter database open resetlogs;

12. Check the new DBID. It should be different.

select dbid, name

from v$database;



DBID NAME

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

689567005 SNEAKERS



Elapsed: 00:00:00.20

How to script a SQL Server a Job with Visual Basic and SQL-DMO...

How to script a SQL Server a Job with Visual Basic and SQL-DMO...
Try this. Fill in the blanks as indicated by << >>. Put it in a job with a ActiveX Script step.
One thing you might want to check is where the filename is generated. If you have any weird characters it might generate an invalid filename.

Dim conSrvr, Srvr, oJB, strJob
Dim fso, iFile, strFldr, strFilename
Dim dteNow, strDate

strServer = "<<>>"

dteNow = Now()
strDate = CStr(YEAR(dteNow))
strDate = strDate & RIGHT("00" & MONTH(dteNow),2)
strDate = strDate & RIGHT("00" & DAY(dteNow),2)
strDate = strDate & RIGHT("00" & HOUR(dteNow),2)
strDate = strDate & RIGHT("00" & MINUTE(dteNow),2)
strDate = strDate & RIGHT("00" & SECOND(dteNow),2)

strFldr = "<<>>" & strServer & "\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set conServer = CreateObject("SQLDMO.SQLServer")
conServer.LoginSecure = True
conServer.Connect strServer

For Each oJB In conServer.JobServer.Jobs
strFilename = strFldr & oJB.Name & ".sql"

Set iFile = fso.CreateTextFile(strFilename, True)

strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & "-- SCRIPTING JOB: " & oJB.Name & vbCrLf
strJob = strJob & "--------------------------------------------------" & vbCrLf
strJob = strJob & oJB.Script() & vbCrLf

iFile.Write (strJob)
iFile.Close

Next

Set conServer = Nothing
Set fso = Nothing

Monitoring Oracle processes on Windows

Monitoring Oracle processes on Windows NT:
Monitoring Oracle processes in Windows NT
Ravinder Bhalla
06 Oct 2004
Rating: -4.33- (out of 5)

It's much easier to monitor CPU usage in a Unix environment for each individual Oracle process (i.e., PMON, SMON, DBWR) as compared to NT. In NT, there is only a single process (i.e., oracle.exe). In order to get the status of individual threads of Oracle processs in a Windows environment, use Quick Slice, a GUI-based standalone application. It's a free download from Microsoft. It works with Oracle8i and 9i in all Windows environments. Here is how to use it:

1. First of all, you need to find the process id of all threads running in Oracle.

SQL> select p.spid "Thread ID", b.name "Background Process", s.username
"User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr and b.paddr(+) = p.addr;

Here is the output:

Thread ID Backg User Name OS User STATUS Session ID Serial No.
OS Program
------------ ----- ---------- ---------- -------- ---------- ----------

20229 PMON Oracle ACTIVE 1 1
oracle@db02.s0.gc.media.com (PMON)
20231 DBW0 Oracle ACTIVE 2 1
oracle@db02.s0.gc.media.com (DBW0)
20233 LGWR Oracle ACTIVE 3 1
oracle@db02.s0.gc.media.com (LGWR)
20235 CKPT Oracle ACTIVE 4 1
oracle@db02.s0.gc.media.com (CKPT)
20237 SMON Oracle ACTIVE 5 1
oracle@db02.s0.gc.media.com (SMON)
20239 RECO Oracle ACTIVE 6 1
oracle@db02.s0.gc.media.com (RECO)
20241 ARC0 Oracle ACTIVE 7 1
oracle@db02.s0.gc.media.com (ARC0)
20243 ARC1 Oracle ACTIVE 8 1
oracle@db02.s0.gc.media.com (ARC1)
20245 DMON Oracle ACTIVE 9 1
oracle@db02.s0.gc.media.com (DMON)
18460 SYS Oracle ACTIVE 17 22737
sqlplus@db02.s0.gc.media.com (TNS V1-V3)
15700 Oracle ACTIVE 12 7
oracle@db02.s0.gc.sj.ipixmedia.com (LNS0)

In this example, thread 20229 is PMON and 20231 is DBW0, etc.

2. Now doubleclick on the qslice.exe file under "c:Program FilesResource Kit."

3. To display the individual threads of a process, find the process -- oracle.exe and double click on it.

Another graphical window will pop up showing all of the threads and the cpu usage of each.

4. To identify the threads, match up the TID column to the thread ID column of the previous SQL statement. To match the TID numbers with the Oracle thread process ID you will need to convert the TID number from HEX to DEC using a scientific calculator.

The red bar displays kernel time taken by that thread; the blue bar displays user time.

ITworld.com - Zipping your way to free space: Part 2

ITworld.com - Zipping your way to free space: Part 2: "bash-2.03$ ./compressTest

real 1:55.6
user 1:41.1
sys 11.2
compress 57% reduction

real 54.6
user 29.1
sys 22.1
pack 26% reduction

real 2:33.0
user 2:23.6
sys 5.7
gzip 71% reduction

real 2:39.5
user 2:29.2
sys 6.1
zip 71% reduction

real 11:35.1
user 11:14.3
sys 4.5
bzip2 74% reduction

As you can see from these results, my best compression ratio resulted from the bzip2 compression, though this command took quite a bit more time to run than all of the other commands. Whether or not compression time should be given serious consideration in determining which compression tool you should use probably depends on how many files you need to compress and whether you need to compress these files in a narrow window.

The gzip and zip commands were neck in neck -- both with respect to compression and with respect to running time. Since my calculation ignored decimal places, any difference in the performance of these two tools has been obliterated by the simplicity of my math.

The script that I used to generate these time and compression results is shown below. Notice that I take care to preserve the original file, so that it is available for each compression operation. Also, because the syntax for the zip command is different -- requiring a file name to be specified -- I put my compression commands in a case statement.


#!/bin/bash

if [ '$1' == '' ]; then
echo -n 'file> '
read $file
else
file=$1
fi

orig_sz=`ls -l $file | awk '{print $5}'`
mv $file $file.$$

for tool in compress pack gzip zip bzip2
do
cp $file.orig $file
case $tool in
compress) time compress $file
comp_sz=`ls -l $file.Z | awk '{print $5}'`
;;
pack) time pack $file > /dev/null
comp_sz=`ls -l $file.z | awk '{print $5}'`
;;
gzip) time gzip $file
comp_sz=`ls -l $file.gz | awk '{print $5}'`
;;
zip) time zip $file.zip $file > /dev/null
comp_sz=`ls -l $file.zip | awk '{print $5}'`
;;
bzip2) time bzip2 $file
comp_sz=`ls -l $file.bz2 | awk '{print $5}'`
;;
esac

percent=`expr $comp_sz \* 100 / $orig_sz`
reduction=`expr 100 - $percent`
echo $tool ${reduction}% reduction
done

mv $file.$$ $file
# rm -i $file.*


Starting with a file name "myfile", you will end up with a series of files such as those shown below. If you don't want to keep these files around after you've completed your timing/efficiency tests, uncomment the remove command at the bottom of the script.

-rw-r--r-- 1 shs staff 291209216 Feb 3 10:14 myfile
-rw-r--r-- 1 shs staff 127372957 Feb 3 10:16 myfile.Z
-rw-r--r-- 1 shs staff 76466288 Feb 3 10:27 myfile.bz2
-rw-r--r-- 1 shs staff 85682214 Feb 3 10:20 myfile.gz
-rw-r--r-- 1 shs staff 217362875 Feb 3 10:18 myfile.z
-rw-r--r-- 1 shs staff 85682333 Feb 3 10:26 myfile.zip

Next week, we will examine the remaining compression considerations -- whether the compression algorithm is patented and whether the compression commands have other limitations that you need to consider.

"

Oracle - Monitoring free memory in Large Pool and Shared pool

Monitoring free memory in Large Pool and Shared pool on Oracle
Monitoring Free Memory in Large Pool and Shared Pool (Oracle)
by Avnish Rastogi

The below process was developed to monitor free memory in shared pool and large pool proactively. Lack of memory in these two pools will cause Oracle to generate ORA-4031. Another reason for ORA-4031 is memory pool fragmentation. When we try to allocate large chunk of memory, Oracle will first flush the memory segments, which aren’t in use and merge them. If there is still not enough large piece of continuous memory, Oracle will generate ORA-4031. If your application uses Java, then Oracle may also generate ORA-4031 if Java pool is not big enough.



Oracle provides several System tables, which DBA can use to troubleshoot ORA-4031 errors.



V$SQLAREA—Oracle uses this view to store information of every sql statement and pl/sql block executed since your database is started.



X$KSMLRU—Oracle uses this view to keep tracks allocation in shared pool that causes other objects to aged out.

X$KSMSP—You can use this view to find out how free space is currently allocated.

V$SGASTAT—Oracle uses this view to displace detailed information about SGA allocation. You can use this view to see free memory in share pool and large pool. I used primary v$SGASTAT to develop following process for monitoring free memory in shared pool and large pool.



CREATE OR REPLACE MemoryMgmt

(

mem_threshold IN NUMBER /* IN MB */

) IS

large_free_mem NUMBER;

shared_free_mem NUMBER;

datetime VARCHAR2(20);

message VARCHAR2(200);

BEGIN

EXECUTE IMMEDIATE

'SELECT bytes FROM v$sgastat

WHERE pool = ''large pool'' AND name = ''free memory'''

INTO large_free_mem;



EXECUTE IMMEDIATE

'SELECT bytes FROM v$sgastat

WHERE pool = ''shared pool'' AND name = ''free memory'''

INTO shared_free_mem;



SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') INTO datetime FROM dual;



-- If Free Large Memory is less than INPUT memory then write an alert in alert log.

-- If Free Large Memory is greater than INPUT memory then write information in alert log.

IF ((large_free_mem < (mem_threshold*1024*1024)) OR (shared_free_mem < (mem_threshold*1024*1024)))

THEN

message := datetime || ' ** MM-0001 ** Large Pool Free Memory : ' || TO_CHAR(large_free_mem/1024/1024,'9999.99') || ' and Shared Pool Free Memory : ' || TO_CHAR(shared_free_mem/1024/1024,'9999.99') || ' MB. **';

ELSE

message := datetime || ' ** Large Pool Free Memory : ' || TO_CHAR(large_free_mem/1024/1024,'9999.99') || ' and Shared Pool Free Memory : ' || TO_CHAR(shared_free_mem/1024/1024,'9999.99') || ' MB. **';

END IF;

DBMS_SYSTEM.KSDWRT(2, message);

END MemoryMgmt;



Note that DBMS_SYSTEM package is not a documented package from Oracle and KSDWRT procedure allows to write any string into Oracle Alert Log file.



You can use two approach to resolve memory issue; one is to allocate memory using ‘alter system’ command another approach is to use any monitoring utility to monitor oracle alert log and generate email or page to OnCall DBA to allocate memory. We used second approach and used BMC Log Monitor to monitor MM-0001 string and generate trouble ticket to OnCall person. In our environment we are running thing procedure every 15 minutes using Oracle DBA jobs.

Get CD Key and MDAC Version from SQL Server 2000

Get CD Key and MDAC Version from SQL Server 2000 - ExtremeExperts: How can I find the SQL Server CD key I used to install? It would be great to get this information from my desktop.

Answer:

As said the easy way is to get to the remote machines registry key settings and search for the same. The location to look for this CD key is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\registration\CD_KEY

More often than not this is not a feasible option. But donot deter, here is a cool undocumented command that can help you get this information from the registry. It is called the xp_regread . As the name suggests its used to read the registry values.
USE master
EXEC xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\80\registration',
'CD_KEY'

I think the three parameters are self explanatory seeing the registry key we have to access. Since this extended stored procedure is un-documented. Use this with care. The systems adminsitrator can disable all these stored procedures at your work place ... :) ... And Microsoft can remove these stored procedures without warning.

Having seen the use of this extended procedure usage, lets get one more important registry key. I've also been asked how can we find the MDAC Version from the ISQLW window. And working on the same logic here is the solution.

EXEC master..xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\DataAccess',
N'Version'

I think you should be able to decifer the appropriate registry key we are trying to access.

Using Excel to Pull Data from SQL Server

Using Excel to Pull Data from SQL Server
Using Excel to Pull Data from SQL Server
Michael Schmalz

Before I start, I want to emphasize that this example uses Excel for the functionality as opposed to Enterprise Manager or any of the SQL Server User Interface functionality. In the first article that I wrote about getting data from SQL Server to Excel, you were shown how to push data into Excel using DTS. This is useful in many cases when you are dealing with large datasets. But, if you want access to specific pieces of data in real time, then DTS is not the method to use. In cases like this, two very viable options are to use Microsoft Access or Microsoft Excel. You could use Microsoft Word if you needed to mail letters to customers meeting some criteria, but similar concepts that you would use in Excel apply to Word as well. Your decision about whether to use Access or Excel will depend mostly on what you want to do with the data after you get it. For example, if you just need to page through data and don’t need calculations and don’t need to do anything else with the data, then Access is a good choice. If you need the data in a report format with or without calculations and don’t need to do anything else with the data, Access Reports or Excel could work. In that case just pick the one you are most comfortable using. But, if you want access to the data and need to do calculations and also need to be able to reference the data for other purposes, then Excel can be an excellent choice. It is also worth noting that many companies do not put Microsoft Access on everyone’s desktop. So, in those cases Excel is going to be the go to application.

Pulling Data with a Query

There is a query tool that comes with Excel that can be used to get data from a variety of sources. However, it is not easy to modify your query on the fly and you don’t have a whole lot of control over where the data goes. It simply takes a result set and places it in your worksheet where you tell it. If you want to change the number of parameters and their values on the fly and do different things with each record, then this is not the tool for you.

One of the other options you have is to use Visual Basic for Applications (VBA) and ActiveX Data Objects (ADO) to manage the data. That is the topic of this article. After you are done reading this article, you will understand how to: build a connection string to SQL Server, connect to SQL Server, execute a query, and navigate through the resulting recordset.

Step 1 – Get Connection Information on Your Server

Your first order of business is to determine how you want to connect to your server. If you are currently using your server with MS Access, then it is likely that you already have a DSN set up with your server information in it. You can easily use that DSN through VBA. If you don’t have a DSN set up, then you will need to get the connection information to your server. The information that you need is the server name or IP address, the database name, your username, and your password. I wrote this article using this information instead of a DSN. If you are using a DSN or use Windows Authentication instead of a password, then your connection string will change slightly. There are many examples about how to change your connection string for these different methods in the ADO documentation.

Step 2 – Get Excel Ready

The next thing that you must do is start-up Excel and get into a new workbook. Then, hit Atl-F11 to bring up the Visual Basic Editor. In the default view, you will see the project explorer on the left hand side of your screen with a treeview list of items. If it is not already expanded, you should click the plus sign on the listing that says “VBAProject (filename.xls)” and then right-click on the listing that says “Microsoft Excel Objects”. You will see a pop-up list with several options. You need to select Insert – Module. This will get you a blank module. As a default, ActiveX Data Objects is not available for use from VBA. In order to make ADO available for use, you need to go to the menu bar and select Tools – References. From the box that comes up, you need to scroll down the list and check the box for “Microsoft ActiveX Data Objects 2.x Library”. You should generally select the highest version number. The only caveat to this is when it will be used by multiple users. In cases like this, you should select the version that is on everyone’s computer – or better yet install the latest version on everyone’s computer. Now, you are ready to write your code.

Step 3 – Write Your Code

Your next decision is whether you want to set up a generic procedure to connect to SQL Server or if you want to simply have it as part of your module. For the purposes of this article, the connection to SQL Server will be part of the module, although the information for the connection will come from the Excel Workbook.

On the Project Explorer, you will see a treeview for Modules and a module named “Module1”. Double-click on “Module1” to bring up the blank module. Next, go to the menu bar and click on Insert – Procedure. You will want the radio button for Sub to be selected and the radio button for Public to be selected. For the name of the procedure, let’s call it GetMyData.

With the procedure ready, it is time to go back to the Excel Workbook. In Sheet1, set up your worksheet like the figure below.

You could also create a field for password, but for the purposes of this article I have the password coming from an inputbox. I would not suggest storing a password in your worksheet, but it can be done. The ADO objects that you will need are Connection, Recordset, and Field. Also, when you type in your Fields and Criteria, you must use the ' character to start your entry so that Excel does not think you are entering a formula. For the rest of the article, refer to the code below:

Public Sub GetMyData()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim adofld As ADODB.Field

Dim server As String
Dim dbname As String
Dim usernm As String
Dim tblname As String
Dim pword As String
Dim sqlstr As String
Dim whrclse As String
Dim x As Integer
Dim y As Integer

Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Set xlws = Sheets("Sheet1")

pword = InputBox("Please enter your password.", "Password Prompt")

server = xlws.Range("B3").Value
dbname = xlws.Range("B4").Value
usernm = xlws.Range("B5").Value
tblname = xlws.Range("B6").Value

Set adoconn = New ADODB.Connection
Set adors = New ADODB.Recordset

adoconn.ConnectionString = "Provider=sqloledb;Datasource=" & server & _
";Database=" & dbname & ";uid=" & usernm & _
";pwd=" & pword

adoconn.Open

sqlstr = "Select " & tblname & ".* from " & tblname

x = 12
y = 0

While xlws.Cells(x, 1).Value <> ""
y = y + 1
whrclse = whrclse & " AND " & xlws.Cells(x, 1).Value & _
" " & xlws.Cells(x, 2).Value
x = x + 1
Wend

If y <> 0 Then
whrclse = " WHERE 1 = 1 " & whrclse
sqlstr = sqlstr & whrclse
End If

adors.Open sqlstr, adoconn, adOpenStatic
Debug.Print sqlstr
Set xlws = Sheets("Sheet2")
xlws.Activate

x = 1

For Each fld In adors.Fields
xlws.Cells(1, x).Value = fld.Name
x = x + 1
Next fld

Set xlrng = xlws.Range("A2")
xlrng.CopyFromRecordset adors
xlws.Columns.AutoFit


adors.Close
adoconn.Close
Set fld = Nothing
Set adors = Nothing
Set adoconn = Nothing
Set xlrng = Nothing
Set xlws = Nothing

End Sub

In this code, there are a lot of things going on. The two concepts that I want to cover are the ones that are not necessarily covered in the documentation that comes with ADO. The first is the building of the where clause. What I am trying to accomplish with the Excel Worksheet is the ability for a user to enter as many fields and criteria as they wish. The code is going to start at the first row where fields and criteria are entered and then continue until a blank row is encountered. In order to not have to check for the first row to determine if an “AND” is needed, what I have done is created a first part of the where clause that will always be true “1 = 1”. You could use other expressions that evaluate to true or capture the first row separately and then place “AND”s in subsequent rows. But, this way is very easy. The second item that I would like to call your attention to is the line “For Each fld in adors.fields”. What I am doing there is getting the field name for each field in the recordset so that I can put column headers on.

You may also notice the Excel objects that are being used – the Worksheet Object and the Range Object. As you type this code, you will notice that there are many other properties and methods that you can access. In this case, we are using the CopyFromRecordset method of the Range Object. This method takes the entire recordset and starts the copy and paste in the range specified. The second method being used is the AutoFit method. This is setting up the Worksheet to be more readable.

Final Step

The final step is to place a command button on the Worksheet. You do this by opening up the Visual Basic Toolbar from the View > Toolbars menu. Then, you open up the Control Toolbox from the toolbar. You will see a command button on this toolbox and you simple click on it and then click and drag on your worksheet to draw the command button that you want. When you have done this, you can right-click on it to change properties (from here you can change color, caption, etc.). Then, right-click on it again and go to view code. Your one line of code for the OnClick event is:

Private Sub CommandButton1_Click()
GetMyData
End Sub

You can then fill out the sheet with your information and click the button. The code will run and place the recordset on Sheet2. You could obviously set this up with other sheet names, etc. Some suggested uses for the resulting worksheet can be a report on another sheet that refers to Sheet2, setting criteria for transactions that need reviewed, or any other time that you need to get a recordset into Excel for analysis.

Conclusion

While as an administrator it is unlikely that you will need this type of setup for your work, a method like this makes it possible for you to give recordset viewing capabilities to people on their desktop as long as Excel and MDAC are installed. My guess is that you would not use this code verbatim, but might be able to tweak the code and put it to good use. A final note that you can do is place all of the server information and user names and passwords into the Excel code and then protect the code with a password. You can set up a generic read only login that you only give access to specific views on the server. This would enable several people to use the workbook and they would never know the name of the server, the login, etc. But, how you set it up is up to you.