Hi All,
Here's a placeholder post for keeping all of my routine work commands.I hope this helps others as well. I have tried to give due to credit to original owner wherever possible. However, incase I have missed out, please do let me know...I ll update this post.
--------------------
tablespace size
col Total_MB for 999999999
col Free_MB for 999999999
break on Total_MB on report
compute sum of Total_MB on report
compute sum of Free_MB on report
select a.TABLESPACE_NAME,
a.MB Total_MB,
b.MB Free_MB,
round(((a.MB-b.MB)/a.MB)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES)/1048576 MB
from dba_data_files
group by TABLESPACE_NAME
)
a ,
(
select TABLESPACE_NAME,
sum(BYTES)/1048576 MB
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.MB-b.MB)/a.MB) desc;
and a.TABLESPACE_NAME =upper('&tbs_name');
100% full tablespace
select a.TABLESPACE_NAME,
a.MB Total_MB,
b.MB Free_MB from
(
select TABLESPACE_NAME,
sum(BYTES)/1048576 MB
from dba_data_files
group by TABLESPACE_NAME
)
a ,
(
select TABLESPACE_NAME,
sum(BYTES)/1048576 MB
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and b.mb=0;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Size of datafile with used/free space
set linesize 1000
set pagesize 5000
column a1 heading 'Tablespace' format a20
column a2 heading 'Data File' format a80
column a3 heading 'Total|Space [MB]' format 9999999.99
column a4 heading 'Free|Space [MB]' format 99999.99
column a5 heading 'Free|%' format 9999.99
break on a1 on report
compute sum of a3 on a1
compute sum of a4 on a1
compute sum of a3 on report
compute sum of a4 on report
SELECT a.tablespace_name a1,
a.file_name a2,
a.avail a3,
NVL(b.free,0) a4,
NVL(ROUND(((free/avail)*100),2),0) a5
FROM (SELECT tablespace_name,
SUBSTR(file_name,1,80) file_name,
file_id,
ROUND(SUM(bytes/(1024*1024)),3) avail
FROM sys.dba_data_files
GROUP BY tablespace_name,
SUBSTR(file_name,1,80),SUBSTR(file_name,1,80),
file_id) a,
(SELECT tablespace_name,
file_id,
ROUND(SUM(bytes/(1024*1024)),3) free
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id) b
WHERE a.file_id = b.file_id and a.tablespace_name = '&tbs_name'
ORDER BY 1, 2
/
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Size of directory sorted by size
du -sk * | sort -nr | head -10
processors
Linux: cat /proc/cpuinfo
Sun psrinfo
Top processes
Linux : top
Solaris : prstat
Memory info
Linux : cat /proc/meminfo
Sun : prtdiag
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Zip multiple files and folders
zip -r new_filename.zip folder_name(to be zipped)
Unzip and overite
zip -o file.zip
See contents of zip file
unzip -l filename.zip
ZIP Password
zip -P secretpassword zipfilename.zip files_*
search & replace string
:1,$s/old_string/new_string
:%s/old_string/new_string/g
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SFTP
cd into destination path
sftp box-admin
it ll prompt for password
at sftp prompt---cd in to the source path
get filename
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Mailx
cat AM58WT90.LOG|mailx -s "AM58WT90.LOG" yogesh.tiwari@xyz.com
uuencode UsWorkFlow.zip UsWorkFlow.zip | mailx -s "Sit Data" akshay.choudhary@xyz.com (need to mention attachment name twice)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sar command usage
For everything of sar
sar -A
for load average
sar -q
for io tps
sar -b
for swapping
sar -B
for cpu utilization
sar -u
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- To list file with line number
nl <filename>
-- Saves man pages to text
man cat | col -b > less.txt
-- Mounting as a loop device
mount -o loop -t iso9660 /FC2-i386-DVD.iso /iso
-- To split a large file into multiple files
split --bytes 1m bigfile 1_
-- List of open files opened by raj under /dev/hda1
lsof -u raj -a /dev/hda1
-- Find who owns the port number
fuser -v -n tcp 992345
-- List of users accessing the /mnt file, -k option to kill all using the FS
fuser -v /mnt/*
-- File/File System status
stat afile
-- Trace System calls
strace -aef cat afile
-- Run Top in batch file
top -b -d 5 -n 3 >> top.out
b=batch, d=delay in seconds , n=number of iterations
-- To read headers of binary files (ELF)
readelf --file-header /bin/ls
-- Provides file system statistics
stat <filename>
-- List all network services running
netstat -tanup
-- List listening interfaces
netstat -l
-- Shread and remove a file
shred -n 50 -zuv afile
n=shred the file "n" times, -z will zero out the file in the last pass (extra protection), u=truncate it every time, v=verbose.
-- Create a function Calculator - calc 3+45/10
function calc { echo "{$1}" | bc -l ; }
-- Command line calculator
echo "10/2*10" | bc -l
-- Search man pages for string
man -k string
-- Merge pdf files into one
gs -dNOPAUSE -sDEVICE=pdfwrite -sOUTPUTFILE=Merged.pdf -dBATCH img*.pdf
-- Create pdf file from man page
man -t cat | ps2pdf - > cat.pdf
-- Colors the string while grepping
grep --color string filename
-- Copy files between Servers using netcat (Alternate option to consider if ftp is disabled )
On Server (in my case host graphics) run
tar -cvjf - dummy.dat | netcat -l -p 3333
On the Client ( in my case it was silicon) run
netcat graphics 3333 | pv -b | tar -xjvf -On the server listening port 3333 is created and dummy.dat is tar'd. When netcat is ran with no default options, it basically connects to the server/port specified.
-- Rsync files between servers
silicon~> rsync -avrz . --include "init*.ora" --rsh='ssh' graphics:/usr/oracle/admin
Above syncs all init.ora files from silicon to graphics a=archive,v=verbose,r=recursive,z=compress
-- List all network services running on the system
netstat -tanup
-- Test FTP Speed. Use dd to create a large file, but instead of sending it to the disk send it to null device. Here
/dev/zero is the input and output of the dd is /dev/null.
put "|dd if=/dev/zero bs=1024 count=1000000" /dev/null
-- Find all files with group owner is "oracle" and change it to "dba" group.
find . -group oracle -exec chgrp dba '{}' \ ;
--Show files greater than a specific size
find ./ -size +100M -type f -print0 | xargs -0 ls -Ssh1 --color
-- Send e-mail attachments with mutt
mutt -a file.txt -s "A Text File" raj.anju@xyz.com < /tmp/junk
-- Kill multiple processes in one shot
ps -ef|grep dbconsole | awk ' { print $2} ' | xargs kill -9
-- Copy directory & files under remotely.
scp -r RMANREPO/ oracle@server:/usr/oracle/admin/RMANREPO/
-- Improve ftp speed with jumbo frames.
Note: Read more on the Jumbo frames, as its applicable only on certain situations. On a 10/100 data the max mtu can only be upto 1500, but for a GigaBit Ethernet connection it can go upto 9000. The best throughput I got while doing an XO (Cross-Over) connection between two systems is when the mtu was 6000.
ifconfig eth0 mtu 6000
For a permanent change you have to edit the below file to include MTU 6000
/etc/sysconfig/network-script/ifcfg-eth0
Check indexes that are in index tablespace or not
Script creation for rebuild---------select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE tablespace USER_INDEX;' from dba_indexes where owner='
PN_USER_OWN1' and tablespace_name!='USER_INDEX';
Select index_name, owner, tablespace_name from dba_indexes where tablespace_name not like '%IND%'
and owner not in ('SYS','SYSTEM','DBSNMP','OUTLN','TSMSYS','WMSYS') order by owner;
SQL> select index_name, owner, tablespace_name from dba_indexes where owner='PN_DEPOSIT_OWN1';
select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE tablespace USER_INDEX;' from dba_indexes where owner='PN_USER_OWN1' and tablespace_name!='USER_INDEX';
Rebuild IOT index(primary key)
alter table schema.table_name move online OVERFLOW TABLESPACE tablespace_name;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Check if Index is Usable---ALWAYS CHECK THIS BEFORE REBUILD
select INDEX_NAME,INDEX_TYPE,STATUS, OWNER from dba_indexes where status='UNUSABLE';
Select * from v$option where parameter ='Online Index Rebuild';
select index_owner, index_name, partition_name, tablespace_name, status
from dba_ind_partitions
where index_owner not in ('SYS','SYSTEM','SYSMAN') and status='UNUSABLE'
order by index_owner, partition_name;
select INDEX_NAME,INDEX_TYPE,STATUS, owner from dba_indexes where status='UNUSABLE';
alter table tablename modify partition partition_name rebuild unusable local indexes;
select partition_name, status, index_owner
from dba_ind_partitions
where index_owner not in ('SYS','SYSTEM','SYSMAN')
order by index_owner, partition_name
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
index rebuild online
select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE ;' from dba_indexes where owner not in ('SYS','SYSTEM','DBSNMP','OUTLN','TSMSYS');
select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE ;' from dba_indexes where owner in ('SYS','SYSTEM');
alter table tablename modify partition partition_name rebuild unusable local indexes;
ALTER INDEX <index_name> REBUILD PARTITION <partition_name> TABLESPACE <new_tablespace_name>;
select 'alter table schema.table modify partition '|| partition_name||' rebuild unusable local indexes;' from dba_ind_partitions where index_owner not in ('SYS','SYSTEM','SYSMAN') and tablespace_name!='abc_DATA';
Rebuild primary key index
alter table "schema"."VALUE" drop constraint "SYS_C0069949";
or
alter table "schema"."VALUE" drop primary key;
alter table "schema."VALUE" add constraint primary key (ID);
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
add datafile
select file_name,tablespace_name,bytes/1024/1024 as bytes, MAXBYTES/1024/1024 as maxsize from dba_temp_files where tablespace_name like '%TEMP%';
select property_name, property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
select file_name,tablespace_name,bytes/1024/1024 as bytes, MAXBYTES/1024/1024 as maxsize from dba_data_files where tablespace_name ='tbs_name';
alter database datafile '/xyz/pkgs/oracle/oradata/PNO1/PNO1.pn_account_index_06.dbf' resize 1500m;
alter database add datafile 'path' autoextend on next 10m maxsize 4000m reuse;
create tablespace <name> datafile 'path' size 4000m reuse;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Stats Gather
EXEC DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>UPPER('$schemaname'), ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, DEGREE=>DBMS_STATS.AUTO_DEGREE, CASCADE=>TRUE);
exec dbms_stats.gather_table_stats( ownname => '&owner', tabname => '&table_name', estimate_percent => 100, cascade => TRUE,NO_INVALIDATE => FALSE);
EXEC DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>UPPER('$schemaname'), ESTIMATE_PERCENT=>20, DEGREE=>4, CASCADE=>TRUE);
select 'EXEC DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME=>(' || ''''||username||'''' ||'), ESTIMATE_PERCENT=>20, DEGREE=>4, CASCADE=>TRUE);' from dba_users where username not in ('SYS','SYSTEM','DBSNMP','OUTLN','TSMSYS');
PROMPT Gathering System Stats
exec dbms_stats.gather_schema_stats('SYS');
PROMPT Gathering Dictionary Stats
exec dbms_stats.gather_dictionary_stats;
PROMPT Gathering all fixed objects stats (dynamic performance tables)
exec dbms_stats.gather_fixed_objects_stats;
REM PROMPT Gathering Database Stats
REM exec dbms_stats.gather_database_stats(options => 'GATHER AUTO');
----SYSAUX is too big----
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
---Check date when huge stats was generated.
select trunc(SAVTIME),count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY group by trunc(SAVTIME) order by 1;
---clear the oldest and heaviest stat first
exec dbms_stats.purge_stats(to_date('01-JAN-2010','DD-MON-YYYY'));
11g
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
select CLIENT_NAME,status from DBA_AUTOTASK_CLIENT;
Stats Job status & completion
10g
column job_name format a20
column status format a12
column actual_start_date format a36
column run_duration format a14
select
job_name, status, actual_start_date, run_duration
from
dba_scheduler_job_run_details
where
job_name ='GATHER_STATS_JOB'
order by
actual_start_date;
11g
set lines 100
col operation form a40 wrap head 'operation(on)'
col target form a1
select operation||decode(target,null,null,'-'||target) operation
,to_char(start_time,'YYMMDD HH24:MI:SS.FF4') start_time
,to_char( end_time,'YYMMDD HH24:MI:SS.FF4') end_time
from dba_optstat_operations
order by start_time desc
/
Restore stats
-- Catalog view DBA_OPTSTAT_OPERATIONS contain history of
-- statistics operations performed at schema and database level
-- using DBMS_STATS.
--
-- The views *_TAB_STATS_HISTORY views (ALL, DBA, or USER) contain
-- history of table statistics modifications.
EXEC DBMS_STATS.RESTORE_SCHEMA_STATS(OwnName => 'LHC',AS_OF_TIMESTAMP => '17-JUL-10 11.11.00.862694000 PM +02:00');
exec dbms_stats.restore_table_stats(OwnName =>'AMKAR1', tabname =>'T_PROFIT_CENTER',AS_OF_TIMESTAMP =>'29-DEC-10 09.35.00.116003 AM +2:00');
EXEC DBMS_STATS.restore_table_stats('LHC','TAB....name','17-JUL-10 11.11.00.862694000 PM +02:00');
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Check for Deadlock
SELECT holding_session bsession_id, waiting_session wsession_id, b.username busername, a.username wusername, c.lock_type type, mode_held, mode_requested, lock_id1, lock_id2 FROM sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE c.holding_session=b.sid and c.waiting_session=a.sid;
What all sessions are accessing objects