Managing Tablespaces

Create Tablespace 

 CREATE TABLESPACE CAR_DATA_01 DATAFILE '/oradata/CAR_DATA_01_01.dbf' SIZE 10M AUTOEXTEND ON MAXSIZE 20G;

--ASM--
CREATE TABLESPACE CAR_DATA_01 DATAFILE '+DATA' SIZE 1M AUTOEXTEND ON NEXT 500K MAXSIZE 10G;

Add Datafile

 ALTER TABLESPACE CMDC_SMALL_DATA01 ADD DATAFILE '/u02/app/oracle/oradata/CMDCPREP/CMDC_SMALL_DATA01_4.dbf' SIZE 1M AUTOEXTEND ON NEXT 500K MAXSIZE 30G;

--ASM--
 ALTER TABLESPACE CAR_DATA_DATA01  ADD DATAFILE '+DATADG' SIZE 1M AUTOEXTEND ON NEXT 500K MAXSIZE 10G ;


Increase MAXSIZE

--Find datafiles--
SELECT FILE_NAME,BYTES/1024/1024/1024,MAXBYTES/1024/1024/1024 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'CAR_DATA_DATA01';

ALTER DATABASE DATAFILE '/u02/app/oracle/oradata/CAR_DATA_DATA01.dbf' AUTOEXTEND ON MAXSIZE 21G;


Move Tablespace/Datafile

ALTER TABLESPACE CAR_DATA_DATA OFFLINE;

cp CAR_DATA_DATA.DBF /NEWPATH/ORADATA/

ALTER DATABASE RENAME FILE '/oldpath/CAR_DATA_DATA.dbf'
TO '/newpath/oradata/CAR_DATA_DATA.dbf';

ALTER TABLESPACE CAR_DATA_DATA ONLINE;

**You can apply same scenario for move or rename datafile.

How to Identify Deadlock Object

You can get trace file from the alert.log

Example trace file 

Deadlock graph:
                                                       ---------Blocker(s)--------           ---------Waiter(s)---------
Resource Name                        process  session   holds waits  process session   holds waits
TX-00450014-00012495                 78    1213     X                       216         92      X
TX-0044000b-00013342               216        92     X                         78     1213       X


Rows waited on:
  Session 1213: obj - rowid = 0005DD13 - AABd0TAAXAAARF5AAE
  (dictionary objn - 384275, file - 23, block - 70009, slot - 4)
  Session 92: obj - rowid = 0005DD5F - AABd1fAAJAAAMlRABP
  (dictionary objn - 384351, file - 9, block - 51537, slot - 79)



SELECT owner, object_name, object_type 
FROM dba_objects 
WHERE object_id = 384275;

SELECT * 
FROM owner.object name  <---- from above query
WHERE rowid = 'AABd0TAAXAAARF5AAE';

SQL to find reference constraint to specific table

SELECT TABLE_NAME, CONSTRAINT_NAME, STATUS, OWNER
FROM ALL_CONSTRAINTS
WHERE R_OWNER = 'SCOTT'
AND CONSTRAINT_TYPE = 'R'
AND R_CONSTRAINT_NAME IN
(
SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P', 'U')
AND TABLE_NAME = 'SCOTT'
AND OWNER = 'EMPLOYEE'
)
ORDER BY TABLE_NAME, CONSTRAINT_NAME

EXPDP Command in Action

Create dump directory and give grants

 CREATE OR REPLACE DIRECTORY DUMP_DIRAS '/u02/live_dump'
 GRANT READ, WRITE ON DIRECTORY importme TO dba_user;


Export Full Schema

expdp directory=DUMP_DIRE  dumpfile=scott_test.dmp logfile=scott_test.log schemas=scott flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\"


Export Tables

expdp directory=DUMP_DIRE  dumpfile=scott_test_table.dmp logfile=scott_test_table.log tables=scott.enployees flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\"


Export Schema without specific tables

expdp directory=DUMP_DIRE  dumpfile=scott_test.dmp logfile=scott_test.log schemas=scott flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\" EXCLUDE=TABLE:\"LIKE \'AUDIT%\'\"

Get dump file size Estimate

expdp directory=DUMP_DIRE  schemas=scott flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\" ESTIMATE_ONLY=Y


Export data with where condition 

create file as backup.par with following details

DIRECTORY= DUMP_DIRE
DUMPFILE= scott_table.dmp
LOGFILE= scott_table.log
TABLES=scott_employee
QUERY="where JOB_TYPE = 'ACC' and SALARY >= 10000"
EXCLUDE=statistics

then execute expdp command

expdp parfile=backup.par

Gather Database Statistics

Database Stat Gather
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

Schema stat gather
EXEC DBMS_STATS.gather_schema_stats('TEST');
EXEC DBMS_STATS.gather_schema_stats('TEST', estimate_percent => 15);

Table Stat gather
EXEC DBMS_STATS.gather_table_stats('TEST', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('TEST', 'EMPLOYEES', estimate_percent => 15);

Index Stat gather
EXEC DBMS_STATS.gather_index_stats('TEST', 'EMPLOYEES_IDX_01');
EXEC DBMS_STATS.gather_index_stats('TEST', 'EMPLOYEES_IDX_01', estimate_percent => 15);

Delete Duplicate rows from table

From this sql you can delete duplicate rows which can identified from specific columns

DELETE
FROM table_x
WHERE rowid IN
  (SELECT MAX(rowid)
  FROM table_x
  WHERE (column_a, column_b, column_c) IN
    (SELECT column_a, column_b, column_c
    FROM
      (SELECT column_a, column_b, column_c,
        COUNT(*)
      FROM table_x
      GROUP BY column_a, column_b, column_c
      HAVING COUNT(*) > 1
      )
    )
  GROUP BY column_a, column_b, column_c

  );

How to get high load sqls which are currently running

How to get high load sqls which are currently running 

SELECT a.sid,d.spid ,  a.username, a.machine, c.consistent_gets, c.block_gets,

       c.physical_reads, round((100 * (c.consistent_gets + c.block_gets -

       c.physical_reads)) / (c.consistent_gets + c.block_gets), 2) hitratio,

       b.sql_fulltext 

    FROM v$session a, v$sqlarea b, v$sess_io c , v$process d

    WHERE a.sql_hash_value = b.hash_value

      AND a.sid = c.sid

      AND c.consistent_gets + c.block_gets > 0

      AND a.username IS NOT NULL

      AND a.status = 'ACTIVE'

      and a.paddr      = d.addr

    ORDER BY a.status ASC, c.consistent_gets DESC, c.physical_reads DESC