Oracle DBA Daily Experiences

Using the following script it will find all version of a command in $PATH (from Unix Power Tools)

$ cat /tmp/whereiz
#!/bin/sh
testx="test -x"

fixpath="`echo $PATH | sed \
-e 's/^:/.:/' \
-e 's/::/:.:/g' \
-e 's/:$/:./'`"

# echo $fixpath
# IFS has a colon, space and a tab
IFS=": "
for command
do
where=""
for direc in $fixpath
do $testx $direc/$command && where="$where $direc/$command"
done
case "$where" in
?*) echo $where ;;
esac
done

Example
$ /tmp/whereiz ls grep
/bin/ls /tmp/ls
/bin/grep

http://msdn.microsoft.com/en-us/library/ms186256.aspx

By querying msdb.dbo.backupset and msdb.obackupmediafamily one can see backup history like start and end time, type of backup, location of the backup.

select bs.database_name
, bs.type
, bs.backup_start_date
, bs.backup_finish_date
, bmf.physical_device_name
FROM msdb.dbo.backupset bs
, msdb.dbo.backupmediafamily bmf
where bmf.media_set_id = bs.media_set_id
and backup_start_date > ’2010-01-22′

testDB D 2010-01-22 00:00:02.000 2010-01-22 05:09:32.000 c:\Backup\testdb_full.bak
testDB I 2010-01-23 02:26:21.000 2010-01-23 02:30:28.000 C:\Backup\testdbdiff20100123_022621.bak

Type – D = Database, I – Differential Database, L – Log

Documentation on backupset:
SQLServer 2008 – http://technet.microsoft.com/en-us/library/ms186299.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms186299%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260602%28SQL.80%29.aspx

Documentation on backupmediafamily:
SQLServer 2008 http://technet.microsoft.com/en-us/library/ms190284.aspx
SQLServer 2005 – http://technet.microsoft.com/en-us/library/ms190284%28SQL.90%29.aspx
SQLServer 2000 – http://technet.microsoft.com/en-us/library/aa260605%28SQL.80%29.aspx

To find the trace file one using oradebug one can find it using the example below:

SQL> oradebug mysetpid
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_9964.trc

If you try to run the above command without attaching to a session it will return ORA-00074
SQL> oradebug tracefile_name
ORA-00074: no process has been specified

By calling msdb..sp_get_composite_job_info or msdb.dbo.sp_help_job one can find the current jobs running on SQLServer.

Example:

exec msdb..sp_get_composite_job_info @execution_status=1
Or
exec msdb.dbo.sp_help_job @execution_status = 1

Sample output:
job_id originating_server name
———————————— —————————— ———————
7E043796-44F3-4ABF-A047-AA27691DF674 sqlserver01 job_name

Using the following PL/SQL code one can find average size of a row in a table, the following code samples the first 100 rows. It expects 2 parameters table owner and table_name.

DECLARE
l_vc2_table_owner VARCHAR2(30) := '&table_owner';
l_vc2_table_name VARCHAR2(30) := '&table_name';
/* sample number of rows */
l_nu_sample_rows NUMBER := 100;

/* loop through columns in the table */
CURSOR l_cur_columns IS
SELECT column_name, data_type FROM dba_tab_columns
WHERE owner = l_vc2_table_owner
AND table_name = l_vc2_table_name;
l_rec_columns l_cur_columns%ROWTYPE;
l_vc2_sql VARCHAR2(10000);
l_avg_row_size NUMBER(10,2);
BEGIN
l_vc2_sql := '';
OPEN l_cur_columns;
FETCH l_cur_columns INTO l_rec_columns;
/* loop through columns */
WHILE l_cur_columns%FOUND
LOOP
/* if LOB datatype use dbms_log.get_length to find length */
IF l_rec_columns.data_type = 'CLOB' OR l_rec_columns.data_type = 'BLOB' THEN
l_vc2_sql := l_vc2_sql || 'NVL(dbms_lob.getlength(' || l_rec_columns.column_name || '), 0) + 1';
ELSE
l_vc2_sql := l_vc2_sql || 'NVL(VSIZE(' || l_rec_columns.column_name || '), 0) + 1';
END IF;
FETCH l_cur_columns INTO l_rec_columns;
IF l_cur_columns%FOUND THEN
l_vc2_sql := l_vc2_sql || ' + ';
END IF;
END LOOP;
IF l_vc2_sql IS NOT NULL THEN
l_vc2_sql := 'SELECT 3 + AVG(' || l_vc2_sql || ') FROM ' || l_vc2_table_owner || '.' || l_vc2_table_name
|| ' WHERE rownum < ' || l_nu_sample_rows;
EXECUTE IMMEDIATE l_vc2_sql INTO l_avg_row_size;
dbms_output.put_line(l_vc2_table_owner || '.' || l_vc2_table_name || ' average row length: ' || l_avg_row_size);
ELSE
dbms_output.put_line('Table ' || l_vc2_table_owner || '.' || l_vc2_table_name || ' not found');
END IF;
END;
/
Output
Enter value for table_name: TEST_OBJECTS
old 3: l_vc2_table_name VARCHAR2(30) := ‘&table_name’;
new 3: l_vc2_table_name VARCHAR2(30) := ‘TEST_OBJECTS’;
SCOTT.TEST_OBJECTS average row length: 76.88

PL/SQL procedure successfully completed.

Now Oracle has a quick way to find users with detault password.

SQL> SELECT * FROM dba_users_with_defpwd;

And here is the output:

USERNAME
——————————
DIP
MDSYS
WK_TEST
CTXSYS
OLAPSYS
OUTLN
EXFSYS
SCOTT
MDDATA
ORDPLUGINS
ORDSYS
XDB
LBACSYS
SI_INFORMTN_SCHEMA
WMSYS

– print usernames and the roles assigned to the user, it can be run against any database and it will print the users and the roles assigned to different users
SELECT sl.name, g.name
FROM sysusers u, sysusers g, sysmembers m, master.dbo.syslogins sl
where g.name IN (SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1))
and g.uid = m.groupuid
and sl.sid = u.sid
and g.issqlrole = 1
and u.uid = m.memberuid
and sl.denylogin = 0
order by sl.name

Sample output:
localuser db_owner
localuser db_datareader
domain\ADuser db_datareader
domain\ADuser db_denydatawriter
….

Using ANALYZE TABLE owner.table_name COMPUTE STATISTICS one can find if the table has chained rows.

Example:

– analyze again
SQL> analyze table scott.chain_table COMPUTE STATISTICS;

Table analyzed.

– shows row chaining occurred when existing rows were updated and percent of rows
SQL> SELECT chain_cnt,
round(chain_cnt/num_rows*100,2) pct_chained
FROM user_tables
WHERE table_name = 'CHAINED_ROW';

CHAIN_CNT PCT_CHAINED AVG_ROW_LEN PCT_FREE PCT_USED
———- ———– ———– ———- ———-
76824 58.61 16 10 40

Using this SQL one can find the snapshot interval and snapshot retention.

SQL> SELECT extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_Interval,
extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_Interval
FROM dba_hist_wr_control;

Snapshot_Interval Retention_Interval
—————– ——————
60 10080