Some basic commands for DBA's

**************** queries ****************
-----------
The below query lists the records for which the employees have salary greater than 5000 and
the departments which has the number of employees greater than 2.

select * from emp where sal > 5000 group by detpno having count(empno) >2;
-----------

The below query lists the first two employee records filtered using rownum.

select a.* from
(select rownum as rowcnt,empno,ename from emp) a
where rowcnt > 2

-----------

select * from v$session
select * from v$database;
select * from v$archived_log;
select * from v$instance;
select * from v$tablespace;
select * from v$locks;
select * from v$bg;
select * from v$process;
select * from v$datafile;
select * from v$log
select * from v$logfile;
select * from v$controlfile;
select * from v$process
select * from v$system_events

--------- RMAN -----------

Show all;
report schema;
List backup summary;
restore database preview;
List backup;
List incarnation;
List archivelog all completed before 'sysdate-6';
backup archivelog all completed before 'sysdate-1';
backup archivelog all not backedup 1 time;
List backup of datafile 1 ;
restore database except datafile 1;
restore database exclude tablespace 1;
delete archivelog sequence between 1 and 100;
backup datafile 1;

run
{
allocate channel c1 device type disk format='e:\backup\navan.dbf';
backup database;
}

Restore datafile to another location:
----------------------------------------
run
{
allocate channel c1 device type disk;
set newname for datafile 1 to 'e:\backup\system01.dbf';
restore datafile 1;
recover datafile 1;
sql "alter database datafile 1 online";
switch datafile 1;
}

Alter database create datafile 'new datafile path' using 'old datafile path';

************* Parameter related options ***********

> set linesize 100
> col name format a20
> show parameter pfile
> show parameter spfile
> show parameter archivelog
> show parameter controlfile_record_keep_time
> show parameter datafile
> show parameter db_recovery_file_dest
> show parameter db_create_online_log_dest
> show parameter db_create_file_destw
> show parameter logfile
> show parameter undo_retention
> show parameter process
> show parameter listener

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

------------------ Flask back technology --------------

Flash back table to before drop;
select * from emp as of timestamp to_timestamp('14/04/2009 12:22:11 am','dd/mm/yyyy hh:mi:ss am');
select sysdate from dual;
select * from flashback_query
----------------------------------------------------
alter tablespace 1 offline;
alter database datafile 1 move 'new location'
------------------------------------------------------

---------- Linux commands ------------

ps -ef grep oracle
kill -9 processid
top
tail -f filename
[ -- df -g -> only in IBM AIX ]
fdisk -l
mount
df -h
pwd
----
service nfs restart
service network restart
----
[ ext3 -> Linux partition file system ]
**
mount -t /mnt/cdrom /dev/cdrom
cd /dev/cdrom
**
ls -l
ls -a

ls -l -t ->  << list files in last modified order >>.
find *.* -atime -2 exec ls -l {} \; -->  << list files that are accessed 2 times >>
find *.* -atime -2 -exec ls -al {} \;
find . -iname "*.*" -mtime -2 -print