Oracle DBA Daily Experiences

To change the logical name of the SQL Server databse one can follow the steps below to change the logical name of database data file and log file. You may want to take a backup of the database before applying the change to ensure you have a rollback.

Example:
Use test
select fileid, name, filename from sysfiles
fileid name filename
1 test C:\MSSQL\Data\test.mdf
2 test_Log C:\MSSQL\Data\test_log.LDF

– rename data file
ALTER DATABASE Monitor MODIFY FILE (NAME = test, NEWNAME = App_Data)
GO
– rename log file
ALTER DATABASE Monitor MODIFY FILE (NAME = test_Log, NEWNAME = App_Log)
GO

– verify change
select fileid, name, filename from sysfiles
fileid name filename
1 App_Data C:\MSSQL\Data\test.mdf
2 App_Log C:\MSSQL\Data\test_log.LDF

Using the script one can set the title of a window, one of the reason for doing would be distinguish a window from another. The following example was tried on AIX.

$ cat settitle.ksh
TITLEBAR=”33]0;${1} 07″
echo $TITLEBAR

# in this example the title of the window will change to “Hi”
$ ./settitle.ksh Hi

Using the SQL below one can list SQL Server tables

Use testDB
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;

table_name
———–
ExceptionLog
Employee
…

Using the example below one can view the contents a compress tar file without un-compressing the file.
# if the file is compressed using compress
$ uncompress -c file.Z | tar -tvf -
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 a
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 b

$ uncompress < file.tar.Z | tar -tvf -
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 a
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 b

# if the file is compressed using gzip
$ cat file.tar.gz | gunzip | tar -tvf -
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 a
-rw-r–r– 2054 202 0 May 20 22:17:19 2009 b

In 10g, there is a column in V$session called blocking_session to find the session id that is blocking.

Session 1) sid # 145

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
145

SQL> create table t ( x number );

Table created.

SQL> create unique index t_idx on t(x);

Index created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1 );

1 row created.

Session #2) sid – 159

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
159

– hung as 145 is blocking as i haven’t committed as the table t has primary key
SQL> insert into t values ( 1 );

Session 1) to find the blocking_session

– shows both session are active, the second session# 159 is blocked by 145

SQL> select sid, blocking_session, status from V$session where sid IN (159, 145);

SID BLOCKING_SESSION STATUS
———- —————- ——–
145 ACTIVE
159 145 ACTIVE

– another way of find sessions blocked the status of blocked session is VALID
SQL> select sid, blocking_session, seconds_in_wait from v$session where blocking_session_status = 'VALID';

SID BLOCKING_SESSION SECONDS_IN_WAIT
———- —————- —————
159 145 130

Using the following SQL one can find the oracle bit version installed:

SQL> SELECT length(addr)*4 || ‘-bit’ word_length FROM v$process where rownum=1;

WORD_LENGTH
—————
64-bit

Using the SQL one can create a list of tables to pass as a parameter to export (exp).

set heading off
set pages 0

– create the list for a specific user
SELECT DECODE( rownum, 1, ‘tables=(‘, ‘,’ ), owner || ‘.’ || table_name FROM dba_tables WHERE owner IN ( ‘SCOTT’ )
UNION ALL
SELECT ‘)’, null FROM dual;

Output:
tables=( SCOTT.USERS
, SCOTT.DEPT
, SCOTT.EMP
, SCOTT.BONUS
, SCOTT.SALGRADE
, SCOTT.TEST_OBJECTS
, SCOTT.STATS_BACKUP
, SCOTT.DUMMY1
, SCOTT.BIGEMP
, SCOTT.MYDUAL
)

– in the example below it will create list of tables that begin with F but using the example you can create
SELECT DECODE( rownum, 1, ‘tables=(‘, ‘,’ ), owner || ‘.’ || table_name FROM dba_tables WHERE table_name like ‘F%’
UNION ALL
select ‘)’, null from dual
/

If one is keeping archive logs available on disk and also have them archived to tape and would like to delete only the ones that have been archived to tape in the archive directory through RMAN, you can use the following command.

connect target /
connect catalog rmancatalog/rmancatalog@catalog;
# For Tape
run {
DELETE ARCHIVELOG LIKE '%' BACKED UP 1 TIMES TO DEVICE TYPE SBT_TAPE;
}

# For Disk
run {
DELETE ARCHIVELOG LIKE '%' BACKED UP 1 TIMES TO DEVICE TYPE DISK;
}

# Delete logs backed 2 times and more than 1/2 day old
run {
delete noprompt archivelog until time = 'sysdate-0.5' backed up 2 times to sbt ;
}

Using the following rman command one can setup to keep 2 days of archive log on disk and also archive them to tape/disk the ones that haven’t been sent to.

connect target /
connect rmancatalog/rmancatalog@catalog

run {
# tells to backup all archive logs that haven’t been backed up one time which prevents from backing up more than once to
backup archivelog all not backed up 1 times;
# deletes archive log more than 2 days old, as the 2nd command runs after the first one so if some reason first one fails the delete step won’t run
delete archivelog until time ‘SYSDATE-2′;
}

There is another way of doing which is:
# set the following parameter in the rman parameters
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1;
run {
delete archivelog all completed before ‘sysdate-2′;
}

In Query analyzer

– prints job name, step name, start time and end time of each step in the job and sorts by latest on the top
– if jh.sql_message_id is 0 then that step failed but that status is set set at the overall job outcome
SELECT j.Name, jh.Step_name,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/)
AS Start_DateTime,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
and j.name = ‘Job_name’
ORDER BY run_date desc, run_time desc

Sample Output: