Author: Admin

Transfer SQL Profiles from One database to other database.

05-Sep-2024 Admin No Comments

In this guide, I’ll walk you through moving SQL profiles using a staging table. SQL profiles help databases optimize query execution plans. Step#1 Create the Staging Table First, create a staging table to hold SQL profiles that need to be transferred. SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name => ‘SQL_STG_TAB’, schema_name => ‘SYS’); This will create a table SQL_STG_TAB…

Oracle, shell, SQL scripts

Load testing on Oracle 19C RAC with HammerDB

18-Jan-202418-Jan-2024 Admin No Comments

When dealing with Oracle 19c Real Application Clusters (RAC), the process of capturing a workload from a source Oracle 19c RAC database and replaying it on a target Oracle 19c RAC database using HammerDB involves some additional considerations. Below are the revised steps: Capture and Replay Workload from Source to Target Oracle 19c RAC Download…

Oracle

Add new columns in dataframe

30-Sep-202301-Oct-2023 Admin No Comments

from pyspark.sql.functions import col, lit# File location and typefile_location = “/FileStore/tables/sales_data_part1.csv”file_type = “csv”# CSV optionsinfer_schema = “false”first_row_is_header = “true”delimiter = “,”# The applied options are for CSV files. For other file types, these will # be ignored.df = spark.read.format(file_type) .option(“inferSchema”, infer_schema) .option(“header”, first_row_is_header) .option(“sep”, delimiter) .load(file_location)display(df)# Adding new column with and with default values. #…

Python/PySpark

Getting started with notebook

30-Sep-202330-Sep-2023 Admin No Comments

# Databricks notebook source %sqlcreate database if not exists abcdb %sqluse abcdb %sqlcreate table student (id int , name string) %sqlinsert into studentselect 1 as id, ‘user1’ as name %sqlselect * from student spark.sql(“create database if not exists xyzdb”)spark.sql(“use xyzdb”) # Use three double quotes (“””) if SQL…

Python/PySpark

Read CSV file using PySpark

30-Sep-202330-Sep-2023 Admin No Comments

from pyspark.sql.functions import col # File location and type file_location = “/FileStore/tables/sales_data_part1.csv” file_type = “csv” # CSV options infer_schema = “false” first_row_is_header = “true” delimiter = “,” # The applied options are for CSV files. For other file types, these will be ignored. df = spark.read.format(file_type) .option(“inferSchema”, infer_schema) .option(“header”, first_row_is_header) .option(“sep”,…

Python/PySpark

Read CSV File using Python

30-Sep-202330-Sep-2023 Admin No Comments

# Databricks notebook source import pandas as pd # preapre dataframe df_h=pd.read_csv(“https://people.sc.fsu.edu/~jburkardt/data/csv/zillow.csv”) #Print dataframe df_h #define custom columns columns = [“Srno”, “SQFT”, “BR”, “Baths”, “Zipcode”, “YearBuilt”, “ListPrice”] df_h=pd.read_csv(“https://people.sc.fsu.edu/~jburkardt/data/csv/zillow.csv”, header=None, names=columns) #Print dataframe df_h # show top 10 rows df_h.head(10) # show last 10 rows df_h.tail(10) #Print dataframe df_h #Drop non-needed columns, column names are case…

Python/PySpark

Creating never expiring DB user accounts in Oracle

14-Sep-202314-Sep-2023 Admin No Comments

DB Users are required to reset the password periodically for better security. For some service accounts, we need to make sure the password does not expire and does not impact the application. For this purpose, we need to update that user’s Profile. Each Profile has multiple security settings that controls Password_life_time, password_reuse_time, password_reuse_max settings. These…

Oracle, SQL scripts

Convert multiple rows to single column

13-Sep-202313-Sep-2023 Admin No Comments

Converting rows to single column: create table emp ( empno number, empname varchar2(100), deptno number); insert into emp values( 1, ’emp1′, 10); insert into emp values( 2, ’emp2′, 20); insert into emp values( 3, ’emp3′, 30); insert into emp values( 4, ’emp4′, 10); insert into emp values( 5, ‘paresh’, 30); insert into emp values( 6,…

Oracle, SQL scripts

Find all users who have DML privileges

05-Sep-202305-Sep-2023 Admin No Comments

Run following query as sysdba. alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; select username, created, account_status /* Users who granted role with DML privs */ from dba_users where oracle_maintained = ‘N’ and username in (select grantee from dba_role_privs where granted_role in (select role from dba_roles where role in (select GRANTEE from dba_tab_privs where privilege…

Oracle, SQL scripts

Oracle GoldenGate lag monitoring shell script

12-Jun-202318-Jun-2023 Admin No Comments

Following script will be useful for tracking Oracle GoldenGate replicat process and related lag. This script will generate output on standard screen. You can redirect it using file redirection to logfile or other locations. while true do <goldengate home directory>/ggsci <<EOF > test.out info all exit EOF lag=`cat test.out | grep TESTREPLICAT | awk ‘{print…

Linux/Unix, Oracle, shell