Programmer's Blog

Programmer's reference

Category Archives: Oracle DB

[SQL] delete duplicated rows

DELETE FROM table
WHERE rowid not in
(SELECT MIN(rowid)
FROM table
GROUP BY column1, column2);

[OracleDB] Drop DB in mount mode

SQL> startup mount exclusive restrict;
SQL> drop database;

[Oracle DB] Refresh Materialized View

execute DBMS_MVIEW.REFRESH('MYVIEW');

[OracleDB] maintain archivelog

//Command in sqlplus 
archive log list
archive log stop
archive log start
archive log next
archive log all
archive log n

//Command in rman
//Show all of the RMAN parameters for the database
RMAN> show all;

//Configure the disk channel to the format /backup/ora_df%t_s%s_s%p’
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p';

//Configure the disk channel to the format +FLASHDG
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘+FLASHDG';

//Backup the database using the standard RMAN parameters
RMAN> backup database;

//List all of the current backups for the database
RMAN> list backup;

//List Backup summary
RMAN> list summary;

//Crosscheck backups to see if they still exist
RMAN> crosscheck backup;

//Crosscheck archive logs to see if they still exist
RMAN> crosscheck archivelog all;

//Remove any expired backup
RMAN> delete expired backup;

//Remove any expired archivelos
RMAN> delete expired archivelog;

//Backup the database and archivelogs using the standard RMAN parameters and delete the archivelogs after the backup
RMAN> backup archivelog all delete input;

And

RMAN> run

2> {

3> backup database;

4> backup  archivelog all delete input;

5> }

//Report of files that need backup due to unrecoverable operations
RMAN> report unrecoverable;

//Report the obsolete backups and archivelogs
RMAN> report obsolete;
 
//Delete the obsolete backups and archivelogs
RMAN> delete obsolete;

//List all of the archivelogs
RMAN> list archivelog all;

//Delete a specifed backupset
RMAN> delete backupset 1344

Sample backup command

RMAN> run

2> {

3> backup database;

4> sql “alter system switch logfile”;

5> backup archivelog all delete input;

6> sql “alter database backup controlfile to trace”;

7> }

Backup archive logs from scn 680

RMAN> run

2> {

3> ALLOCATE CHANNEL d1 DEVICE TYPE disk

4> FORMAT = ‘/app/oracle/admin/TEST/backup/df_%d_%s_%p.bak';

5> backup as compressed backupset archivelog from sequence 680;

6> }

//Backup archive logs and delete them up to scn 679

RMAN> run

2> {

3> ALLOCATE CHANNEL d1 DEVICE TYPE disk

4> FORMAT = ‘/app/oracle/admin/TEST/backup/df_%d_%s_%p.bak';

5> backup as compressed backupset archivelog high sequence 679 delete input;

6> }

//Point in time recovery

RMAN> run{

2> set until time “to_date(’05/07/11 09:00:00′ , ‘dd/mm/yy hh24:mi:ss’)”;

3> restore database;

4> recover database;

5> alter database open resetlogs;}

//Delete archive logs up to scn 650
RMAN> delete archivelog high sequence 650;

//list archivelogs from yesterday.
RMAN> list archivelog time between “sysdate -1″ and “sysdate”

//Delete archivelogs backed up 1 time to disk
RMAN> delete archive log all backed up 1 times to disk;

//Delete backups older than 7 days
RMAN> delete backup completed before ‘sysdate -7′;

//Restore archivelog between to scn numbers
RMAN> restore archivelog scn between 547484587 and 547662805;

//Check For Logical And Physical Block Corruption
RMAN> backup check logical validate database;

//RMAN command source : http://oracledbazone.com/2012/04/18/useful-rman-commands/

[OracleDB] set format of output

To enable default formatting of output of queries

$ORACLE_HOME/sqlplus/admin/glogin.def

//add the following lines

set pagesize 0
set linesize 9999
set trimspool on
set tab off
set echo off
set feedback off
set recsep off

[OracleDB] Clean archive logs

$> RMAN target /

RMAN> delete archivelog all;
OR
RMAN> delete archivelog all backed up 1 times to device type disk;
OR
RMAN> delete archivelog until time '2003-03-24:02:38:10' backed up 1 times to device type disk;
OR
RMAN> delete archivelog until time 'sysdate-2' backed up 1 times to device type disk;
OR
RMAN> delete force archivelog until time 'sysdate-2';
OR
RMAN> backup archivelog all not backed up 1 times delete input;

[Oracle] Show number of record in each table

Simply as

SQL> select table_name, num_rows from user_tables;

[Oracle DB] Query plan

The following procedure helps to show the query plan of the SQL

SQL> EXPLAIN PLAN FOR SELECT * FROM TABLE;

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

OR

SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT * FROM TABLE;

to close autotrace
SQL> SET AUTOTRACE OFF;

SAMPLE OUTPUT:
Plan hash value: 1046824173

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 1440 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TABLE | 16 | 1440 | 7 (0)| 00:00:01 |
---------------------------------------------------------------

[Oracle] search for column name

SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE '%search keywords%';

[Oracle] Count number of records for all Tables

SELECT TABLE_NAME,NUM_ROWS FROM USER_TABLES;