Oracle Memory usage Queries

Some Important Oracle database Memory management queries.

SGA usage by Oracle Instance:

select  round(sum(bytes)/1024/1024,2)||' MB' total_sga,round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used,round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;

SGA Stat:

SELECT pool, name, bytes / 1024 / 1024 AS "MB"

FROM v$sgastat

WHERE pool IN ('java pool', 'shared pool', 'large pool', 'streams pool')

ORDER BY pool, bytes DESC;

Total SGA:

SELECT sum(bytes)/1024/1024 AS "Total SGA Size (MB)"

FROM v$sgastat;

Using V$PROCESS to see individual process memory usage

SELECT p.spid, p.program, p.pga_max_mem, p.pga_alloc_mem, p.pga_used_mem, p.pga_freeable_mem

FROM v$process p;

Using V$PGASTAT for overall PGA statistics

SELECT name, value

FROM v$pgastat;

 

 

Best Wishes!!! 

 Check progress on expdp and impdp:

In few cases we need to monitor the progress of an export or import job in oracle. below are the steps can be followed to get the details.

Datapump Client:

get the session details: 

SQL> select * from dba_datapump_jobs where state='EXECUTING';

attach the session:

from Command line:

    expdp system/********** attach=SYS_EXPORT_FULL_01

    Export > status

From Database:

Query 1: The % of work done for the running job can be found from this query:

  

set pages 200

set lines 300

col USERNAME for a15

col TARGET for a15

col OPNAME for a20

col "%DONE" for a7

SELECT b.username,
         a.sid,
         b.opname,
         b.target,
         ROUND (b.SOFAR * 100 / b.TOTALWORK, 0) || '%'       AS "%DONE",
         b.TIME_REMAINING,
         TO_CHAR (b.start_time, 'YYYY/MM/DD HH24:MI:SS')     start_time
    FROM v$session_longops b, v$session a
   WHERE a.sid = b.sid
ORDER BY 6;

Query 2: The work done so far and status of the job:

SELECT sl.sid,
       sl.serial#,
       sl.sofar,
       sl.totalwork,
       dp.owner_name,
       dp.state,
       dp.job_mode
  FROM v$session_longops sl, v$datapump_job dp
 WHERE sl.opname = dp.job_name AND sl.sofar != sl.totalwork;

Query 3: The amount of work done so far:

SELECT sl.sid,
       sl.serial#,
       sl.sofar,
       sl.MESSAGE,
       sl.totalwork,
       dp.owner_name,
       dp.state,
       dp.job_mode
  FROM v$session_longops sl, v$datapump_job dp
 WHERE sl.opname = dp.job_name;

Reference:

How To Monitor The Progress Of Datapump Jobs (Doc ID 1471766.1)

Best Wishes!!

Unix-Linux Shell Source of environment variables

Shell - Source of env variables

In many times, we need to know what are the script is being called while setting up the environment variables, this is very handy when we are getting errors like some env variables are not setup.

If zsh is the login shell:

zsh -xl

Execute the below command to get from which file the environment is setup:

PS4='+$BASH_SOURCE> ' BASH_XTRACEFD=7 bash -xl 7>&2

Best Wishes!!

ORA-01940: cannot drop a user that is currently connected

Issue with drop user in Oracle

While Dropping a user if the drop user command is throwing error like:

ORA-01940: cannot drop a user that is currently connected

Follow the below steps:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' "SQL Statement" from v$session where username=UPPER('&username');

SQL Statement

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

alter system kill session '101,265842' immediate;

Execute the alter statement to kill the active session.

alter system kill session '101,265842' immediate;

System altered.

Now the drop user will work:

drop user <username> cascade;

Note: cascade option drops all the dependent objects under the schema.

Gather Statistics

 

Oracle Gather Stat 

Gather Table, Index and Schema Statistics

 

DBMS_STATS.GATHER_TABLE_STATS is used to gather stats for a single table

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES');

EXEC DBMS_STATS.gather_table_stats('HR','EMPLOYEES',cascade=>TRUE);

( Note: Cascade gathers Index stats associated with the table )

Failed to start ASMB (connection failed) state=0x1

Issue with Oracle ASM

Oracle DB Links

What is DB link? 

Oracle DB links are used to connect to different sources through procedures/views. It helps creating views or materialized views by pulling data from different db sources.

To check the db links present in the database, the below query can be used.

Query:

select * from dba_db_links;

SQL> desc dba_db_links;

 Name                                      Null?    Type

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

 OWNER                                     NOT NULL VARCHAR2(128)

 DB_LINK                                   NOT NULL VARCHAR2(128)

 USERNAME                                           VARCHAR2(128)

 HOST                                               VARCHAR2(2000)

 CREATED                                   NOT NULL DATE

 HIDDEN                                             VARCHAR2(3)

 SHARD_INTERNAL                                     VARCHAR2(3)

 VALID                                              VARCHAR2(3)

 INTRA_CDB                                          VARCHAR2(3)

Create a DB link:

Create or replace database link "<DB link name>" connect as "<remote username>" identified by "<Remote user password>" using "<Remote DB TNS details>";

Hope this helps. Please let me know if you face any issues, comment below.

Find Concurrent Program Run History from backend

Query to find concurrent program run history and status

SELECT f.request_id,

         pt.user_concurrent_program_name

             user_conc_program_name,

         f.actual_start_date

             start_on,

         f.actual_completion_date

             end_on,

         p.concurrent_program_name

             concurrent_program_name,

         DECODE (f.phase_code,

                 'R', 'RUNNING',

                 'C', 'COMPLETED',

                 f.phase_code)

             phase,

         DECODE (f.status_code,  'C', 'NORMAL',  'E', 'ERROR',  f.status_code)

             Status,

         f.requested_by,

         fu.user_id,

         fu.user_name

    FROM apps.fnd_concurrent_programs   p,

         apps.fnd_concurrent_programs_tl pt,

         apps.fnd_concurrent_requests   f,

         apps.fnd_user                  fu

   WHERE     f.concurrent_program_id = p.concurrent_program_id

         AND f.program_application_id = p.application_id

         AND f.concurrent_program_id = pt.concurrent_program_id

         AND f.program_application_id = pt.application_id

         AND pt.language = USERENV ('lang')

         AND f.actual_start_date IS NOT NULL

         AND f.actual_start_date >

             TO_DATE ('15-DEC-2021 00:00:00', 'DD-MON-YYYY HH24:MI:SS')

         AND f.actual_completion_date <

             TO_DATE ('17-DEC-2021 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

         AND f.requested_by = fu.user_id

         AND pt.USER_CONCURRENT_PROGRAM_NAME = '&User_concurrent_program_name'

--AND fu.user_name = '&user_name'

ORDER BY f.actual_start_date ASC; 

##Change the date accordingly.

Temp Tablespace Usage

Oracle Temp Tablespace usage

Check for Temp tablespace usage

set lin 200;

col file_name for a75;

col autoextensible for a15;

select file_name,tablespace_name,sum(bytes)/1024/1024 as FILE_SIZE,sum(maxbytes)/1024/1024 as MAX_SIZE,autoextensible from dba_temp_files

where tablespace_name ='TEMP' group by file_name,tablespace_name,autoextensible order by file_name;

Data file usage of a tablespace

Oracle Tablespaces 

Check Data File Size in any given Tablespace (Dynamic) :

set lin 200;

col file_name for a75;

col autoextensible for a15;

select file_name,tablespace_name,sum(bytes)/1024/1024 "FILE_SIZE(MB)",sum(maxbytes)/1024/1024 as MAX_SIZE,autoextensible from dba_data_files

where tablespace_name ='&tablespace_name' group by file_name,tablespace_name,autoextensible order by file_name;

Create Oracle Database User

Oracle Database User Management

This is the simplest task for a Database Administrator. 

Login to sqlplus session from your OS(Windows/Linux/AIX) using sysdba / a user who has create user privilege. 

sqlplus / as sysdba

alter user <username> identified by <password>;

this will create a user with default tablespace and profile. Depending on the organization's policy and user's requirement you might need to mention few other options in the query. Those are advanced options you can explore more on my advance create user query.

Now user needs some privileges to connect to the database and do some transactions or query any tables.

CONNECT - this is the Oracle-defined default privilege for any user to be able to connect to the database.

grant connect to <username>;

alternatively, grant create session to <username>;  will do the same.

Now, for the user to be able to query any table in the db, one basic privilege is required.

grant select any table to <username>;

This will create a db user in Oracle. 

To see the user is created properly you can validate with below query.

select * from dba_users where username='<username>';

The dba_users is a data dictionary view which eventually being created from all_users. To know more about all_users and dba_users column properties stay tuned!!

Best Wishes!!