XscalibaL

Uncategorized

Execute Linux Commands In Python

We used individual command lines on different OS layers and the commands used on each OS may be similar but syntax is different.

Occasionally, python scripts need to be embed with other OS commands. To execute linux OS commands in python, we can use ‘subprocess’ to read the commands and print the output.

[oracle@dbperf ~]$ python3
Python 3.6.8 (default, Jan 16 2024, 02:08:59)
[GCC 8.5.0 20210514 (Red Hat 8.5.0-20.0.1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import subprocess

subprocess has a function called ‘run’ which can help us to run OS commands as required.

>>> subprocess.run(["ls","-l","/"])
total 36
drwxr-xr-x.   3 root   root        17 Apr  5  2023  apps01
lrwxrwxrwx.   1 root   root         7 Oct  9  2021  bin -> usr/bin
dr-xr-xr-x.   6 root   root      4096 May 15  2023  boot
drwxr-xr-x   19 root   root      3280 May 14 07:56  dev
drwxr-xr-x. 177 root   root     12288 May 14 07:56  etc
drwxr-xr-x.   4 root   root        34 Oct  9  2021  home
lrwxrwxrwx.   1 root   root         7 Oct  9  2021  lib -> usr/lib
lrwxrwxrwx.   1 root   root         9 Oct  9  2021  lib64 -> usr/lib64
drwxr-xr-x.   2 root   root         6 Oct  9  2021  media
drwxr-xr-x.   2 root   root         6 Oct  9  2021  mnt
drwxr-xr-x.   4 root   root        32 Apr  5  2023  opt
dr-xr-xr-x  357 root   root         0 May 14 07:56  proc
dr-xr-x---.  15 root   root      4096 May 13 22:47  root
drwxr-xr-x   52 root   root      1440 May 14 07:59  run
lrwxrwxrwx.   1 root   root         8 Oct  9  2021  sbin -> usr/sbin
drwxr-xr-x.   2 root   root         6 Oct  9  2021  srv
dr-xr-xr-x   13 root   root         0 May 14 07:55  sys
drwxrwxrwt.  22 root   root      4096 May 14 07:58  tmp
drwxrwxr-x.   3 oracle oinstall    17 Apr  5  2023  u01
drwxrwxr-x.   5 oracle oinstall    42 May 10  2023  u02
drwxr-xr-x.  13 root   root       158 Apr  5  2023  usr
drwxr-xr-x.  23 root   root      4096 Apr  5  2023  var
-rw-r--r--.   1 root   root       932 Apr  5  2023 'ystemctl stop firewalld'
CompletedProcess(args=['ls', '-l', '/'], returncode=0)

kishan

May, Tue, 2024

Uncategorized

FileNotFoundError: [Errno 2] No such file or directory: ‘home/oracle/abc.txt’

Cause:

Traceback (most recent call last):
  File "perfbot.py", line 150, in <module>
    abc = parseabc()
  File "perfbot.py", line 126, in parseabc
    file1 = open("home/oracle/abc.txt", 'w')
FileNotFoundError: [Errno 2] No such file or directory: 'home/oracle/abc.txt'

Solution:

When specifying the path, instead of specifying the relative path, just use absolute path which will solve the error.

    with open(r"home/oracle/abc.txt", 'w') as file: <==== wrong

with open(r"/home/oracle/abc.txt", 'w') as file: <=== right (just added a / in front of home directory)

kishan

May, Mon, 2024

Uncategorized

Move Data From One Partition To Another Oracle

Steps to move partition from one partition to another.

SQL> set heading off
SQL> set long 1000000000 numwidth 50
SQL> SELECT dbms_metadata.get_ddl('TABLE','RP','C##HYDRA') from dual;


  CREATE TABLE "C##HYDRA"."RP"
   (    "R_ID" NUMBER(30,0),
        "R_NAME" VARCHAR2(30),
        "R_COST" NUMBER(30,0),
        "R_DATE" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1"
  PARTITION BY RANGE ("R_DATE")
 (PARTITION "R_JAN2022"  VALUES LESS THAN (TO_DATE(' 2022-02-01 00:00:00', 'SYYY

Y-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1" ,
 PARTITION "R_FEB2022"  VALUES LESS THAN (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY
-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1" ,
 PARTITION "R_MAR2022"  VALUES LESS THAN (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY
-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1" ,
 PARTITION "R_APR2022"  VALUES LESS THAN (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY
-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1" ,
 PARTITION "R_MAY2022"  VALUES LESS THAN (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY
-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1" )

SQL> desc c##hydra.rp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 R_ID                                               NUMBER(30)
 R_NAME                                             VARCHAR2(30)
 R_COST                                             NUMBER(30)
 R_DATE                                             DATE

SQL>   CREATE TABLE "C##HYDRA"."RPNEW"
   (    "R_ID" NUMBER(30,0),
        "R_NAME" VARCHAR2(30),
        "R_COST" NUMBER(30,0),
        "R_DATE" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1"
  PARTITION BY RANGE ("R_DATE")
 (PARTITION "R_JAN2022"  VALUES LESS THAN (TO_DATE(' 2022-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN  2  S 255
 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  3    TABLESPACE "TBS1" ,
 PARTITION "R_FEB2022"  VALUES LESS THAN (TO_DATE(' 2022-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS   4  ADVANCED LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1" ,
  5   PARTITION "R_MAR2022"  VALUES LESS THAN (TO_DATE(' 2022-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR  6  ANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1" ,  7
 PARTITION "R_APR2022"  VALUES LESS THAN (TO_DATE(' 2022-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  8    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1" ,
 PARTITION  9   "R_MAY2022"  VALUES LESS THAN (TO_DATE(' 2022-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
 ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAU 10  LT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS1" ); 11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38   39   40   41   42   43   44   45

Table created.

SQL> col HIGH_VALUE for a50
SQL> SELECT partition_name,high_value FROM dba_tab_partitions WHERE table_name='RP';

PARTITION_NAME       HIGH_VALUE
-------------------- --------------------------------------------------
R_MAY2022            TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:
                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

R_MAR2022            TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:
                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

R_JAN2022            TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:
                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

R_FEB2022            TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:
                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

R_APR2022            TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:
                     MI:SS', 'NLS_CALENDAR=GREGORIAN')

SQL>    CREATE table RP_TEMP as SELECT * from RP WHERE 1=2;

Table created.

SQL> ALTER table RP exchange partition R_JAN2022 with table RP_TEMP without validation;

Table altered.

SQL> ALTER table RP exchange partition R_FEB2022 with table RP_TEMP without validation;

Table altered.

SQL>  ALTER table RP exchange partition R_MAR2022 with table RP_TEMP without validation;

Table altered.

SQL> ALTER table RP exchange partition R_APR2022 with table RP_TEMP without validation;

Table altered.

SQL> ALTER table RP exchange partition R_MAY2022 with table RP_TEMP without validation;

Table altered.

kishan

May, Mon, 2024

Uncategorized

SQL Query To Find Statistics Of SQL Query

set lines 200 pages 1000
col sql_id for a13
col waitsec for 999999999
col status for a7
col command for a10
SELECT s.sid,
       s.serial#,
       s.sql_id,
       st.command_name command,
       s.seconds_in_wait waitsec,
       s.status,
       ses.value cpu FROM v$session s
INNER JOIN v$sesstat ses on (s.sid = ses.sid) 
INNER JOIN v$statname stn on (ses.statistic# = stn.statistic#)
INNER JOIN v$sqlcommand st on (s.command = st.command_type)
WHERE stn.name='CPU used by this session'
AND sql_id is not null;

kishan

May, Mon, 2024

Uncategorized

Alternative Ways To Get Row Count In Oracle

It is sometimes time consuming for a DBA to know the count of records in a table because of table’s big size and it may also affect production performance during peak hours to just fetch the count.

Universally, every one uses count(*), count(1) or count(column_name) to get the total record count of the table. But this goes through a full table scan which in case, if there are millions of rows will take decades to return the results provided the database has high undo space to hold the read consistent images.

SQL> CREATE table dbsrc as select * from dba_source;
CREATE table dbsrc as select * from dba_source
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> SELECT count(*) from dbsrc;

  COUNT(*)
----------
    582106

SQL> SELECT count(1) from dbsrc;

  COUNT(1)
----------
    582106

SQL> SELECT count(NAME) from dbsrc;

COUNT(NAME)
-----------
     582106

Another easy alternative is to use ‘num_rows’ column from dba_tables which gives the same result if the statistics are up to date. This method is not going to scan the table but get the already stored information and print it.

SQL> SELECT num_rows from dba_tables where table_name='DBSRC';

  NUM_ROWS
----------


SQL> EXEC dbms_stats.gather_table_stats('SYS','DBSRC');

PL/SQL procedure successfully completed.

SQL> SELECT num_rows from dba_tables where table_name='DBSRC';

  NUM_ROWS
----------
    582106

Another effective but false method is to use explain plan. This gives the idea of estimated row count of the table but not always legit because it is an estimation.

SQL> SELECT * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 107326479

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |  4697   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |
|   2 |   TABLE ACCESS FULL| DBSRC |   582K|  4697   (1)| 00:00:01 |
--------------------------------------------------------------------

9 rows selected.

Another way is to get the sql_id and get the row count from cursor when the query actually is executing.

SQL> SELECT sql_id,sql_text from v$sql where sql_text like '%SELECT count(*) from dbsrc%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
7k1xrmrb2w4rg
SELECT sql_id,sql_text from v$sql where sql_text like '%SELECT count(*) from dbs
rc%'

bc6s75u2cvp48
SELECT count(*) from dbsrc

Get the real time execution plan and check the actual number of rows.

SQL> SELECT * from table(dbms_xplan.display_cursor('bc6s75u2cvp48',null,'ADVANCED'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  bc6s75u2cvp48, child number 0
-------------------------------------
SELECT count(*) from dbsrc

Plan hash value: 107326479

--------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |  4697 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL| DBSRC |   582K|  4697   (1)| 00:00:01 |
--------------------------------------------------------------------

kishan

May, Sat, 2024

Uncategorized

Session In Killed Status Stays Forever In Oracle

select sid,serial#,username,command,event,module,status,sql_id,type,last_call_et from v$session where username='SYS';

       SID    SERIAL# USERNAME      COMMAND EVENT                MODULE               STATUS   SQL_ID        TYPE       LAST_CALL_ET
---------- ---------- ---------- ---------- -------------------- -------------------- -------- ------------- ---------- ------------
      4693      57328 SYS                 0 OFS idle                                  ACTIVE                 BACKGROUND      9820860
     21554      47593 SYS                 0 db file sequential r sqlplus@host KILLED                 USER               2909
                                            ead                   (TNS V1-V3)

select p.spid from v$process p ,v$session s where p.addr = s.paddr and s.sid=21554 and s.serial#=47593;

SPID
------------------------
3301292

ps -ef|grep 3301292
oracle   3301292       1  3 02:47 ?        00:01:53 oraclehost

kill -9 3301292

ps -ef|grep 3301292

kishan

Mar, Fri, 2024

Uncategorized

Stress Test Your System’s Potential In Linux

Always in linux, update the epel repository to the system current release to get the stress package.

[root@dbperf oracle]# yum install epel-release
Last metadata expiration check: 1:33:18 ago on Mon 25 Mar 2024 07:22:22 PM IST.
Dependencies resolved.
=====================================================================================================================
 Package                           Architecture     Version                        Repository                   Size
=====================================================================================================================
Installing:
 oracle-epel-release-el8           x86_64           1.0-5.el8                      ol8_baseos_latest            15 k
Installing dependencies:
 yum-utils                         noarch           4.0.21-14.1.0.1.el8            ol8_baseos_latest            74 k

Transaction Summary
=====================================================================================================================
Install  2 Packages

Total download size: 90 k
Installed size: 41 k
Is this ok [y/N]: y
Downloading Packages:
(1/2): oracle-epel-release-el8-1.0-5.el8.x86_64.rpm                                   43 kB/s |  15 kB     00:00
(2/2): yum-utils-4.0.21-14.1.0.1.el8.noarch.rpm                                      164 kB/s |  74 kB     00:00
---------------------------------------------------------------------------------------------------------------------
Total                                                                                196 kB/s |  90 kB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                             1/1
  Installing       : yum-utils-4.0.21-14.1.0.1.el8.noarch                                                        1/2
  Installing       : oracle-epel-release-el8-1.0-5.el8.x86_64                                                    2/2
  Running scriptlet: oracle-epel-release-el8-1.0-5.el8.x86_64                                                    2/2
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored

  Verifying        : oracle-epel-release-el8-1.0-5.el8.x86_64                                                    1/2
  Verifying        : yum-utils-4.0.21-14.1.0.1.el8.noarch                                                        2/2

Installed:
  oracle-epel-release-el8-1.0-5.el8.x86_64                    yum-utils-4.0.21-14.1.0.1.el8.noarch

Complete!

Install stress stresslessly using yum.

[root@dbperf oracle]# yum install stress
Oracle Linux 8 EPEL Packages for Development (x86_64)                                7.6 MB/s |  64 MB     00:08
Oracle Linux 8 EPEL Modular Packages for Development (x86_64)                        568 kB/s | 322 kB     00:00
Last metadata expiration check: 0:00:01 ago on Mon 25 Mar 2024 08:56:09 PM IST.
Dependencies resolved.
=====================================================================================================================
 Package                Architecture           Version                      Repository                          Size
=====================================================================================================================
Installing:
 stress                 x86_64                 1.0.4-24.el8                 ol8_developer_EPEL                  40 k

Transaction Summary
=====================================================================================================================
Install  1 Package

Total download size: 40 k
Installed size: 79 k
Is this ok [y/N]: y
Downloading Packages:
stress-1.0.4-24.el8.x86_64.rpm                                                       9.4 kB/s |  40 kB     00:04
---------------------------------------------------------------------------------------------------------------------
Total                                                                                9.4 kB/s |  40 kB     00:04
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                             1/1
  Installing       : stress-1.0.4-24.el8.x86_64                                                                  1/1
  Running scriptlet: stress-1.0.4-24.el8.x86_64                                                                  1/1
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2011.1.2.el8uek.x86_64.conf:6: hwcap directive ignored

  Verifying        : stress-1.0.4-24.el8.x86_64                                                                  1/1

Installed:
  stress-1.0.4-24.el8.x86_64

Complete!

[oracle@dbperf ~]$ lscpu|grep -E 'CPU\(s\)|core'
CPU(s):              1
On-line CPU(s) list: 0
Thread(s) per core:  1
NUMA node0 CPU(s):   0

[root@dbperf oracle]# stress --cpu 32
stress: info: [65430] dispatching hogs: 32 cpu, 0 io, 0 vm, 0 hdd

top - 21:03:11 up  6:20,  2 users,  load average: 30.30, 14.08, 5.62
Tasks: 303 total,  33 running, 256 sleeping,  13 stopped,   1 zombie
%Cpu(s): 98.3 us,  0.0 sy,  0.0 ni,  0.0 id,  0.0 wa,  1.7 hi,  0.0 si,  0.0 st
MiB Mem :   9671.1 total,   2361.5 free,   1215.7 used,   6093.9 buff/cache
MiB Swap:   5024.0 total,   5024.0 free,      0.0 used.   5421.4 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  65438 root      20   0    7984    100      0 R   3.3   0.0   0:03.76 stress
  65458 root      20   0    7984    100      0 R   3.3   0.0   0:03.76 stress
  65460 root      20   0    7984    100      0 R   3.3   0.0   0:03.76 stress
  65431 root      20   0    7984    100      0 R   3.0   0.0   0:03.76 stress
  65432 root      20   0    7984    100      0 R   3.0   0.0   0:03.76 stress
  65433 root      20   0    7984    100      0 R   3.0   0.0   0:03.76 stress
  65434 root      20   0    7984    100      0 R   3.0   0.0   0:03.76 stress
  65435 root      20   0    7984    100      0 R   3.0   0.0   0:03.76 stress
  65436 root      20   0    7984    100      0 R   3.0   0.0   0:03.76 stress
  65437 root      20   0    7984    100      0 R   3.0   0.0   0:03.76 stress
  65439 root      20   0    7984    100      0 R   3.0   0.0   0:03.76 stress

kishan

Mar, Mon, 2024

Uncategorized

How To Select Rows Between Two Numerical Values In Oracle

If there is a requirement to select the data between certain rownumbers or due to memory constraints if selecting million rows from a table, we can split the output into multiple parts with rownum to make sure there is no overflow of memory.

Using rownum

SQL> col name for a20
SQL> set lines 200 pages 1000
SQL> SELECT * from (SELECT rownum rn,name,type from dba_source WHERE owner='SYS') WHERE rownum between 1 and 100;

        RN NAME                 TYPE
---------- -------------------- ------------
         1 ADR_HOME_T           TYPE
         2 ADR_HOME_T           TYPE
         3 ADR_HOME_T           TYPE
         4 ADR_HOME_T           TYPE
         5 ADR_HOME_T           TYPE
         6 ADR_HOME_T           TYPE
         7 ADR_HOME_T           TYPE
         8 ADR_HOME_T           TYPE
         9 ADR_HOME_T           TYPE
        10 ADR_HOME_T           TYPE
        11 ADR_HOME_T           TYPE
        12 ADR_HOME_T           TYPE

2a) Using Offset

SQL> SELECT rownum rn,name,type from dba_source WHERE owner='SYS' offset 187090 rows;

        RN NAME                 TYPE
---------- -------------------- ------------
    187091 schedFileWatcherJava JAVA SOURCE
    187092 schedFileWatcherJava JAVA SOURCE
    187093 schedFileWatcherJava JAVA SOURCE
    187094 schedFileWatcherJava JAVA SOURCE
    187095 schedFileWatcherJava JAVA SOURCE
    187096 schedFileWatcherJava JAVA SOURCE
    187097 schedFileWatcherJava JAVA SOURCE
    187098 schedFileWatcherJava JAVA SOURCE
    187099 schedFileWatcherJava JAVA SOURCE
    187100 schedFileWatcherJava JAVA SOURCE
    187101 schedFileWatcherJava JAVA SOURCE
    187102 schedFileWatcherJava JAVA SOURCE
    187103 schedFileWatcherJava JAVA SOURCE
    187104 schedFileWatcherJava JAVA SOURCE
    187105 schedFileWatcherJava JAVA SOURCE
    187106 schedFileWatcherJava JAVA SOURCE
    187107 schedFileWatcherJava JAVA SOURCE
    187108 schedFileWatcherJava JAVA SOURCE
    187109 schedFileWatcherJava JAVA SOURCE
    187110 schedFileWatcherJava JAVA SOURCE
    187111 schedFileWatcherJava JAVA SOURCE

21 rows selected.

2b) Offset + Fetch clause

SQL> SELECT rownum rn,name,type from dba_source WHERE owner='SYS' offset 187090 rows fetch next 10 rows only;

        RN NAME                 TYPE
---------- -------------------- ------------
    187091 schedFileWatcherJava JAVA SOURCE
    187092 schedFileWatcherJava JAVA SOURCE
    187093 schedFileWatcherJava JAVA SOURCE
    187094 schedFileWatcherJava JAVA SOURCE
    187095 schedFileWatcherJava JAVA SOURCE
    187096 schedFileWatcherJava JAVA SOURCE
    187097 schedFileWatcherJava JAVA SOURCE
    187098 schedFileWatcherJava JAVA SOURCE
    187099 schedFileWatcherJava JAVA SOURCE
    187100 schedFileWatcherJava JAVA SOURCE

10 rows selected.

kishan

Mar, Mon, 2024

Uncategorized

ddl_lock_timeout For Concurrency Of DDL Over DML Oracle

Executing a DDL statement during peak hour of the database queues the sessions until the DML gets completed. So in order to wait in the queue without nowait clause, a parameter ddl_lock_timeout initiates a exclusive DDL lock request on the object until the DML commit the transaction.

SQL> CREATE table dbsrc as select * from dba_source;

Table created.

SQL> INSERT into dbsrc select * from dba_source;

291053 rows created.

SQL> !date
Fri Mar 22 21:03:35 IST 2024

SQL> !date
Fri Mar 22 21:03:37 IST 2024

SQL> !date
Fri Mar 22 21:03:40 IST 2024

SQL> !date
Fri Mar 22 21:03:42 IST 2024

SQL> !date
Fri Mar 22 21:03:47 IST 2024

If it is normal DDL without nowait, then the DDL errors out with ora-00054. When we set ddl_lock_timeout to 60 seconds, the DDL session waits for the insert to commit the transaction reserving the object before any other DML statement clutch the lock. Always make sure that the parameter is set at the session level.

SQL> ALTER table dbsrc move;
ALTER table dbsrc move
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> ALTER table dbsrc move;
ALTER table dbsrc move
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> ALTER session set ddl_lock_timeout=60;

Session altered.

SQL> set time on
21:04:59 SQL> ALTER table dbsrc move;
ALTER table dbsrc move
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


21:06:02 SQL>

After the transaction is commited, the DDL immediately started to execute.

SQL> commit;

Commit complete.


21:06:02 SQL> ALTER table dbsrc move;

Table altered.

21:07:50 SQL>

kishan

Mar, Fri, 2024

Uncategorized

SQL Query To Find Any SQL Command Type Oracle

To Find all the sql command type executing on the database.

Any query can be monitored with this query which is universal to all the type of commands.

SQL> col COMMAND_NAME for a20
SQL> set lines 200 pages 1000
SELECT s.sid,
       s.serial#,
       s.sql_id,
       st.command_name,
       s.seconds_in_wait,
       s.status FROM v$session s 
INNER JOIN v$sqlcommand st on (s.command = st.command_type)
AND sql_id is not null;

       SID    SERIAL# SQL_ID        COMMAND_NAME         SECONDS_IN_WAIT STATUS
---------- ---------- ------------- -------------------- --------------- --------
       237      39068 1h50ks4ncswfn ALTER DATABASE                   517 INACTIVE
       288      45281 fkcjjjfr5v7ub SELECT                             0 ACTIVE

For example, to monitor ‘index rebuild’ the below predicate can be used.

SQL> SELECT sql_id,seconds_in_wait,sid,serial# from v$session where command=9;

no rows selected

kishan

Mar, Mon, 2024