Database Technology

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;

Friday, June 7, 2024

script to find out user part of which group in AWS Redshift

script to find out user part of which group in AWS Redshift:

you just need to feed the username and script will provide associated group name

SELECT

pg_group.groname

,pg_group.grosysid

,pg_user.*

FROM pg_group, pg_user

WHERE pg_user.usesysid = ANY(pg_group.grolist)

AND pg_user.usename='<user_name>'

ORDER BY 1,2 ;

script to find what are the users associated with group in AWS Redshift

script to find what are the users associated with group in AWS Redshift:

select usename

from pg_user , pg_group

where pg_user.usesysid = ANY(pg_group.grolist) and

pg_group.groname='<group_name>';

Monday, May 20, 2024

How to disable automatic restart of the cluster services in oracle RAC

login as root user in linux server and check the status of cluster resources

1)# ./crsctl status resource -t

stop all the services

2)[root@<server_name> bin]# ./crsctl stop cluster -all

3)disable the automatic restart of the cluster

[root@<server_name> bin]# ./crsctl disable has

***************************************************

[root@<server_name> bin]# ./crsctl status resource -t

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

Name Target State Server State details

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

Local Resources

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

ora.DATA.SHARED.advm

ONLINE ONLINE <server_name> Volume device /dev/a

sm/shared-500 is onl

ine,STABLE

ora.DATA.dg

ONLINE ONLINE <server_name> STABLE

ora.FRA.dg

ONLINE ONLINE <server_name> STABLE

ora.LISTENER.lsnr

ONLINE ONLINE <server_name> STABLE

ora.asm

ONLINE ONLINE <server_name> Started,STABLE

ora.data.shared.acfs

ONLINE ONLINE <server_name> mounted on /shared,S

TABLE

ora.net1.network

ONLINE ONLINE <server_name> STABLE

ora.ons

ONLINE ONLINE <server_name> STABLE

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

Cluster Resources

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

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE <server_name> STABLE

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE <server_name> STABLE

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE <server_name> STABLE

ora.cdb1.db

1 OFFLINE OFFLINE Instance Shutdown,ST

ABLE

ora.<server_name>.vip

1 ONLINE ONLINE <server_name> STABLE

ora.collabn2.vip

1 ONLINE INTERMEDIATE <server_name> FAILED OVER,STABLE

ora.cvu

1 ONLINE ONLINE <server_name> STABLE

ora.oc4j

1 OFFLINE OFFLINE STABLE

ora.rac.db

1 OFFLINE OFFLINE Instance Shutdown,ST

ABLE

2 OFFLINE OFFLINE STABLE

ora.scan1.vip

1 ONLINE ONLINE <server_name> STABLE

ora.scan2.vip

1 ONLINE ONLINE <server_name> STABLE

ora.scan3.vip

1 ONLINE ONLINE <server_name> STABLE

ora.target.db

1 OFFLINE OFFLINE STABLE

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

[root@<server_name> bin]# ./crsctl stop cluster -all

CRS-2673: Attempting to stop 'ora.crsd' on '<server_name>'

CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on '<server_name>'

CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on '<server_name>'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on '<server_name>'

CRS-2673: Attempting to stop 'ora.FRA.dg' on '<server_name>'

CRS-2673: Attempting to stop 'ora.data.shared.acfs' on '<server_name>'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on '<server_name>'

CRS-2673: Attempting to stop 'ora.<server_name>.vip' on '<server_name>'

CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on '<server_name>'

CRS-2673: Attempting to stop 'ora.cvu' on '<server_name>'

CRS-2677: Stop of 'ora.LISTENER.lsnr' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.scan3.vip' on '<server_name>'

CRS-2673: Attempting to stop 'ora.scan2.vip' on '<server_name>'

CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.scan1.vip' on '<server_name>'

CRS-2677: Stop of 'ora.cvu' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.<server_name>.vip' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.collabn2.vip' on '<server_name>'

CRS-2677: Stop of 'ora.data.shared.acfs' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.DATA.SHARED.advm' on '<server_name>'

CRS-2677: Stop of 'ora.DATA.SHARED.advm' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.DATA.dg' on '<server_name>'

CRS-2677: Stop of 'ora.FRA.dg' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.scan1.vip' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.scan2.vip' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.scan3.vip' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.collabn2.vip' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.DATA.dg' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.asm' on '<server_name>'

CRS-2677: Stop of 'ora.asm' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.ons' on '<server_name>'

CRS-2677: Stop of 'ora.ons' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.net1.network' on '<server_name>'

CRS-2677: Stop of 'ora.net1.network' on '<server_name>' succeeded

CRS-2792: Shutdown of Cluster Ready Services-managed resources on '<server_name>' has completed

CRS-2677: Stop of 'ora.crsd' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.ctssd' on '<server_name>'

CRS-2673: Attempting to stop 'ora.evmd' on '<server_name>'

CRS-2673: Attempting to stop 'ora.storage' on '<server_name>'

CRS-2677: Stop of 'ora.storage' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.asm' on '<server_name>'

CRS-2677: Stop of 'ora.evmd' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.ctssd' on '<server_name>' succeeded

CRS-2677: Stop of 'ora.asm' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on '<server_name>'

CRS-2677: Stop of 'ora.cluster_interconnect.haip' on '<server_name>' succeeded

CRS-2673: Attempting to stop 'ora.cssd' on '<server_name>'

CRS-2677: Stop of 'ora.cssd' on '<server_name>' succeeded

[root@<server_name> bin]#

[root@<server_name> bin]# ./crsctl disable has

CRS-4621: Oracle High Availability Services autostart is disabled.

ORA-29702: error occurred in Cluster Group Service operation in oracle database

Issue:

when I started my oracle database got the below error.

ORA-29702: error occurred in Cluster Group Service operation

SQL> startup mount;

ORA-29702: error occurred in Cluster Group Service operation

Solution:

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk rac_off ioracle

make -f ins_rdbms.mk ioracle

[oracle@<server_name> ~]$ cd $ORACLE_HOME/rdbms/lib

[oracle@<server_name> lib]$ make -f ins_rdbms.mk rac_off ioracle

rm -f /u01/app/oracle/product/12.1.0/dbhome_1/lib/libskgxp12.so

cp /u01/app/oracle/product/12.1.0/dbhome_1/lib//libskgxpg.so /u01/app/oracle/product/12.1.0/dbhome_1/lib/libskgxp12.so

rm -f /u01/app/oracle/product/12.1.0/dbhome_1/lib/libskgxn2.so

cp /u01/app/oracle/product/12.1.0/dbhome_1/lib//libskgxns.so \

/u01/app/oracle/product/12.1.0/dbhome_1/lib/libskgxn2.so

/usr/bin/ar d /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a kcsm.o

/usr/bin/ar cr /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ksnkcs.o

chmod 755 /u01/app/oracle/product/12.1.0/dbhome_1/bin

- Linking Oracle

rm -f /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle

/u01/app/oracle/product/12.1.0/dbhome_1/bin/orald -o /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ -L/u01/app/oracle/product/12.1.0/dbhome_1/lib/ -L/u01/app/oracle/product/12.1.0/dbhome_1/lib/stubs/ -Wl,-E /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/opimai.o /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /u01/app/oracle/product/12.1.0/dbhome_1/lib/nautab.o /u01/app/oracle/product/12.1.0/dbhome_1/lib/naeet.o /u01/app/oracle/product/12.1.0/dbhome_1/lib/naect.o /u01/app/oracle/product/12.1.0/dbhome_1/lib/naedhs.o /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/config.o -lserver12 -lodm12 -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12 -lrt -lplp12 -lserver12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 `if [ -f /u01/app/oracle/product/12.1.0/dbhome_1/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f /u01/app/oracle/product/12.1.0/dbhome_1/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12 -lrt -lplp12 -ljavavm12 -lserver12 -lwwg `cat /u01/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /u01/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnro12 `cat /u01/app/oracle/product/12.1.0/dbhome_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lnnzst12 -lzt12 -lztkg12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12"; fi` -L/u01/app/oracle/product/12.1.0/dbhome_1/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -loraz -llzopro -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12 -laio -lons `cat /u01/app/oracle/product/12.1.0/dbhome_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12.1.0/dbhome_1/lib -lm `cat /u01/app/oracle/product/12.1.0/dbhome_1/lib/sysliblist` -ldl -lm -L/u01/app/oracle/product/12.1.0/dbhome_1/lib

test ! -f /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle ||\

mv -f /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracleO

mv /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle

chmod 6751 /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle

[oracle@<server_name> lib]$ make -f ins_rdbms.mk ioracle

chmod 755 /u01/app/oracle/product/12.1.0/dbhome_1/bin

- Linking Oracle

rm -f /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle

test ! -f /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle ||\

mv -f /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracleO

mv /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/lib/oracle /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle

chmod 6751 /u01/app/oracle/product/12.1.0/dbhome_1/bin/oracle

[oracle@<server_name> lib]$

[oracle@<server_name> lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 20 22:24:34 2024

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1369579520 bytes

Fixed Size 2288200 bytes

Variable Size 436209080 bytes

Database Buffers 922746880 bytes

Redo Buffers 8335360 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

SOURCE READ WRITE

Wednesday, April 17, 2024

Pl/Sql script to generate n number of datafile in oracle database.

we have scenario ,used to add 100 plus datafiles to a tablespace and we dont need to create it manually,I have created pl/sql script to generate n number of datafiles for this table space.

This is really helped us.

DECLARE

v_tablespace_name VARCHAR2(30) := 'USER'; -- Specify your tablespace name

v_datafile_prefix VARCHAR2(50) := '/path/to/datafile'; -- Specify the base path for datafiles

v_datafile_size_mb NUMBER := 100; -- Specify the size of each datafile in MB

v_num_files NUMBER := 100; -- Specify the number of datafiles to add

v_sql VARCHAR2(1000);

BEGIN

FOR i IN 1..v_num_files LOOP

-- Generate SQL to add datafile

v_sql := 'ALTER TABLESPACE ' || v_tablespace_name ||

' ADD DATAFILE ''' || v_datafile_prefix || i || '.dbf''' ||

' SIZE ' || v_datafile_size_mb || 'M AUTOEXTEND OFF NEXT 100M MAXSIZE UNLIMITED;';

-- Output SQL statement

DBMS_OUTPUT.PUT_LINE(v_sql);

END LOOP;

END;