How to find the top 10 sql's and Find How much PGA usage in Oracle 19C DB

 Dear All,

In this post i am sharing few useful SQL's and How to find the top 10 sql's and Find How much PGA usage in Oracle 19C DB.

1) To find corrupt Object/Segments using file_id and block_id

select segment_name,block_id,file_id from dba_extents where FILE_ID=&1 and &2 between block_id and block_id+blocks-1

/

 Indexes created on Table_name

set lin 10000

col INDEX_NAME for a30

col TABLE_NAME for a30

col index_type for a25

col status for a10

--col LAST_ANALYZED for a19 wrap

col column_name for a20

col column_expression for a25

select a.INDEX_NAME ,b.column_name, column_expression,a.TABLE_NAME,STATUS,INDEX_TYPE

from user_indexes a,user_ind_columns b, user_ind_expressions c

where a.table_name = upper(trim('&1'))

and a.TABLE_NAME=b.TABLE_NAME

and a.INDEX_NAME=b.INDEX_NAME

and a.INDEX_NAME=c.INDEX_NAME (+)

and a.INDEX_NAME=c.INDEX_NAME (+)

/ 

Current running query

set lin 10000

col sid for 999999999

col serial# for  9999999999

col osuer for a20

col SQL_TEXT for a70 wrap

col osuser for a10

col status for a10

--select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a,  v$sqlarea b

--where a.SQL_ADDRESS=b.ADDRESS

--and b.HASH_VALUE = a.SQL_HASH_VALUE

select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a,  v$sql b

where a.SQL_ADDRESS=b.ADDRESS

and b.HASH_VALUE = a.SQL_HASH_VALUE

order by 6 desc,5 desc

/

To find query how much memory (PGA)

SET LINESIZE 145

SET PAGESIZE 9999

COLUMN sid FORMAT 999  HEADING 'SID'

COLUMN oracle_username  FORMAT a12     HEADING 'Oracle User'     JUSTIFY right

COLUMN os_username FORMAT a9  HEADING 'O/S User' JUSTIFY right

COLUMN session_program  FORMAT a18     HEADING 'Session Program' TRUNC

COLUMN session_machine  FORMAT a18      HEADING 'Machine'  JUSTIFY right TRUNC

COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'

COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'

COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'

COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

SELECT

    s.sid  sid

  , lpad(s.username,12)  oracle_username

  , lpad(s.osuser,9)     os_username

  , s.program     session_program

  , lpad(s.machine,8)    session_machine

  , (select ss.value from v$sesstat ss, v$statname sn

     where ss.sid = s.sid and

    sn.statistic# = ss.statistic# and

    sn.name = 'session pga memory') session_pga_memory

  , (select ss.value from v$sesstat ss, v$statname sn

     where ss.sid = s.sid and

    sn.statistic# = ss.statistic# and

    sn.name = 'session pga memory max')    session_pga_memory_max

  , (select ss.value from v$sesstat ss, v$statname sn

     where ss.sid = s.sid and

    sn.statistic# = ss.statistic# and

    sn.name = 'session uga memory') session_uga_memory

  , (select ss.value from v$sesstat ss, v$statname sn

     where ss.sid = s.sid and

    sn.statistic# = ss.statistic# and

    sn.name = 'session uga memory max')    session_uga_memory_max

FROM

    v$session  s

ORDER BY session_pga_memory DESC

/

 SID  Oracle User  O/S User Session Program               Machine     PGA Memory PGA Memory Max     UGA Memory UGA Memory MAX

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

  20                 oracle oracle@ggnode1 (M0  ggnode1               32,850,736     59,786,032     23,623,584     29,389,984

  18                 oracle oracle@ggnode1 (MM  ggnode1               23,875,304     26,562,280      4,287,472     21,663,552

 252                 oracle oracle@ggnode1 (TT  ggnode1               20,333,360     20,333,360        367,072        367,072

 274                 oracle oracle@ggnode1 (J0  ggnode1               11,682,608    104,809,264        367,072        367,072

 246                 oracle oracle@ggnode1 (DB  ggnode1                8,953,728     10,478,112        367,072        367,072

  42                 oracle oracle@ggnode1 (M0  ggnode1                5,718,832              0         65,488        628,864

 262                 oracle oracle@ggnode1 (M0  ggnode1                5,522,224      8,274,736      3,650,952      5,102,328

  41          SYS    oracle sqlplus@ggnode1 (T  ggnode1                5,006,336     19,489,792      1,611,368      2,893,104

   9                 oracle oracle@ggnode1 (DI  ggnode1                4,844,408      4,844,408        367,072        367,072

  27                 oracle oracle@ggnode1 (CJ  ggnode1                4,411,112      7,294,696      2,462,592      5,014,824

  28                 oracle oracle@ggnode1 (W0  ggnode1                3,556,144      4,408,112      2,070,120      2,633,496

   7                 oracle oracle@ggnode1 (DB  ggnode1                3,533,688      4,123,512      1,742,320      1,873,296

 275                 oracle oracle@ggnode1 (W0  ggnode1                3,425,072      4,408,112      1,929,584      3,006,712

 251                 oracle oracle@ggnode1 (LR  ggnode1                3,354,136      3,681,816        970,864      1,152,952

 250                 oracle oracle@ggnode1 (RE  ggnode1                2,764,312      3,026,456        970,856      1,280,040

  12                 oracle oracle@ggnode1 (SM  ggnode1                2,698,776      3,419,672        970,864      1,458,232

   6          SYS    oracle oracle@ggnode1 (OF  ggnode1                2,567,704      2,567,704        825,488        890,976

  38                 oracle oracle@ggnode1 (Q0  ggnode1                2,442,032      2,900,784      1,036,344      1,341,656

  10                 oracle oracle@ggnode1 (LG  ggnode1                2,379,496      2,379,496        367,072        367,072

  11                 oracle oracle@ggnode1 (CK  ggnode1                2,282,496      2,282,496        367,072        367,072

   4                 oracle oracle@ggnode1 (W0  ggnode1                2,245,424      2,573,104        839,888      1,210,680

   3                 oracle oracle@ggnode1 (GE  ggnode1                2,174,488      2,174,488        367,072        367,072

 263                 oracle oracle@ggnode1 (Q0  ggnode1                2,114,352      2,114,352        498,048        498,048

 259                 oracle oracle@ggnode1 (W0  ggnode1                2,114,352      6,964,016        498,048        498,048

 256                 oracle oracle@ggnode1 (AQ  ggnode1                2,043,416      2,043,416        367,072        367,072

  13                 oracle oracle@ggnode1 (W0  ggnode1                2,031,760      7,291,696        629,048      1,283,976

  15                 oracle oracle@ggnode1 (W0  ggnode1                1,917,744      2,442,032        498,056      1,152,952

   8                 oracle oracle@ggnode1 (SV  ggnode1                1,912,344      1,912,344        367,072        367,072

 253                 oracle oracle@ggnode1 (MM  ggnode1                1,912,344      1,912,344        367,072        432,560

 245                 oracle oracle@ggnode1 (PM  ggnode1                1,846,808      1,846,808        367,072        367,072

 244                 oracle oracle@ggnode1 (VK  ggnode1                1,846,808      1,846,808        367,072        367,072

 242                 oracle oracle@ggnode1 (DI  ggnode1                1,846,808      1,846,808        367,072        367,072

 241                 oracle oracle@ggnode1 (GE  ggnode1                1,846,808      1,846,808        367,072        367,072

 240                 oracle oracle@ggnode1 (MM  ggnode1                1,846,808      1,846,808        367,072        367,072

  19                 oracle oracle@ggnode1 (TM  ggnode1                1,846,808      1,846,808        367,072        367,072

  17                 oracle oracle@ggnode1 (PX  ggnode1                1,846,808      1,846,808        367,072        367,072

 239                 oracle oracle@ggnode1 (VK  ggnode1                1,846,808      1,846,808        367,072        367,072

   2                 oracle oracle@ggnode1 (PS  ggnode1                1,846,808      1,846,808        367,072        367,072

 249                 oracle oracle@ggnode1 (SM  ggnode1                1,846,808      1,846,808        367,072        367,072

 238                 oracle oracle@ggnode1 (CL  ggnode1                1,797,728      1,797,728        367,072        367,072

   1                 oracle oracle@ggnode1 (PM  ggnode1                1,797,728      1,797,728        367,072        367,072

 255                 oracle oracle@ggnode1 (Q0  ggnode1                1,786,672      1,786,672        432,560        432,560

  24                 oracle oracle@ggnode1 (TT  ggnode1                1,655,600      1,655,600        367,072        367,072

  22                 oracle oracle@ggnode1 (QM  ggnode1                1,655,600      1,655,600        367,072        367,072

   5                 oracle oracle@ggnode1 (SC  ggnode1                1,655,600      1,655,600        367,072        367,072

 269                 oracle oracle@ggnode1 (W0  ggnode1                1,655,600      1,655,600        367,072        367,072

 248                 oracle oracle@ggnode1 (LG  ggnode1                1,655,600      1,655,600        367,072        367,072

 247                 oracle oracle@ggnode1 (LG  ggnode1                1,655,600      1,655,600        367,072        367,072

 237                 oracle oracle@ggnode1 (TT  ggnode1                1,655,600      1,655,600        367,072        367,072

 243                 oracle oracle@ggnode1 (SC  ggnode1                1,655,600      1,655,600        367,072        367,072

  35                 oracle oracle@ggnode1 (W0  ggnode1                1,655,600      1,655,600        367,072        367,072

51 rows selected.

Find  Active SQL old versions ::: 

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,

    SID,  

    MACHINE,

    REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,

    ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'

    || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'

    || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT

FROM    V$SESSION SES,  

    V$SQLtext_with_newlines SQL

where SES.STATUS = 'ACTIVE'

    and SES.USERNAME is not null

    and SES.SQL_ADDRESS    = SQL.ADDRESS

    and SES.SQL_HASH_VALUE = SQL.HASH_VALUE

    and Ses.AUDSID <> userenv('SESSIONID')

order by runt desc, 1,sql.piece;

Thanks,

Srini

Oracle Deep Data Security - lab examples and use cases - 2026

 Hi All,

In this post i am going share about Oracle Deep Data Security .

You will learn how to shift from fragmented, application-based controls to a centralized, declarative model that evaluates identity and context at runtime. 

The brief offers practical guidance for enforcing least-privilege access, protecting AI-driven workflows such as retrieval-augmented generation (RAG), and allowing agents to operate within clearly defined guardrails.

 It also explains how capabilities such as cell-level authorization, secure identity propagation, and controlled privilege elevation can reduce risk without sacrificing flexibility or performance.

 Identity & context-aware access control across workloads :::

Key benefits include: 

 Reduced data exposure risk with database-enforced authorization across all access paths .

 Least-privilege access for users and non-human identities, including AI agents .

 Rapid adaptation to changing security requirements by separating policy from application code. 

 Strengthened governance with centralized, end-user-aware auditing.

 Performance and scale maintained for enterprise workloads.

Why Agentic AI increases the risk  ? 

Agentic AI changes how applications interact with data. Instead of executing predefined logic, agents generate SQL at runtime based on user input and model reasoning. This shift introduces new security risks that traditional controls were not designed to address.

Declarative policy model  :

Deep Data Security enforces access control through declarative SQL policies, referred to as Data Grants. These policies define which operations—such as SELECT, INSERT, UPDATE, and DELETE—are allowed on specific rows, columns, or individual data values. 

Policies use SQL predicates to identify the data they apply to and can incorporate joins, subqueries, and runtime attributes. This enables precise, context-aware decisions based on both user attributes and data relationships.

 Policy for row-level access ::

Cell-level authorization ::

 Cell-level authorization enables control over individual data values within a row. 

This allows organizations to enforce strict least-privilege access without creating complex views or duplicating data structures. 

For example: 

 Employees can view their own records but update only contact details .

 Managers can update salaries for direct reports but not their own.

 Sensitive attributes such as SSNs remain restricted even when other fields are visible

Runtime policy enforcement ::

  At runtime, Deep Data Security evaluates authorization policies and transparently rewrites queries and other SQL operations, independent of application logic, to enforce authorization controls. In effect, Deep Data Security serves as the policy decision point (PDP), while the database SQL engine functions as the policy enforcement point (PEP). This helps ensure end users can access only authorized data, regardless of the SQL executed by an agent or application, which helps mitigate prompt injection and SQL injection attacks.

Authorization APIs ::

  

End-user security context

The Oracle Deep Data Security advantage  ::

Securing AI-driven access requires enforcement at the data layer. 

Oracle Deep Data Security, built into Oracle AI Database 26ai, embeds centralized, declarative authorization policies directly in the database. By separating authorization logic from application code, it helps organizations apply consistent access controls across agentic AI, analytics, and enterprise applications, without depending on how SQL is generated. 

Realizing enterprise benefits :::

 Oracle Deep Data Security helps organizations scale AI adoption while maintaining governance and control: 

Enforce identity- and context-aware access: Evaluate runtime security context for both human users and AI agents to support least-privilege access. 

 Protect data at a granular level: Apply row-, column-, and cell-level authorization without requiring complex application logic or data duplication. 

 Simplify application development: Decouple authorization policies from application code to reduce hardcoded logic and streamline updates. 

 Apply consistent policy enforcement: Extend controls across relational data, vector data used in retrieval augmented generation (RAG), and heterogeneous Lakehouse environments. 

Strengthen governance and auditing: Centralize auditing of database activity with end-user and agent attribution to support security and compliance requirements.

Thanks,

Srini

Oracle 19C DB How to export the dump file using LOGTIME parameter step by step lab 2026

 Dear All,

In this post i am sharing Oracle 19C DB  How to export the dumpfile using LOGTIME parameter step by step lab .

This parameter LOGTIME give us the timestamp for each and every export operation during expdp., therefore its easy to check the diagnise/elapsed time taken for execution.

 default LOGTIME=NONE, If you want to use this feature use this parameter in expdp command.

Types of LOGTIME parameters:

LOGTIME=NONE : Default value which will not display timestamp in both command prompt as well as export log file.

LOGTIME=ALL : Display the timestamp in both command prompt as well as export log file.

LOGTIME=LOGFILE : Display the timestamp in only export log file, not in command prompt.

LOGTIME=STATUS : Display the timestamp in only command prompt and not in export log file.

Check the DB version and status :

create schema and that schema we can use it for this lab

grant the schema and connect to that schema create some objects 

Now take the export of the dump file for his schema 

if schema exist in CDB$ROOT use below command :

expdp directory=DATA_PUMP_DIR dumpfile=racsdev_sch.dmp logfile=racsdev_sch.log schemas=racsdev LOGTIME=ALL

If schema exist in PDB use below command

Logtime parameter for all option ::  

expdp system/****@pdb1 directory=DATA_PUMP_DIR dumpfile=racsdev_sch1.dmp logfile=racsdev_sch1.log schemas=racsdev LOGTIME=ALL

Without logtime parameter output 

Logtime parameter with logfile option : 

Display the timestamp in only export log file, not in command prompt.

Logtime parameter STATUS option 

Display the timestamp in only command prompt and not in export log file

Hope this will help when you are working with real-time tasks, with LOGTIME options.

Thanks,

Srini