System Statistics

To query the existing system statistics:

select pname, pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN';

To query the existing system statistics using dbms_stats.get_system_stats()

SET SERVEROUTPUT ON
DECLARE
STATUS VARCHAR2(20);
DSTART DATE;
DSTOP DATE;
PVALUE NUMBER;
PNAME VARCHAR2(30);
BEGIN
PNAME := 'cpuspeed';
DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab => NULL, statid => NULL, statown => 'SYSTEM');
DBMS_OUTPUT.PUT_LINE('status : 'status);
DBMS_OUTPUT.PUT_LINE('cpu in mhz : 'pvalue);
PNAME := 'sreadtim';
DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab => NULL, statid => NULL, statown => 'SYSTEM');
DBMS_OUTPUT.PUT_LINE('single block readtime in ms : 'pvalue);
PNAME := 'mreadtim';
DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab => NULL, statid => NULL, statown => 'SYSTEM');
DBMS_OUTPUT.PUT_LINE('multiblock readtime in ms : 'pvalue);
PNAME := 'mbrc';
DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab => NULL, statid => NULL, statown => 'SYSTEM');
DBMS_OUTPUT.PUT_LINE('average multiblock readcount: 'pvalue);
END;
/

Ref: http://www.dbaxchange.com/2_3_systemstats.htm

List all existing database links

SET linesize 100
COL username format a20
COL host format a13
COL db_link format a20
COL owner format a10
SELECT db_link, owner, host, username, created FROM DBA_DB_LINKS;

Calculate free space in TEMP tablespace

select tablespace_name, SUM(bytes_used), sum(bytes_free)
from v$temp_space_header
where tablespace_name = ‘TEMP’
group by tablespace_name;

Commands on AIX

***Check OS level***
oracle>/appl/oracle> oslevel -r
5300-05

SQLPlus could not be started

After AIX OS Upgrade, I encountered the following via starting up a SQLPLUS session:

oracle>/appl/oracle/product/9.2.0/bin> sqlplus /"as sysdba"
exec(): 0509-036 Cannot load program sqlplus because of the following errors:
0509-130 Symbol resolution failed for /usr/lib/libc.a[aio_64.o] because:
0509-136 Symbol kaio_rdwr64 (number 0) is not exported from
dependent module /unix.
0509-136 Symbol listio64 (number 1) is not exported from
dependent module /unix.
0509-136 Symbol acancel64 (number 2) is not exported from
dependent module /unix.
0509-136 Symbol iosuspend64 (number 3) is not exported from
dependent module /unix.
0509-136 Symbol aio_nwait (number 4) is not exported from
dependent module /unix.
0509-136 Symbol aio_nwait64 (number 5) is not exported from
dependent module /unix.
0509-136 Symbol aio_nwait_timeout (number 6) is not exported from
dependent module /unix.
0509-136 Symbol aio_nwait_timeout64 (number 7) is not exported from
dependent module /unix.
0509-026 System error: Error 0
0509-192 Examine .loader section symbols with the
'dump -Tv' command.

Cause:

The Anonymous I/O driver was not linked by the Unix Administrator

Solution:

Relink Anonymous I/O driver and Reboot the server.

Unable to connect SQLState=IM004 [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

User can't test local DSN connectivity to an Oracle database and receive:

Unable to connect

SQLState=IM004

[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

Cause:

Oracle Metalink says the Oracle Client needs to be patched to 10.2.0.3 from 10.2.0.1.

Solution:

Alternatively, I simply uninstalled the ODBC driver component from the Oracle Universal Installer, rebooted the PC, reinstalled the same ODBC driver.

ORA-27092: skgfofi: size of file exceeds file size limit of the process

ERROR at line 2:
ORA-01116: error in opening database file 34
ORA-01110: data file 34: '/data/xxx/02/oradata/DM_IDX_1_01.dbf'
ORA-27092: skgfofi: size of file exceeds file size limit of the process
Additional information: 131071
Additional information: 202241

Cause:

The account that is used to run the stored procedure or simple SQL query could yield the above errors. The issue was related to the ULIMIT set to the user account

Solution:

Have UNIX administrator reset the ULIMIT to a higher value or "unlimited"

Using ODBC driver in VBScript to connect to Oracle DB

Dim strCon
strCon = “Driver={Microsoft ODBC for Oracle}; ” & _
“CONNECTSTRING=(DESCRIPTION=” & _
“(ADDRESS=(PROTOCOL=TCP)” & _
“(HOST=Server_Name)(PORT=1521))” & _
“(CONNECT_DATA=(SERVICE_NAME=DB_Name))); uid=system;pwd=system;”

Dim oCon: Set oCon = WScript.CreateObject(”ADODB.Connection”)
Dim oRs: Set oRs = WScript.CreateObject(”ADODB.Recordset”)
oCon.Open strCon

Set oRs = oCon.Execute(”SELECT name from v$database”)
While Not oRs.EOF
WScript.Echo oRs.Fields(0).Value
oRs.MoveNext
WendoCon.Close
Set oRs = Nothing
Set oCon = Nothing
Ref: http://www.codeproject.com/vbscript/connection_string.asp?print=true

Resize redo log files

/* Check logs and log groups */
set linesize 100
col member format a35
SELECT a.group#, a.member, b.bytes
FROM v$logfile a, v$log b WHERE a.group# = b.group#;

/* Check for active log group */
select group#, status, bytes from v$log order by group#;

/* If group 1 is not active from the query above, it's safe to drop it */
alter database drop logfile group 1;
alter database add logfile group 1 (‘/data/ises/01/oradata/redo01.log’) size 450m reuse;

alter system switch logfile;

/* If group 2 is not active from the query above */
alter database drop logfile group 2;
alter database add logfile group 2 (‘/data/ises/02/oradata/redo02.log’) size 450m reuse;

alter database drop logfile group 3;