Find Tablespace Freespace in ORACLE 11g

Below query is useful to displays a list of tablespaces and their used/full status.

COL tspace form a25 Heading "Tablespace"
COL tot_ts_size form 99999999999999 Heading "Size (Mb)"
COL free_ts_size form 99999999999999 Heading "Free (Mb)"
COL ts_pct form 9999 Heading "% Free"
COL ts_pct1 form 9999 Heading "% Used"
BREAK on report
COMPUTE sum of free_ts_size on report
COMPUTE sum of tot_ts_size on report
SELECT                                                            /* + RULE */
         df.tablespace_name tspace, df.BYTES / (1024 * 1024) tot_ts_size,
         SUM (fs.BYTES) / (1024 * 1024) free_ts_size,
         NVL (ROUND (SUM (fs.BYTES) * 100 / df.BYTES), 1) ts_pct,
         ROUND ((df.BYTES - SUM (fs.BYTES)) * 100 / df.BYTES) ts_pct1
    FROM dba_free_space fs,
         (SELECT   tablespace_name, SUM (BYTES) BYTES
              FROM dba_data_files
          GROUP BY tablespace_name) df
   WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, df.BYTES
UNION ALL
SELECT                                                            /* + RULE */
         df.tablespace_name tspace, fs.BYTES / (1024 * 1024) tot_ts_size,
         SUM (df.bytes_free) / (1024 * 1024) free_ts_size,
         NVL (ROUND ((SUM (fs.BYTES) - df.bytes_used) * 100 / fs.BYTES),
              1
             ) ts_pct,
         ROUND ((SUM (fs.BYTES) - df.bytes_free) * 100 / fs.BYTES) ts_pct1
    FROM dba_temp_files fs,
         (SELECT   tablespace_name, bytes_free, bytes_used
              FROM v$temp_space_header
          GROUP BY tablespace_name, bytes_free, bytes_used) df
   WHERE fs.tablespace_name(+) = df.tablespace_name
GROUP BY df.tablespace_name, fs.BYTES, df.bytes_free, df.bytes_used
ORDER BY 4 DESC
/

Thanks,
Chowdari

To get lsinventory with SQL statement in 12.1.0.2

In Oracle Database 12c there's DBMS_QOPATCH package available which is very useful to query plenty of information about patches from within the database.

The DBMS_QOPATCH package provides a PLSQL/SQL interface to view the installed database patches. The interface provides all the patch information available as part of the OPATCH LSINVENTORY -XML command. The package accesses the OUI patch inventory in real time to provide patch and meta-information.

A client-server database connection won't be able to run OPatch easily and hence the DBMS_QOPATCH API is required.

Find below scripts to get lsinventory with SQL.

1)  Query to find which patches have been applied:

      SQL> set serverout on

      SQL> exec dbms_qopatch.get_sqlpatch_status;    
2)  Query to find lsinventory from SQL:

      SQL> with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
                select x.*
                from a,
                xmltable('InventoryInstance/patches/*'
                passing a.patch_output
                columns
                patch_id number path 'patchID',
                patch_uid number path 'uniquePatchID',
                description varchar2(80) path 'patchDescription',
                applied_date varchar2(30) path 'appliedDate',
                sql_patch varchar2(8) path 'sqlPatch',
                rollbackable varchar2(8) path 'rollbackable') x;

3)  Query to find ORACLE_HOME and inventory:

      SQL> set pagesize 0
      SQL> set long 1000000
      SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;

Hope these scripts will help you.

Thanks,
Chowdari

Find your ORACLE session ID number

In Oracle, it provides a view V$SESSION to display all current session details. There are other several queries you can use to see your own session ID.

Find below queries to get your own session ID:

select sys_context('USERENV','SID') SESSION_ID from dual;

select distinct sid from v$mystat;

select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;

select sid from v$mystat where rownum <=1;

Sample Outputs:

SQL> select sys_context('USERENV','SID') SESSION_ID from dual;

SESSION_ID
--------------------------------------------------------------------------------
283

SQL>

SQL> select sid from  v$mystat where  rownum <=1;

       SID
----------
       283

SQL> select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;

     MYSID
----------
       283

SQL> select distinct sid from v$mystat;

       SID
----------
       283

SQL>


Thanks,
Chowdari

Query to check characterset in Oracle

Below are the queries to check characterset and NLS parameters in database.

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

Sample output:

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

VALUE$
-----------------------
AL32UTF8

SQL> set linesize 200
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                                               VALUE
-----------------------------------------           -------------------------------------------------------------------------
NLS_CSMIG_SCHEMA_VERSION                  5
NLS_NCHAR_CHARACTERSET                UTF8
NLS_LANGUAGE                                         AMERICAN
NLS_TERRITORY                                         AMERICA
NLS_CURRENCY                                          $
NLS_ISO_CURRENCY                                AMERICA
NLS_NUMERIC_CHARACTERS               .,
NLS_CHARACTERSET                              AL32UTF8
NLS_CALENDAR                                        GREGORIAN
NLS_DATE_FORMAT                                 DD-MON-RR
NLS_DATE_LANGUAGE                          AMERICAN
NLS_SORT                                                   BINARY
NLS_TIME_FORMAT                                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                  DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                        HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT           DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                              $
NLS_COMP                                                BINARY
NLS_LENGTH_SEMANTICS                  BYTE
NLS_NCHAR_CONV_EXCP                   FALSE
NLS_RDBMS_VERSION                         11.2.0.3.0

21 rows selected.

SQL>

Thats it.. Hope this will help you.. :)

Thanks,
Chowdari.

Query to check locally managed tablespaces

Below query to identify tablespaces are in locally or dictionary managed tablespaces:

SELECT tablespace_name,extent_management FROM dba_tablespaces;

Sample Output:

SQL> SELECT tablespace_name,extent_management FROM dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
SYSAUX                         LOCAL
TEMP                              LOCAL
.............................
.............................
............................
XDB                                LOCAL
APPS_UNDOTS1           LOCAL

15 rows selected.

SQL>

Its possible all tablespaces may be locally managed and below query to find tablespaces are in locally managed.

SELECT tablespace_name,extent_management FROM dba_tablespaces where extent_management='LOCAL';

Same way below query to find dictionary managed tablespaces:

SELECT tablespace_name,extent_management FROM dba_tablespaces where extent_management='DICTIONARY';

Thats it.. Hopw this will help you.. :)

Regards,
Chowdari

Script: Display SGA Statistics in Oracle Database

Below script is useful for find database SGA statistics.

rem -----------------------------------------------------------------------
rem Filename:   sga_stat.sql
rem Purpose:    Display database SGA statistics
rem created by: http://mbc-dba.blogspot.com/
rem -----------------------------------------------------------------------
prompt Recommendations:
prompt =======================
prompt* SQL Cache Hit rate ratio should be above 90%, if not then increase the Shared Pool Size.
prompt* Dict Cache Hit rate ratio should be above 85%, if not then increase the Shared Pool Size.
prompt* Buffer Cache Hit rate ratio should be above 90%, if not then increase the DB Block Buffer value.
prompt* Redo Log space requests should be less than 0.5% of redo entries, if not then increase log buffer.
prompt* Redo Log space wait time should be near to 0.
prompt
set serveroutput ON
DECLARE
      libcac number(10,2);
      rowcac number(10,2);
      bufcac number(10,2);
      redlog number(10,2);
      redoent number;
      redowaittime number;
BEGIN
select value into redlog from v$sysstat where name = 'redo log space requests';
select value into redoent from v$sysstat where name = 'redo entries';
select value into redowaittime from v$sysstat where name = 'redo log space wait time';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
        and ncu.name = 'db block gets'
        and con.statistic# = nco.statistic#
        and nco.name = 'consistent gets'
        and phys.statistic# = nph.statistic#
        and nph.name = 'physical reads';
dbms_output.put_line('SGA CACHE STATISTICS');
dbms_output.put_line('********************');
dbms_output.put_line('SQL Cache Hit rate = '||libcac);
dbms_output.put_line('Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('Redo Log space requests = '||redlog);
dbms_output.put_line('Redo Entries = '||redoent);
dbms_output.put_line('Redo log space wait time = '||redowaittime);
if
 libcac < 90  then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
 rowcac < 85  then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
 bufcac < 90  then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
 redlog > 1000000 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/

Sample Output:

SGA CACHE STATISTICS
********************
SQL Cache Hit rate = 99.99
Dict Cache Hit rate = 99.62
Buffer Cache Hit rate = 33.6
Redo Log space requests = 649618
Redo Entries = 18292985697
Redo log space wait time = 2153535
*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.


PL/SQL procedure successfully completed.

SQL>

Hope this will help you.. :)

Best Regards,
Chowdari

Script: Check RMAN Backup job Status

Below script is very useful for checking RMAN backup job status.

set pagesize 1000
set lin 400
COL COMPRESSION format 99.9
COL Input_GB FORMAT 99999.9999
COL Output_GB FORMAT 99999.99
COL Time FORMAT a10
alter session set nls_date_format='YYYY-MM-DD HH24:MI';
SELECT
        STATUS,
        START_TIME,
        input_type Type,
       COMPRESSION_RATIO Compression,
       INPUT_BYTES/1024/1024/1024 Input_GB ,
       OUTPUT_BYTES/1024/1024/1024 Output_GB,
       TIME_TAKEN_DISPLAY Time
FROM   V$RMAN_BACKUP_JOB_DETAILS
ORDER BY START_TIME;

Sample Output:

STATUS                START_TIME                      TYPE          COMPRESSION    INPUT_GB   OUTPUT_GB    TIME
----------------------- -------------------------------------           -------------     ------------------------ ----------------   -------------------    ----------
COMPLETED      2014-11-13 22:27                    DB INCR         24.5                9366.1675    381.88            14:29:11
COMPLETED      2014-11-14 22:27                    DB INCR         35.1                8868.0660    252.77            15:46:57
RUNNING             2014-11-16 00:18                   DB INCR          5.6                  1918.7863    339.77            08:35:29

Hope this will help you.. :)

Best Regards,
Chowdari

Script: Oracle Database Growth per Month

Here you can find two scripts those are database growth per month and tablespace level database growth.

The below script lists the details of database growth per month:

select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by  to_char(creation_time, 'MM-RRRR');

Sample output from the script:

Month                                                                       Growth in GB
------------------------------------------------------------      -----------------
05-2014                                                                       101.588867
06-2014                                                                       525.609375
07-2014                                                                             57.5
09-2014                                                                               10
10-2014                                                                       31.0976563
11-2014                                                                               52

Below script is useful for tablespace level database growth:

select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , round(AVG(inc_used_size_mb),2)avg_increas_mb  

from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb

from ( SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days  ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb

from dba_hist_tbspc_space_usage  tsu , dba_hist_tablespace_stat  ts ,dba_hist_snapshot  sp, dba_tablespaces  dt   where tsu.tablespace_id= ts.ts# 

AND tsu.snap_id = sp.snap_id

AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7

GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname 

ORDER BY ts.tsname, days ) a ) b GROUP BY b.tsname ORDER BY b.tsname;

Sample output from the script:

TABLESPACE_NAME                CUR_USED_SIZE_MB AVG_INCREAS_MB

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

DW_AGG                                       8150.31                                 46.94

DW_DAC_REP_DATA                    858.31                                   3.29

DW_DIM_DATA                             7078.81                                 6

DW_DIM_INDX                              3229.69                                3.89

DW_DIM_STG                                1202.38                                -9.61

DW_FACT_DATA                          304706.94                            894.51

DW_FACT_INDX                           32227.81                             183.04

DW_FACT_STG                             5483.81                                120.7

DW_INFA_DOMAIN_DATA              1                                      0

DW_INFA_REP_DATA                 3617.88                                2.43

DW_OTHER                                  14314.88                              83.68

DW_OTHER_INDX                        203.81                                  .71

PRD_BIPLATFORM                      7493                                     64.57

PRD_MDS                                        13.44                                    0

SYSAUX                                       7097.75                                 -3.94

SYSTEM                                       2408.31                                10.43

UNDOTBS1                                 62488.13                               7074.66

UNDOTBS2                                 13088.38                              -866.43

UNDOTBS3                                  81902                                   10326

USERS                                          2015.13                                  -2.68

Hope this scripts will help you. :)

Best Regards,

Chowdari

ORA-31685 Error while import using IMPDP

Got a ORA-31685 error on schema import with remap option.

ORA-31685: Object type REF_CONSTRAINT:"DIGITAL"."FK_SEVERITY" failed due to insufficient privileges.
Failing sql is: ALTER TABLE "DIGITAL"."DIGITAL_ALLERGYGRPTRANSACTION" ADD CONSTRAINT "FK_SEVERITY" FOREIGN KEY ("SEVERITYID") REFERENCES "EHIS"."ALLERGYSEVERITYMASTER" ("ALLERGYSEVERITYID") ENABLE

I tried in many ways to create FOREIGN KEY but still its showing insufficient privileges error. Finally I get to know that its below grant issue.

grant REFERENCES on EHIS.ALLERGYSEVERITYMASTER to DIGITAL;

Hope this will help you.

Best regards,

Find CPU and Memory info in AIX Machine

Below commands to find memory information in AIX machines:

lparstat -i | grep Memory
lsconf | grep Memory

lparstat:

 The lparstat command provides a report of LPAR (Reports logical partition) related information and utilization statistics.

Sample outputs:

bash-3.2$ lparstat -i | grep Memory
Online Memory                              : 65536 MB
Maximum Memory                             : 98304 MB
Minimum Memory                             : 36864 MB
Memory Mode                                : Dedicated
Total I/O Memory Entitlement               : -
Variable Memory Capacity Weight            : -
Memory Pool ID                             : -
Physical Memory in the Pool                : -
Unallocated Variable Memory Capacity Weight: -
Unallocated I/O Memory entitlement         : -
Memory Group ID of LPAR                    : -
Desired Memory                             : 65536 MB
Target Memory Expansion Factor             : -
Target Memory Expansion Size               : -
bash-3.2$

bash-3.2$ lsconf | grep Memory
Memory Size: 65536 MB
Good Memory Size: 65536 MB
+ mem0                                                                          Memory
bash-3.2$

Below commands to find CPU information in AIX machines:

lsconf | grep Processor
lscfg -vp | grep proc

Sample outputs:

bash-3.2$ lsconf | grep Processor
Processor Type: PowerPC_POWER7
Processor Implementation Mode: POWER 7
Processor Version: PV_7_Compat
Number Of Processors: 6
Processor Clock Speed: 3550 MHz
  Model Implementation: Multiple Processor, PCI bus
+ proc0                                                                         Processor
+ proc4                                                                         Processor
+ proc8                                                                         Processor
+ proc12                                                                        Processor
+ proc16                                                                        Processor
+ proc20                                                                        Processor
bash-3.2$
bash-3.2$ lscfg -vp | grep proc
  proc0                                                                         Processor
  proc4                                                                         Processor
  proc8                                                                         Processor
  proc12                                                                        Processor
  proc16                                                                        Processor
  proc20                                                                        Processor
bash-3.2$

Hope this will help you... :)

Best Regards,

Script: Some Useful Oracle Database Monitoring Commands for DBA's - PART1

Find Blocking session:

select INST_ID,sid,serial#,username,status,BLOCKING_SESSION_STATUS,
terminal,program,sql_id,BLOCKING_SESSION,EVENT
 from gv$session a where BLOCKING_SESSION  IS NOT NULL;

Find Blocked Object:

select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine
from gv$locked_object a ,gv$session b,dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;

Identify the Hung Materialized View: 

select VS.INST_ID,VL.SID||','||VS.SERIAL#,VS.USERNAME,ao.object_name,
'alter system kill session '''||vl.SID||','||vs.SERIAL#|| ',@'||vs.inst_id ||''' IMMEDIATE;'
  from GV$LOCK VL, Gv$session VS, all_objects ao
 where vl.type = 'JI' and vl.Lmode = 6 and VS.SID=VL.SID
 and VS.INST_ID=VL.inst_id and vl.ID1=ao.object_id;

Find Pending Transaction:

select * from pending_trans$ where state='prepared'
select 'commit force '''||local_tran_id||''';' from pending_trans$ where state='prepared'
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('transanction_id');

Total Active Sessions Count - EXCLUDING BACKGROUND SESSIONS:

select inst_id,count(1)  from gv$session g
where type != 'BACKGROUND' and status='ACTIVE' GROUP BY INST_ID order by inst_id;

Active Session Count Excluding Background Sessions:

select inst_id,username,count(1)   from gv$session
 where type != 'BACKGROUND' and status='ACTIVE' and username is not null
 group by inst_id,username order by count(1) desc;

Program-wise Session count:

select inst_id,program,count(1)
from gv$session where type<>'BACKGROUND' AND USERNAME IS NOT NULL
group by inst_id,program order by 2 desc;

w3wp.exe sessions - Worker Process:

select inst_id,machine,count(1) from gv$session
where program='w3wp.exe' and status='INACTIVE'
group by inst_id,machine
order by 2;

User Session Consuming Concurrency, Cluster, User I/O:

Select inst_id "Inst",SID||','||serial# "SidS#",username,PROGRAM,sql_id,wait_class,status,machine,terminal,logon_time
from gv$session where type != 'BACKGROUND'   and status = 'ACTIVE'
and username is not null and wait_class in
('Concurrency', 'System I/O', 'User I/O','Network','Apllication')
order by inst_id, username;

Find FRA Size:

select name,
       space_limit / 1024 / 1024 / 1024 as Total_size,
       space_used / 1024 / 1024 / 1024 as Used,
       SPACE_RECLAIMABLE / 1024 / 1024 / 1024 as reclaimable,
       NUMBER_OF_FILES as "number"
  from V$RECOVERY_FILE_DEST;

Thats it..Hope this will help you.. :)

Best Regards,