Database Technology
48 FOLLOWERS
Sharing DBA experience to all. Blog topics cover Oracle, MS SQL, DB2, Netezza DBA, PostgreSQL, and AWS Redshift.
Database Technology
4M ago
issue:
ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], [] error reported on oracle database 64 bit 19.16.0.0
Root cause:
looks like oradism (file located under cd $ORACLE_HOME/bin) permission issue.
Solution:
1) check if the below parameter set to _highest_priority_processes=VKTM in DB level ,if not set
alter system set _highest_priority_processes=VKTM scope=spfile;
2) check cd $ORACLE_HOME/bin oradism and make sure it has Sticky bit set,if not set the sticky bit and reboot ..read more
Database Technology
8M ago
script to find what are the users having CREATE privilege's on AWS Redshift database:
SELECT u.usename AS username,
nsp.nspname AS schema_name,
has_schema_privilege(u.usename, nsp.nspname, 'CREATE') AS has_create_privilege
FROM pg_user u
CROSS JOIN pg_namespace nsp
WHERE nsp.nspname NOT LIKE 'pg_%' AND nsp.nspname not in ('information_schema','public') and u.usename not in('admin')
AND has_schema_privilege(u.usename, nsp.nspname, 'CREATE') = true
ORDER BY u.usename, nsp.nspname ..read more
Database Technology
1y ago
pre-request:
******************
get the cluster_name_space of source & Target cluster.
[source_cluster_name_space]==>source_DB
[target_cluster_name_space]==>Target_DB
source_cluster (Producer)
**********************
create datashare Data_share_copy_source_2_target publicaccessible=false; --create Datashare
alter datashare Data_share_copy_source_2_target add schema schema_name; --- add schema to migrate the tables
alter datashare Data_share_copy_source_2_target set includenew=true for schema schema_name;--add future object
alter datashare D ..read more
Database Technology
1y ago
Issue:
we have recently build new oracle database with version 19.16.0.0 and alert log reported the below 2 errors.
Errors in file /oracle/DB_name/diag/rdbms/DB_name/DB_name/trace/DB_name_vktm_60713.trc (incident=54058):
ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes], [] (Doc ID 2718971.1)
Incident details in: /oracle/DB_name/diag/rdbms/DB_name/DB_name/incident/incdir_54058/DB_name_vktm_60713_i54058.trc
2022-11-09T12:21:15.626799+00:00
Error attempting to elevate VKTM's prior ..read more
Database Technology
1y ago
script to copy data from S3 bucket to Redshift:
script to copy data from S3 bucket to Redshift using keys:
copy db_name.schema_name.tabl2 FROM 's3://s3_bucket/tabl2.csv'
credentials 'aws_access_key_id=123456789; aws_secret_access_key=987654321;token=12345678898' CSV IGNOREHEADER 1 REGION 'us-east-1';
script to copy data from S3 bucket to Redshift using ARN:
copy db_name.schema_name.table8 from 's3://s3_bucket/table8.csv
iam_role 'arn:aws:iam::123456789:role/S3_to_Redshift_copy_role'
CSV IGNOREHEADER ..read more
Database Technology
1y ago
user priority change:
The new priority to be assigned to all queries issued by user_name.
This argument must be a string with the value CRITICAL, HIGHEST, HIGH, NORMAL, LOW, LOWEST, or RESET.
Only superusers can change the priority to CRITICAL.
Changing the priority to RESET removes the priority setting for user_name.
select change_user_priority('<username>','<Priority>');
select change_user_priority('user@abc.com','HIGHEST');
session priorty change:
CHANGE_SESSION_PRIORITY enables superusers to immediately change the priority of any session in the sy ..read more
Database Technology
1y ago
setup DDL Group in Redshift database
1.create group <group_name>
2.load the DDL Privilege's to group
3.add the users to DDL group
create schema <schema_name>;
create group group_<schema>_DDL;
GRANT CREATE ON database <DB name> to group group_<schema>_DDL;
GRANT all ON schema <schema_name> to group group_<schema>_DDL ..read more
Database Technology
1y ago
setup Read-only and Read-write Group in Redshift database:
1.create group <group_name>
2.load the select /DML Privilege's to group
3.add the users to Read-only or Read-write group
Please find the syntax for Read-Only:
grant usage on schema <schema_name> to group Redshift_readonly_group;
grant select on all tables in schema <schema_name> to group Redshift_readonly_group;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT select ON tables TO group Redshift_readonly_group;
Please find the syntax for Read-Write:
gran ..read more
Database Technology
1y ago
privilege needed to create UDF function using Python in Redshift: grant usage on language plpythonu to group <group> ..read more
Database Technology
1y ago
script to find currently running queries in Redshift:
select userid , query , pid , starttime , text from stv_inflight order by starttime desc;
script to find currently locks in Redshift:
select table_id, last_update, lock_owner, lock_owner_pid from stv_locks;
SELECT * FROM STV_SESSIONS ..read more