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
;