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;

Wednesday, January 8, 2014

This query returns the table count,and the row count.

This query returns the table count,and the row count.

select tname,count(*) from tab group by tname;

select table_name,num_rows from user_tables

Thursday, August 15, 2013

How to Move tables from one tablespace to another in oracle



select 'alter table ' || table_name || ' move tablespace your_target_tablespace_name ;' from user_tables;

Wednesday, July 10, 2013

Resizing Temporary Tablespace

 SELECT tablespace_name, file_name, bytes
    FROM dba_temp_files WHERE tablespace_name = 'TEMP';
 
 
ALTER DATABASE TEMPFILE '/u02/oracle/data/temp02.dbf' RESIZE 18M;