Wednesday, April 23, 2014

Linux Command

File Commands

ls                     -list directory
ls -al                   -formatted listing with hidden files
ls -ltr                  -formatted listing ordered by time
cd dir                   -change to directory
cd -                     -go to directory in previously
cd                       -go to home directory
pwd                      -show current directory path
mkdir dir                -make directory dir
rm file                  -delete file
rm -r dir                -delete directory dir
rm -f file               -force remove file
rm -rf dir               -force remove directory dir
cp file1 file2           -copy file1 to file2
cp -r dir1 dir2          -copy dir1 to dir2; create dir2 if it doesn’t exist
mv file1 file2           -rename/move file1 to file2 if file2 is an existing dir, moves file1
ln -s file link          -create symbolic link link to file
touch file               -create or update file
cat > file               -places standard input into file
more file                -output the contents of file
head file                -output the first 10 lines of file
tail file                -output the last 10 lines of file
tail -f file             -output the contents of file as it grows, starting with the last 10


ssh user@host            -connect to host as user
ssh -p port user@host    -connect to host on port port as user
ssh-copy-id user@host    -add your key to host for user to enable a keyed or passwordless login



ping host                 – ping host and output results
whois domain              – get whois information for domain
dig domain                – get DNS information for domain
dig -x host               – reverse lookup host
wget file                 – download file
wget -c file              – continue a stopped download

 Save File in Vi / Vim Text Editor

Command    Description
q    :Quit
q!    :Quit without saving changes i.e. discard changes
r      : :fileName    Read data from file called fileName
wq     :Write and quit (save and exit)
w    :fileName    Write to file called fileName (save as)
w!   :fileName    Overwrite to file called fileName (save as forcefully)


Monday, April 21, 2014

Table Fragmentation in oracle

select owner,table_name,round((blocks*8),2)||'kb' "Fragmented size", round((num_rows*avg_row_len/1024),2)||'kb' "Actual size", round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||'kb',
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from dba_tables where table_name =’ table_Name’ AND OWNER LIKE 'schema_name';
 
 
PAYSOUTH_REG    PAY_EARNINGS    18944kb    15808.48kb    3135.52kb    6.55152027027027027027027027027027027027
 
 
Gather table states:
 
exec dbms_stats.gather_table_stats('schema_name','table_name');
 
 
Rebuild all indexes:
 
select index_name from user_indexes where table_name like 'table_name';
  

Thursday, April 10, 2014

RMAN Command in Oracle




RMAN> LIST Backup of Controlfile;
RMAN> LIST Backup of Spfile;
RMAN> LIST Backup of Tablespace Test;
RMAN> LIST expired Backup;
RMAN> LIST expired Backup summary;
RMAN> LIST expired Backup of Archivelog all;
RMAN> LIST expired Backup of datafile 10;
RMAN> LIST recoverable backup;
RMAN> LIST ARCHIVELOG ALL;
RMAN> LIST BACKUP OF ARCHIVELOG ALL;
RMAN> LIST BACKUP;
RMAN> LIST BACKUP OF DATABASE;
RMAN> LIST BACKUP OF DATAFILE 1;
RMAN> LIST BACKUP SUMMARY;
RMAN> LIST INCARNATION;
RMAN> LIST BACKUP BY FILE;
RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN> LIST COPY OF DATAFILE ;
RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;
RMAN> LIST expired Backup of archivelog all summary;
RMAN> LIST Backup of tablespace Test summary;
RMAN> LIST BACKUPSET OF DATAFILE 1;
RMAN> LIST FAILURE;

Wednesday, March 26, 2014

Dba Scripts

FINDING BIG TABLES BY BLOCKS-- in this example top 5

select t.owner, t.table_name, t.occupied_blocks
from
(select owner, table_name, blocks-empty_blocks occupied_blocks
from dba_tables
order by 3 desc nulls last) t
where rownum <=5

**************************************

owner    table_name,           t.occupied_blocks
SH          SALES                 918843
SYS       SOURCE$            135041
SH       COSTS                    82112
SYS      ARGUMENT$       58244
SYS       COL$                     56957


IDLE SESSIONS FOR MORE THAN  HOUR


select sid,serial#,username,trunc
(last_call_et/3600,2)||' hr'
last_call_et
from V$session where
last_call_et > 3600 and username is not null
 

Monday, February 24, 2014

Select a random sample of results from an oracle query

select *
   from ( select SCHEDULE_NO, DEPOT
               from SCHEDULE_MASTER
                     where SCHEDULE_NO like '%'
                      order by dbms_random.value )
     where rownum <= 5


SCHEDULE_NO         DEPOT
89D/1                           27
304L/1                          18
333E/1                           22
317G/2                          18
BIAL 9/8                       10


SCHEDULE_NO                   DEPOT
43J/1                                      24
256M/1                                   27
346N/1                                   16
J12/1                                      32
V 500A/2                                 11

For mysql query

select  * from t_employee_details ORDER BY RAND() LIMIT 10;