Tuesday, April 15, 2014
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;
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
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
Wednesday, March 19, 2014
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;
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
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;
Subscribe to:
Posts (Atom)
