Database Technology

Thursday, July 17, 2025

SAP Oracle Database table migration to new tablespace using brtools

issue:

we have requirement to move the table from tablespace A1 to B1 using brtools.

solution:

The below script and steps will take care the table movement to new tablespace without confirmation from the user --brspace -u / -c force,it will force to continue.

brspace -u / -c force -f tbreorg -s PSAPSR3 -o SAPSR3 -t /BIC/AZD_PUR8200 -n PSAPSR3TEST -p 4

-f tbreorg ----> function name

-s PSAPSR3 ----> source schema tablespace name

-o SAPSR3 ----> source schema tablespace name

-t /BIC/AZD_PUR8200 ----> tables name that needs to be migrated

-n PSAPSR3TEST ----> new tablespace name

-p 4 ---->parallel option

<server_name>:orab1p 67> brspace -u / -c force -f tbreorg -s PSAPSR3 -o SAPSR3 -t /BIC/AZD_PUR8200 -n PSAPSR3TEST -p 4

BR1001I BRSPACE 7.40 (47)

BR1002I Start of BRSPACE processing: sfrnodks.tbr 2025-07-17 11:22:10

BR0484I BRSPACE log file: /oracle/B1P/sapreorg/sfrnodks.tbr

BR0280I BRSPACE time stamp: 2025-07-17 11:22:11

BR1009I Name of database instance: B1P

BR1010I BRSPACE action ID: sfrnodks

BR1011I BRSPACE function ID: tbr

BR1012I BRSPACE function: tbreorg

BR0134I Unattended mode with 'force' active - no operator confirmation allowed

BR0280I BRSPACE time stamp: 2025-07-17 11:22:13

BR0813I Schema owners found in database B1P: SAPSR3*

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR0657I Input menu 353 # please enter/check input values

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

Options for reorganization of tables: SAPSR3./BIC/AZD_PUR8200 (degree 1)

1 * Reorganization action (action) ............ [reorg]

2 - Reorganization mode (mode) ................ [online]

3 - Create DDL statements (ddl) ............... [yes]

4 ~ New destination tablespace (newts) ........ [PSAPSR3TEST]

5 ~ Separate index tablespace (indts) ......... []

6 - Parallel threads (parallel) ............... [4]

7 ~ Table/index parallel degree (degree) ...... []

8 ~ Category of initial extent size (initial) . []

9 ~ Sort by fields of index (sortind) ......... []

10 # Index for IOT conversion (iotind) ......... [FIRST]

11 - Compression action (compress) ............. [none]

12 # LOB compression degree (lobcompr) ......... [medium]

Standard keys: c - cont, b - back, s - stop, r - refr

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

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR0134I Unattended mode with 'force' active - continuing processing with default reply 'cont'

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR1108I Checking tables for reorganization...

BR0280I BRSPACE time stamp: 2025-07-17 11:22:14

BR1112I Number of tables selected/skipped for reorganization: 1/0

BR0370I Directory /oracle/B1P/sapreorg/sfrnodks created

BR0280I BRSPACE time stamp: 2025-07-17 11:22:15

BR1101I Starting 'online' table reorganization...

BR0280I BRSPACE time stamp: 2025-07-17 11:22:15

BR1124I Starting 'online' reorganization of table SAPSR3./BIC/AZD_PUR8200 ...

BR0280I BRSPACE time stamp: 2025-07-17 11:24:36

BR1105I Table SAPSR3./BIC/AZD_PUR8200 reorganized successfully

BR0280I BRSPACE time stamp: 2025-07-17 11:24:36

BR1141I 1 of 1 table processed - 23691300 of 23691300 rows done

BR0204I Percentage done: 100.00%, estimated end time: 11:24

BR0001I **************************************************

BR0280I BRSPACE time stamp: 2025-07-17 11:24:36

BR1102I Number of tables reorganized successfully: 1

BR0280I BRSPACE time stamp: 2025-07-17 11:24:37

BR1022I Number of tables processed: 1

BR1003I BRSPACE function 'tbreorg' completed

BR1008I End of BRSPACE processing: sfrnodks.tbr 2025-07-17 11:24:37

BR0280I BRSPACE time stamp: 2025-07-17 11:24:38

BR1005I BRSPACE completed successfully

Tuesday, July 15, 2025

script to list the oracle database ORA- errors from oracle Database views

script to list the oracle database ORA- errors

COLUMN ORIGINATING_TIMESTAMP FORMAT A40

COLUMN message_text FORMAT A100

set linesize 300

set pages 300

SELECT ORIGINATING_TIMESTAMP , message_text

FROM X$DBGALERTEXT

WHERE originating_timestamp > (SYSDATE - 5)

AND message_text LIKE '%ORA-%'

ORDER BY originating_timestamp;

ORIGINATING_TIMESTAMP MESSAGE_TEXT

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

14-JUL-25 11.25.14.115 AM +01:00 ORA-1100 signalled during: alter database mount...

15-JUL-25 01.52.59.280 PM +01:00 Patch Description: ORA-00800 SOFT EXTERNAL ERROR, ARGUMENTS [SET PRIORITY FAILED], [VKTM] , DISM(1

Script to find out user query running which priority in AWS Redshift Database

Script to find out user query running which priority in AWS Redshift Database:

SELECT w.query, i.userid, w.service_class, w.state,w.query_priority

FROM stv_wlm_query_state w

JOIN stv_inflight i ON w.query = i.query

WHERE w.state = 'Running' and i.userid in ('219','384','105');

Redshift_TEST_DB=# SELECT w.query, i.userid, w.service_class, w.state,w.query_priority

Redshift_TEST_DB-# FROM stv_wlm_query_state w

Redshift_TEST_DB-# JOIN stv_inflight i ON w.query = i.query

Redshift_TEST_DB-# WHERE w.state = 'Running' and i.userid in ('219','384','105');

query | userid | service_class | state | query_priority

-----------+--------+---------------+------------------+----------------------

129306353 | 105 | 100 | Running | High

129302344 | 105 | 100 | Running | High

(2 rows)

script to find out assumerole granted to user in AWS redshift Database.

script to find out assumerole granted to user in AWS redshift.

select username, iam_role, cmd FROM pg_get_iam_role_by_user('TESTUSER@abc.domain.com') res_iam_role(username text, iam_role text, cmd text);

ORA-800 [Set Priority Failed] [VKTM] [Check traces and OS configuration] [Check Oracle 19.24.0.0

Issue:

we had ORA-800 popping up after the DB creation and we tried different approaches ,but the error is not going away on oracle database 19.24.0.0.

Error:

ORA-800 [Set Priority Failed] [VKTM] [Check traces and OS configuration] [Check Oracle document and MOS notes] [] [] [] [] [] [] [] []

[00]: dbgexExplicitEndInc [diag_dde]

[01]: dbgeEndDDEInvocationImpl [diag_dde]

[02]: ksbsethighpri_int [background_proc]<-- Signaling

[03]: ksbrdp [background_proc]

[04]: opirip [OPI]

solution :

we had ORA-800 popping up after the DB creation and we tried different approaches ,but the error is not going away on oracle database 19.24.0.0.Then we applied the below Patch and issue resolved.

p34672698_1924000DBRU_Linux-x86-64.zip

no more ORA-800 on the alert log.

Step -by Step Applying the Patch:

**********************bug patch apply on standalone server************************

(I) Prerequisites

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

Before you install or deinstall the patch, ensure that you meet the following requirements:

1. Ensure that the Oracle home on which you are installing the patch or from which you are rolling back the patch is Oracle Database 19 Release 19.24.0.0.240716DBRU.

2. Ensure that 19 Release 19.24.0.0.240716DBRU Patch Set Update (PSU) 36582781 is already applied on the Oracle Database.

3. Oracle recommends you to use the latest version available for 19 Release 19.24.0.0.240716DBRU. If you do not have OPatch 19 Release 19.24.0.0.240716DBRU, then download it from patch# 6880880 for 19.24.0.0.240716DBRU release.

4. Ensure environment variable ORACLE_HOME is set correctly.

5. Ensure that the $PATH definition has the following executables: make, ar, ld and nm. The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin.

6. Before beginning patch application, check the consistency of inventory information for GI home and each database home to be patched. Run the following command as respective Oracle home owner to check the consistency.

% $ORACLE_HOME/OPatch/opatch lsinventory -detail -oh $ORACLE_HOME

Note:

- If this command succeeds, it lists the Oracle components that are installed in the home.

- Save the output so you have the status prior to the patch apply.

- If this command fails, contact Oracle Support Services for assistance.

7. (Only for Installation) Maintain a location for storing the contents of the patch ZIP file. In the rest of the document, this location (absolute path) is referred to as <PATCH_TOP_DIR>. Extract the contents of the patch ZIP file to the location (PATCH_TOP_DIR) you have created above. To do so, run the following command:

$ unzip -d <PATCH_TOP_DIR> p34672698_1924000DBRU_Linux-x86-64.zip

8. (Only for Installation) Determine whether any currently installed interim patches conflict with this patch 34672698 as shown below:

$ cd <PATCH_TOP_DIR>/34672698

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./

The report will indicate the patches that conflict with this patch and the patches for which the current 34672698 is a superset.

Note:

Upon execution of the Opatch command, it validates the patch and ensures that there are no conflicts with the software already installed in the ORACLE_HOME. OPatch categorizes conflicts into the following types:

- Conflicts with a patch already applied to the ORACLE_HOME that is a subset of the patch you are trying to apply - In this case, continue with the patch installation because the new patch contains all the fixes from the existing patch in the ORACLE_HOME. The subset patch will automatically be rolled back prior to the installation of the new patch.

- Conflicts with a patch already applied to the ORACLE_HOME - In this case, stop the patch installation and contact Oracle Support Services.

(II) Installation

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

To install the patch, follow these steps:

As root user, change oradism permissions

Pre-install //these make the existing oradism owned by grid home owner and replaceable

a.ls -ld $GI_HOME/bin/oradism

i.-rwsr-x--- 1 root dba <GI_HOME>/bin/oradism

2. Apply Patch to DB home(s) as DB home owner :

Set your current directory to the directory where the patch is located

and then run the OPatch utility by entering the following commands:

As root user, change oradism permissions:

$ cd <PATCH_TOP_DIR>/34672698

Pre-install //these make the existing oradism owned by "oracle" user and replaceable

a.ls -ld $ORACLE_HOME/bin/oradism

i.-rwsr-x--- 1 root dba <ORACLE_HOME>/bin/oradism

b.change ownership to the DB home owner (i.e oracle)

i.chown oracle $ORACLE_HOME/bin/oradism

ii.chmod 0750 $ORACLE_HOME/bin/oradism

c.ls -ld $ORACLE_HOME/bin/oradism

As DB home owner, apply the patch:

make sure NO DB & listener process running

---> /oracle/TESTDB78/19.0.0/OPatch/opatch apply -oh /oracle/TESTDB78/19.0.0 -local /oracle/TESTDB78/19.0.0/34672698

<ORACLE_HOME>/OPatch/opatch apply -oh <ORACLE_HOME> -local <PATCH_TOP_DIR>/34672698

3. Verify whether the patch has been successfully installed by running the following command:

As the Oracle Database home owner, run the following command:

$ opatch lsinventory -oh <ORACLE_HOME>

3b. Post-install

These make the new oradism binary owned by root with setuid, and executable by "oracle" user

3b.2 - For DB home(s) as root user

a.ls -ld $ORACLE_HOME/bin/oradism

b.change owner to 'root' and enable setuid bit

i.chown root $ORACLE_HOME/bin/oradism

ii.chmod 4750 $ORACLE_HOME/bin/oradism

c.ls -ld $ORACLE_HOME/bin/oradism

----when I delete the patch ,it generate the error : when I apply the patch ,it stops the error

Starting background process VKTM

2025-07-03T11:25:14.278411+01:00

Errors in file /oracle/TESTDB78/diag/rdbms/TESTDB78/TESTDB78/trace/TESTDB78_vktm_276905.trc (incident=28045):

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], []

Incident details in: /oracle/TESTDB78/diag/rdbms/TESTDB78/TESTDB78/incident/incdir_28045/TESTDB78_vktm_276905_i28045.trc

2025-07-03T11:25:14.279793+01:00

----Stop the DB services

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

----pre-checks

<server_name>:/oracle/TESTDB78/19.0.0/34672698> /oracle/TESTDB78/19.0.0/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

Oracle Interim Patch Installer version 12.2.0.1.46

PREREQ session

Oracle Home : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

from : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version : 12.2.0.1.46

OUI version : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-15_13-28-23PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Version 19.24.0.0.0

[oracle@<server_name> 34672698]$ ls -ld $ORACLE_HOME/bin/oradism

-rwxr-x--- 1 oracle dba 147848 Jul 3 11:14 /oracle/TESTDB78/19.0.0/bin/oradism

[oracle@<server_name> 34672698]$ /oracle/TESTDB78/19.0.0/OPatch/opatch apply -oh /oracle/TESTDB78/19.0.0 -local /oracle/TESTDB78/19.0.0/34672698

Oracle Interim Patch Installer version 12.2.0.1.46

Oracle Home : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

from : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version : 12.2.0.1.46

OUI version : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-42-39AM_1.log

Verifying environment and performing prerequisite checks...

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

Start OOP by Prereq process.

Launch OOP...

Oracle Interim Patch Installer version 12.2.0.1.46

Oracle Home : /oracle/TESTDB78/19.0.0

Central Inventory : /oracle/TESTDB78/oraInventory_19240

from : /oracle/TESTDB78/19.0.0/oraInst.loc

OPatch version : 12.2.0.1.46

OUI version : 12.2.0.7.0

Log file location : /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-43-30AM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches: 34672698

Do you want to proceed? [y|n]

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/oracle/TESTDB78/19.0.0')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Applying interim patch '34672698' to OH '/oracle/TESTDB78/19.0.0'

Patching component oracle.rdbms.rsf, 19.0.0.0.0...

Patching component oracle.rdbms, 19.0.0.0.0...

Patch 34672698 successfully applied.

Log file location: /oracle/TESTDB78/19.0.0/cfgtoollogs/opatch/opatch2025-07-03_11-43-30AM_1.log

OPatch succeeded.

Thursday, March 27, 2025

script to check if the schema part of the data share or not in aws Redshift database

script to check if the schema part of the data share or not in aws Redshift database:

SELECT * FROM SVV_DATASHARE_OBJECTS where object_type='schema' and object_name='<schema_name>';

----------+-----------------+-----------+-----------+----------------+--------------------

script to check select access for entire schema in AWS Redshift database.

script to check select access for entire schema in AWS Redshift database.

SELECT

tablename,

usename

FROM

pg_catalog.pg_tables AS tables,

pg_catalog.pg_user AS users

WHERE

tables.schemaname = '<schema_name>'

AND users.usename = 'username@abc.com'

AND tables.schemaname not like 'pg_%'

AND NOT HAS_TABLE_PRIVILEGE(users.usename, tables.tablename, 'select');

script to check user has select,insert,update,delete access on specific schema in AWS Redshift Database

script to check user has select,insert,update,delete access on specific schema in AWS Redshift Database:

SELECT

tablename

,usename

,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'select') AS sel

,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'insert') AS ins

,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'update') AS upd

,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'delete') AS del

FROM

(SELECT * from FROM pg_catalog.pg_tables

WHERE schemaname = '<schema_name>' ) as tables

,(SELECT * FROM pg_catalog.pg_user where usename='username@abc.com') AS users;

script to check user has select,insert,update,delete access on specific table in schema in AWS Redshift Database

script to check user has select,insert,update,delete access on specific table in schema in AWS Redshift Database

SELECT

tablename

,usename

,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'select') AS sel

,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'insert') AS ins

,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'update') AS upd

,HAS_TABLE_PRIVILEGE(users.usename, tablename, 'delete') AS del

FROM

(SELECT * from FROM pg_catalog.pg_tables

WHERE schemaname = '<schema_name>' and tablename in ('dm_sales_rptg_ecomm_new')) as tables

,(SELECT * FROM pg_catalog.pg_user where usename='username@abc.com') AS users;

Tuesday, October 8, 2024

script to find out table part of the datashare in AWS redshift

script to find out table part of the datashare in AWS redshift:

SELECT share_type,

btrim(share_name)::varchar(16) AS share_name,

object_type,

object_name

FROM svv_datashare_objects

WHERE share_name='<share_name>'

AND object_name LIKE '%<table_name>%'

ORDER BY object_name;