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, current and 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

 How to check the CPU right now? Below is running "live" every 2 seconds, for 10 seconds.

>sar -u 2 10

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