Project

General

Profile

Actions

Useful DBA queries

Check tablespace size


select
ts.tablespace_name,
ts.bytes/1024/1024 megbytes,
ts.maxbytes/1024/1024 max_Megbytes,
(ts.maxbytes - ts.bytes + free.bytes)/1024/1024 free_megbytes
from (
  select tablespace_name,
  sum(bytes) bytes,
  sum(maxbytes) maxbytes
  from dba_data_files
  group by tablespace_name
) ts
left outer join  (
  select tablespace_name,
  sum(bytes) bytes
  from dba_free_space
  group by tablespace_name
) free
on ts.tablespace_name = free.tablespace_name
where ts.maxbytes != 0;

Check tablespace usage by schema

SELECT ROWNUM AS Rank,
       Segments.*
FROM
       ( SELECT  Owner         ,
                Tablespace_Name,
                SUM ( Bytes ) / 1024 / 1024 "Used MB" 
       FROM     DBA_SEGMENTS
       WHERE    Owner NOT IN ( 'SYS', 'SYSTEM', 'SYSAUX' )
         and owner = :schemaname
       GROUP BY Owner,
                Tablespace_Name
       ORDER BY 3 DESC
       ) Segments
WHERE  ROWNUM <= 10;

Create tablespaces and users

create tablespace COOLAPP datafile '/oracle/data/XE/COOLAPP.dbf' size 512m autoextend on;
create user APPUSER identified by somepassword default tablespace COOLAPP temporary tablespace temp; 

Check spatial index is valid


This also exists in the Useful spatial queries section.

select SDO_TUNE.QUALITY_DEGRADATION('SCHEMANAME', 'TABLEA_SPATIAL_INDEX')
from dual;

Results greater then 2 indicate that the spatial index needs to be rebuilt.

alter index TABLEA_SPATIAL_INDEX rebuild;

Gather table statistics for optimizer


begin
  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCHEMANAME',tabname=>'A_TABLE_WITH_LOTS_OF_UPDATES',cascade=> true);
  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCHEMANAME',tabname=>'B_TABLE_WITH_LOTS_OF_UPDATES',cascade=> true);
end;
/

Oracle says table stats are locked

If oracle says it can't gather the table statistics on the tables you're wanting to optimise, you may need to unlock the table for the stats analyser.

First check the last time the tables were analysed.

select * from all_tab_statistics
 where stattype_locked = 'ALL'
  and owner = 'SCHEMANAME';

If it was a long while ago, and a lot of tables are locked, you've probably done an export/re-import on the database while the statistics analyser was running. This can (apparently) cause the tables to stay in a locked state for the gathering of table statistics.

The error I've encountered before is: ORA-20005: object statistics are locked (stattype = ALL)

To fix this you'll need to unlock all the table stats:

SELECT distinct 'exec dbms_stats.unlock_table_stats('''||owner||''', '''||table_name||''');'
FROM all_tab_statistics 
where stattype_locked = 'ALL'
and OWNER in ( 'SCHEMANAME' );

Killing run-away queries


Get the process ID of a run-away session

--Get process ID of v$sessions
select s.sid, s.serial#, s.username, s.sql_id,
       p.spid
  from v$session s, v$process p
 WHERE s.paddr = p.addr;

Generate a list of kill session commands

-- get system kill session commands
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';', s.username, s.sql_id,
       p.spid
  from v$session s, v$process p
 WHERE s.paddr = p.addr;

Make a specific kill session command for a given process ID

-- get system kill session command for process ID
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' as KILLCMD, s.username, p.spid, sq.sql_text
  from v$session s, v$process p, v$sql sq
 WHERE sq.sql_id = s.sql_id
       s.paddr = p.addr
    and p.spid = :PROCESS_ID_HERE;

Check open cursors per session

select a.sid, a.value, b.name
  from v$sesstat a, v$statname b
 where a.statistic# = b.statistic#
       and b.name = 'opened cursors current';

Assorted open cursors queries

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, s.machine
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current' 
group by s.username, s.machine
order by 1 desc;
select      sess.username
,     sess.sid
,     sess.serial#
,     stat.value cursors
from     v$sesstat stat
,     v$statname sn
,     v$session sess
where     sess.username is not null
and     sess.sid = stat.sid
and     stat.statistic# = sn.statistic#
and     sn.name = 'opened cursors current'
order by value
select count(hash_value) cursors
, sid
, user_name
from v$open_cursor
group by 
sid
, user_name
order by 
cursors
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value 
from v$session
where username like :username)
SELECT sql_text
FROM   v$sql
WHERE  sql_id IN (SELECT sql_id
                  FROM   v$open_cursor
                  WHERE  user_name = :username
                         AND ( sid, address, hash_value ) IN (SELECT sid,
                                                                     address,
                                                                     hash_value
                                                              FROM   v$session))

Scheduling a job


A job that calls two procedures and repeats daily at 6.00pm.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'do_something_batch',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN SCHEMA.some_proc(); SCHEMA.some_other_proc(); END;',
   start_date           => '18-JUL-13 6.00.00PM',
   repeat_interval      => 'FREQ=DAILY',
   enabled              =>  TRUE,
   comments             => 'Put some meaningful comment here for the poor person that discovers this one day.');
END;
/

Checking for already scheduled jobs under current user

--- in the schedule
SELECT * FROM USER_SCHEDULER_JOBS;

--- log of jobs run
SELECT job_name, job_class, log_date, operation, status FROM USER_SCHEDULER_JOB_LOG order by log_date desc;

Tracing

Might need to do this.

ALTER SYSTEM SET timed_statistics = true;

Enable tracing for a particular client

EXEC DBMS_MONITOR.CLIENT_ID_STAT_ENABLE('SDE');

Enable tracing for a particular session and serial

select 'EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => ' || sid || ', serial_num => ' || serial# || ', waits => TRUE, binds => TRUE);' othtrace from v$session where username = 'SDE';

Alternate

Not sure of the difference between these two yet.

select 'EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=> ' || sid || ', serial#=> ' || serial# || ', sql_trace=>TRUE);' dotrace
  from v$session where username = 'SDE';

Check blocking objects

http://www.orafaq.com/node/854

select s1.username || '@' || s1.machine
  || ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from v$lock l1, v$session s1, v$lock l2, v$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2 ;
SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;

Recompile invalid objects

DECLARE
    str varchar2(100);
BEGIN
   FOR obj IN (SELECT *
                 FROM dba_objects
                WHERE status != 'VALID') 
   LOOP
     if obj.OBJECT_TYPE = 'PACKAGE BODY' then 
      str := 'alter '|| obj.object_type ||' "'|| obj.owner||'"."'||obj.object_name ||'" compile body'; 
  else 
      str := 'alter '|| obj.object_type ||' "'|| obj.owner||'"."'||obj.object_name ||'" compile';
  end if; 
      begin
        EXECUTE IMMEDIATE str;
      exception
        when others then null;
      end; 
   END LOOP;
END;

Invalid java class objects

If you can't resolve JAVA CLASS objects no matter what you try and do, you can reload the class files/jars using particular options from command line to get around the cyclic dependencies. Try this:

loadjava -verbose -user testuser/testpass -resolve -force -genmissing bcprov-jdk14-140-modified.jar
loadjava -verbose -user testuser/testpass -resolve -force -genmissing iText-2.1.3.jar

Enable archivelog mode


[oracle@somewhere ~]$ ORACLE_SID=SOME_DB sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     61
Current log sequence           63

SQL> show parameter recovery_file_dest

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest             string     /u01/fast_recovery_area
db_recovery_file_dest_size         big integer 4122M

SQL> alter system set log_archive_dest_1='LOCATION=/u01/archivelog/SOME_DB' scope=both;

System altered.

SQL> archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           /u01/archivelog/SOME_DB
Oldest online log sequence     61
Current log sequence           63

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size            2227904 bytes
Variable Size          788529472 bytes
Database Buffers      436207616 bytes
Redo Buffers            8994816 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/archivelog/SOME_DB
Oldest online log sequence     61
Next log sequence to archive   63
Current log sequence           63

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size            2227904 bytes
Variable Size          788529472 bytes
Database Buffers      436207616 bytes
Redo Buffers            8994816 bytes
Database mounted.
Database opened.

Switch to NOARCHIVELOG mode

select log_mode from v$database;

If 'ARCHIVELOG':

[oracle@somewhere ~]$ ORACLE_SID=SOME_DB sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size            2235208 bytes
Variable Size         1010828472 bytes
Database Buffers       50331648 bytes
Redo Buffers            5541888 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.

SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size            2235208 bytes
Variable Size         1010828472 bytes
Database Buffers       50331648 bytes
Redo Buffers            5541888 bytes
Database mounted.
Database opened.

SQL> quit;

Now clean up expired archivelogs:

[oracle@somewhere ~]$ ORACLE_SID=SOME_DB rman target=/

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 20 15:19:29 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SOME_DB (DBID=1234567890)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=311 device type=DISK
... ...
... a big list ...
... ...

RMAN> delete archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=311 device type=DISK

List of Archived Log Copies for database with db_unique_name SOME_DB
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
... ...
... a big list ...
... ...

Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/some/path/to/archivelogs/1_1665_123456789.dbf RECID=3330 STAMP=874104844
... ...
... a big list ...
... ...
Deleted 1905 objects

RMAN-06207: WARNING: 1904 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
... ... 
... another big list ...
... ...

RMAN> crosscheck archivelog all;
... and again ...

RMAN> delete expired archivelog all;
... and again ...
Do you really want to delete the above objects (enter YES or NO)? YES
... again ...
Deleted 1904 EXPIRED objects

Cool heirarchical query

SELECT child_name, parent_name, parent_desc, the_path
  FROM (WITH table_hierarchy (child_id, parent_id, path) AS
        (
          SELECT id child_id, id parent_id, name path
            FROM base_table c
           WHERE NOT EXISTS
                   (SELECT 1
                      FROM base_table_self_join
                     WHERE parent_id = c.id)
          UNION ALL
          SELECT h.child_id, p.id parent_id, name || ' -> ' || path
            FROM table_hierarchy h
            JOIN base_table_self_join pp
              ON pp.child_id = h.parent_id
            JOIN base_table p
              ON p.id = pp.parent_id
        )
        SELECT c.name child_name, c.type child_type, c.description child_desc,
               p.name parent_name, p.type parent_type, p.description parent_desc,
               h.path the_path
          FROM base_table c
          JOIN table_hierarchy h
            ON h.child_id = c.id
         JOIN base_table p
            ON p.id = h.parent_id
         WHERE NOT EXISTS
                 (SELECT 1
                    FROM base_table_self_join
                   WHERE parent_id = c.id)
           AND p.id IN (SELECT parent_id
                          FROM base_table_self_join
                         START WITH child_id = c.id
                       CONNECT BY PRIOR parent_id = child_id))
 WHERE child_name LIKE 'some-leaf-node%'
 ORDER BY child_name, parent_name;

Updated by Kamikaze over 2 years ago · 26 revisions