How to get a list of the tables whose statistics are locked?

The below query will do it:

SELECT TABLE_NAME FROM DBA_TAB_STATISTICS WHERE STATTYPE_LOCKED = 'ALL';

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