TOP N PARTITION FROM EACH PARTITION TABLE

 TOP N PARTITION NAMES FROM EACH PARTITION TABLE IN SCHEMA:

SELECT

    table_name,

    LISTAGG(partition_name, '   ') WITHIN GROUP(

        ORDER BY

            partition_position

    ) AS partition_names

FROM

    (

        WITH rws AS (

            SELECT

                o.*,

                ROW_NUMBER() OVER(

                    PARTITION BY table_name

                    ORDER BY

                        partition_position DESC

                ) rn

            FROM

                user_tab_partitions o

        )

        SELECT

            table_name,

            partition_name,

            partition_position

        FROM

            rws

        WHERE

            rn <= 10

        ORDER BY

            table_name,

            partition_position

    )

GROUP BY

    table_name

ORDER BY

    table_name;

It will display top 10 partitions of each table in a schema.

Output will have 1 row per each table.

How to Enable Trace service name and module, another session in oracle

How to Enable Trace servicename and module, another session:

Note:

Test case 1 and Test case 2 : Enable /Disable tracing of sessions on combination of Service name and Module name

Test 3:   Enable/Disable trace on a particular user based on SID,SERIAL# .

Test case 1:

======================

Session 1:

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

sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1

BEGIN

  DBMS_APPLICATION_INFO.set_module(module_name => 'MODULE_NAME',action_name => NULL); 

END;

/

session 2:

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

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=> 'DBNAME1',  module_name=>'MODULE_NAME',  action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE, binds=>TRUE ,plan_stat=>'ALL_EXECUTIONS');

Session 1:

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

create table test1 (a number, b number);

insert into test1 values (4,5);

insert into test1 values (4,5);

commit;

session 2:

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

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'DBNAME1', module_name=>'MODULE_NAME');

 

 

Session 1:

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

exit;

DBNAME1_ora_14963.trc:create table test1 (a number, b number)

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:  value="TEST1"

DBNAME1_ora_14963.trc:insert into test1 values (4,5)

DBNAME1_ora_14963.trc:STAT #140240859786064 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  TEST1 (cr=4 pr=0 pw=0 str=1 time=621 us)'

DBNAME1_ora_14963.trc:insert into test1 values (4,5)

DBNAME1_ora_14963.trc:STAT #140240859786064 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  TEST1 (cr=0 pr=0 pw=0 str=1 time=91 us)'

Test case2:

======================

Session 1:

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

sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1

BEGIN

  DBMS_APPLICATION_INFO.set_module(module_name => 'MODULE_NAME_2',action_name => NULL); 

END;

/

session 2:

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

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=> 'DBNAME1',  module_name=>'MODULE_NAME_2',  action_name=>DBMS_MONITOR.ALL_ACTIONS,waits=>TRUE, binds=>TRUE ,plan_stat=>'ALL_EXECUTIONS');

Session 1:

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

create table test2 (a number, b number);

insert into test1 values (4,5);

insert into test1 values (4,5);

commit;

session 2:

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

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name=>'DBNAME1', module_name=>'MODULE_NAME_2');

 

  

  

  

DBNAME1_ora_17154.trc:create table test2 (a number, b number)

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

DBNAME1_ora_17154.trc:  value="TEST2"

 

TEST CASE 3:

======================

 

Session 1:

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

sqlplus USERNAME/USERNAME@scanname.example.com:1521/DBNAME1

session 2:

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

sqlplus sys as sysdba

col module for a30

col program for a30

col machine for a30

select sid, serial#, module, program, machine,SERVICE_NAME  from gv$session where username is not null;

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual; 

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 392, serial_num => 45590,waits => TRUE, binds => TRUE);

 

 

Session 1:

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

create table test3 (a number, b number);

insert into test1 values (4,5);

insert into test1 values (4,5);

commit;

session 2:

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

sqlplus sys as sysdba

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;

select sysdate from dual; 

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 392, serial_num => 45590);

DBNAME1_ora_19678.trc:create table test3 (a number, b number)

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

DBNAME1_ora_19678.trc:  value="TEST3"

Queries to find trace files of particualr SQL or whole trace file:

select distinct INST_ID, adr_home, trace_filename from gv$diag_trace_file_contents 

where regexp_like (payload, '8h284jvnd0z4k|g3jkap9hxf75k  )

and trace_filename like 'DBNAME%ora%.trc'

and component_name='SQL_Trace' 

--and TIMESTAMP > sysdate -0.5

select PAYLOAD from gv$diag_trace_file_contents 

where trace_filename='DBNAME_ora_6338.trc'

and inst_id=1

and adr_home='..'

order by line_number