Oracle DBA Samples

•@ vs @@
@ find file in current directory
@@ find file in same path as the command file from which it was called

•object priv granted to PUBLIC
select * from dba_tab_privs
where grantee = ‘PUBLIC’;
•system priv granted to PUBLIC
select * from dba_sys_privs
where grantee = ‘PUBLIC’;
•roles granted to public
select * from dba_role_privs
where grantee = ‘PUBLIC’;

Move db files

•backup db
•alter tablespace accounting offline normal | drop;
•mv /disk1/accounting01.dbf /disk2/accounting01.dbf
•alter tablespace accounting rename datafile ‘/disk1/accounting01.dbf’ to ‘/disk2/accounting01.dbf’;
•alter tablespace accounting online ;

•OR: shutdown; startup mount
•mv /disk1/accounting01.dbf /disk2/accounting01.dbf
•alter database account rename file ‘/disk1/accounting01.dbf’ to ‘/disk2/accounting01.dbf’;
•alter database open ;

§Add datafile
alter tablespace “LOB1”
add datafile ‘/disk1/file2.dbf’
size 500M
reuse;

§Recover datafile
alter datafile
recover datafile ‘/disk1/ fils2.dbf’;

§Resize datafile
alter database
datafile ‘/disk1/file2.dbf’
resize 5000K;

Recover from Missing RBS Datafile

•add in pfile to indicate corrupted rbs segmente
_corrupted_rollback_segments=RBS0,RBS1,RBS2…

•shutdown abort;
•alter database
datafile ‘‘ offline drop;
•alter database open;

•create a temp rbs tblspc
•create at least one online rbs seg

•drop all original rbs segments
•drop original rbs tblspc

•recreate original rbs tblspc
•recreate all original rbs segments

•take temp rbs segments offline
•drop temp rbs tblspc

•remove from pfile
_corrupted_rollback_segments=RBS0,RBS1,RBS2…
•restart database

Reduce RBS datafiles

•Create temp rbs tblspc
•Create temp rbs segs in it
•Drop tblspc
•Delete datafile
•Recreate rbs tblspc & rbs segs

Retrieve store procedure text
select obj.object_name, src.text
from user_objects obj,
user_source src
where obj.object_name=src.name
and object_type=’PROCEDURE’
/

•Outer Joins
mysql>select *
from clients c left join invoices i
on c.id=i.id;
Or
mysql>select *
from clients c left join invoices i
using (client_id);

SQL*Plus

•SET AUTOCOMMIT 100
•SET SCAN OFF
•set pagesize 80
•break on name skip page on value

Oracle Table Definition
select table_name, column_name,
data_type, data_length, nullable
from user_tab_columns
order by table name
/

Constraint Definition
select cons.table_name, cons_constraint_nams, cons.constraint_type, col.column_name
from user_constraints cons, user_cons_columns col
where cons.table_name = col.table_name
and cons.constraint_nams = col.constraint_name
order by cons.table_nams, cons.constraint_name

@?/rdbms/admin/UTLXPLAN.SQL

EXPLAIN PLAN FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';
  

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TIM' FOR
  2  SELECT *
  3  FROM   emp e, dept d
  4  WHERE  e.deptno = d.deptno
  5  AND    e.ename  = 'SMITH';

Explained.

SQL> @explain.sql TIM

PLAN                                   OBJECT_NAME     OBJECT_TYPE     BYTES  COST PARTITION_START PARTITION_STOP
-------------------------------------- --------------- --------------- ----- ----- --------------- ---------------
Select Statement                                                          57     4
  1.1 Nested Loops                                                        57     4
    2.1 Table Access (Full)            EMP             TABLE              37     3
    2.2 Table Access (By Index Rowid)  DEPT            TABLE              20     1
      3.1 Index (Unique Scan)          PK_DEPT         INDEX (UNIQUE)            0

5 rows selected.

SQL>

-- SQL*Plus
Set autotrace on

DROP TABLESPACE tbs_01 
    INCLUDING CONTENTS 
        CASCADE CONSTRAINTS; 
 
# DROP tablesapce mytbs01 AND DROP DATA files
DROP tablespace mytbs01 including contents AND datafiles;