What exactly is being audited in the DB?

A very nice site, with a clear explanation:

http://www.acehints.com/2012/12/how-to-check-what-is-getting-being.html

Below mentioned 3 data dictionary views can be used to fetch the details of the auditing.

1. dba_obj_audit_opts: Data dictionary view will give the details of auditing options on all objects.user_obj_audit_opts view will provide the details of the auditing enabled on the objects on the particular user session connected

2. dba_priv_audit_opts: Data dictionary view describes the current system privileges being audited across the database and by the user. The column username can be used to find the details user wise. The column value will be NULL for system-wide auditing

Example:

SQL> select * from DBA_PRIV_AUDIT_OPTS

SQL> /

USER_NAME  PROXY_NAME PRIVILEGE                      SUCCESS    FAILURE

---------- ---------- ------------------------------ ---------- ----------

                      CREATE EXTERNAL JOB            BY ACCESS  BY ACCESS

                      CREATE ANY JOB                 BY ACCESS  BY ACCESS

                      GRANT ANY OBJECT PRIVILEGE     BY ACCESS  BY ACCESS

                      EXEMPT ACCESS POLICY           BY ACCESS  BY ACCESS

3. dba_stmt_audit_opts: Data dictionary view describes the current system auditing options across the database and by the user. The column username can be used to find the details user wise. The column value will be NULL for system-wide auditing

Example

SQL> select * from dba_stmt_audit_opts;

USER_NAME  PROXY_NAME AUDIT_OPTION                             SUCCESS    FAILURE

---------- ---------- ---------------------------------------- ---------- ----------

                      PROFILE                                  BY ACCESS  BY ACCESS

                      ROLE                                     BY ACCESS  BY ACCESS

                      DATABASE LINK                            BY ACCESS  BY ACCESS

                      PUBLIC SYNONYM                           BY ACCESS  BY ACCESS

Monitor the UNDO tablespace extents

Below query will do it:

select status, count(*) Num_Extents, sum(blocks) Num_Blocks, round((sum(bytes)/1024/1024),2) MB from dba_undo_extents 
group by status order by status;

STATUS    NUM_EXTENTS NUM_BLOCKS         MB
--------- ----------- ---------- ----------
ACTIVE          16069   11519120   89993.13
EXPIRED           696       6048      47.25

UNEXPIRED        2286      21408     167.25

How much memory my database is using?

This query is instance wise:


select (sga+pga)/1024/1024 as "sga_pga"
from
(select sum(value) sga from v$sga),
(select sum(pga_alloc_mem) pga from v$process)
/

This query is giving the details per each process:

select vs.program, s.sid, sn.name, round( s.value/1024/1024, 2 ) mb
    from v$statname sn, v$sesstat s, v$session vs
   where sn.statistic# = s.statistic#
     and vs.sid = s.sid
     and sn.name = 'session pga memory'
order by 4
/

Oracle DB: which session is generating the most redo logs right now?

select * from v$sesstat
where STATISTIC# in (select STATISTIC# from v$statname where name like '%redo%')
and value>0
order by value
/


NAME
----------------------------------------------------------------
spare statistic 2

SQL> select name from v$statname where STATISTIC#=194;

NAME
----------------------------------------------------------------
redo size

SQL> select name from v$statname where STATISTIC#=197;

NAME
----------------------------------------------------------------
redo size for direct writes

impd seems to be stuck, a very nice article

Very nice impdp stuck article!!

 https://community.oracle.com/blogs/dearDBA/2015/07/22/impdp-stalls-for-no-apparent-reason

Using "ulimit" to check the processes allocated to a specific UNIX user

Example:

 > ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 579302
max locked memory       (kbytes, -l) 40960000
max memory size         (kbytes, -m) unlimited
open files                      (-n) 524288
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

As a bonus, we can even increase this limit without root, as long as is under the "hard" limit:

Add: ulimit –u 16384  

in the file .bash_profile

To check the "hard limit", run:

>ulimit -aH

How to check if your Oracle CLIENT software version is 32-bit or 64-bit ?

On Linux:
 -----------

cd $ORACLE_HOME/bin

Run the command:

file sqlplus

OR

file sqlldr

OR

file expdp

Oracle: How to check the NLS_CHARCTERSET of the database?

The following query will do it:

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

How to debug "Warning: View created with compilation errors." ?

SQL>alter view MY_VIEW compile;

Warning: View created with compilation errors.

So how do we know what the errors is?

One way is shown below:

SQL> show errors view MY_VIEW

Errors for VIEW MY_VIEW:

LINE/COL ERROR

-------- -----------------------------------------------------------------

0/0      ORA-01031: insufficient privileges

Which query/process is using most of the UNDO tablespace?

Some very nice scripts found at:

http://www.dbaref.com/home/dba-routine-tasks/findingwhatsconsumingthemostundo


SQL> select s.sql_text from v$sql s, v$undostat u
where u.maxqueryid=s.sql_id;

You can also use following SQL to find out most undo used by a session for a currently executing transaction.

SQL> select s.sid,s.username,t.used_urec,t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr
order by t.used_ublk desc;

To find out which session is currently using the most UNDO,

SQL>select s.sid, t.name, s.value
from v$sesstat s, v$statname t
where s.statistic#=t.statistic#
and t.name='undo change vector size'

and s.value > 0
order by s.value;

SQL>select sql.sql_text, t.used_urec records, t.used_ublk blocks,
(t.used_ublk*8192/1024) kb from v$transaction t,
v$session s, v$sql sql
where t.addr=s.taddr
and s.sql_id = sql.sql_id
and s.username ='&USERNAME';

Which objects are loaded in the buffer cache?

A very nice article:

http://www.dba-oracle.com/art_builder_buffer.htm

The script to use is:

/******************************************************************
--   Contents of Data Buffers
******************************************************************/

set pages 999
set lines 92

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
select
   o.owner          owner,
   o.object_name    object_name,
   o.subobject_name subobject_name,
   o.object_type    object_type,
   count(distinct file# || block#)         num_blocks
from
   dba_objects  o,
   v$bh         bh
where
   o.data_object_id  = bh.objd
and
   o.owner not in ('SYS','SYSTEM')
and
   bh.status != 'free'
group by
   o.owner,
   o.object_name,
   o.subobject_name,
   o.object_type
order by
   count(distinct file# || block#) desc
;

column c0 heading "Owner"                                    format a12
column c1 heading "Object|Name"                              format a30
column c2 heading "Object|Type"                              format a8
column c3 heading "Number of|Blocks in|Buffer|Cache"         format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer"    format 999
column c5 heading "Buffer|Pool"                              format a7
column c6 heading "Block|Size"                               format 99,999


select * from
(
select
   t1.owner                                          c0,
   object_name                                       c1,
   case when object_type = 'TABLE PARTITION' then 'TAB PART'
        when object_type = 'INDEX PARTITION' then 'IDX PART'
        else object_type end c2,
   sum(num_blocks)                                     c3,
   buffer_pool                                       c5
from
   t1,
   dba_segments s
where
   s.segment_name = t1.object_name
and
   s.owner = t1.owner
and
   s.segment_type = t1.object_type
and
   nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
group by
   t1.owner,
   object_name,
   object_type,
   buffer_pool
having
   sum(num_blocks) > 10
order by
   sum(num_blocks) desc
)
where rownum < 20
;

Oracle: parallel query historical data

We know the SQL_ID of the query, at which time it ran and we need to know how many parallel processes were dedicated to our query?

The query below will do it:


select sql_id, px_servers_execs_total, px_servers_execs_delta
from dba_hist_sqlstat  
 where sql_id = '85kha1rsqy8wc'
 and snap_id between 81399 and 81400
   ;

SQL_ID        PX_SERVERS_EXECS_TOTAL PX_SERVERS_EXECS_DELTA
------------- ---------------------- ----------------------
85kha1rsqy8wc                    863                      0
85kha1rsqy8wc                    895                     32

Using "sar" to check the CPU utilization, even historical data

For example, to get the CPU for day #6 of the month, only top 30 CPU entries:

>sar -f /var/log/sa/sa06|awk -F" " '{print $4}'|sort -n|tail -30

15.84

19.12

19.64

22.70

23.79

24.06

31.20

32.69

35.55

36.79

37.41

39.59

40.39

43.41

44.62

45.28

46.34

47.20

47.63

60.93

63.28

64.77

64.80

65.31

67.07

67.64

68.44

69.44

69.67

71.79

Linux: how to change the default file name generated by the "gzip" command?

Sometimes you want to be able to control the output file of the gzip command.
This can be done as follows:

1) >gzip -S .suf My_File.dmp
2) >mv My_File.dmp.suf My_File.dmp_2.gz

How to find out if the statistics are locked for a specific table?

The query which might help is:

select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null;