How to change logical name of SQL Server database?
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
Set title of a window
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
How to get list of tables in SQLServer?
Using the SQL below one can list SQL Server tables
Use testDB
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
table_name
———–
ExceptionLog
Employee
…
How to view table of contents of a compress tar file?
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
How to find blocking session?
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
How to find oracle bit size?
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
How to create a list of tables for export using SELECT statement?
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
/
How to delete archive logs already archived to backup device?
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 ;
}
How to keep 2 days of archive log on disk and still have back to tape?
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′;
}
How to extract start and end time of a job in SQL Server?
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:
| Job name | Step Name | Start Time | End Time |
| job_name | step_name | 2009-05-13 12:55:01.000 | 2009-05-13 23:26:01.000 |
| job_name | (Job outcome) | 2009-05-13 12:55:01.000 | 2009-05-13 23:26:01.000 |
| … | … | … | … |
