MySQL DB administration scripts/commands

 -- List all users present in mysql

mysql> select user,host,account_locked,password_expired from mysql.user;

Create user in mysql:

mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_user';

Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON *.* TO 'user'@'localhost';

Query OK, 0 rows affected (0.01 sec)

View create user statements:

MariaDB [(none)]> SHOW CREATE USER 'test_user'@'localhost';

MariaDB [(none)]> SHOW CREATE USER 'test_user'@'%';

Rename a user:

mysql> RENAME USER 'dbatest' TO 'dbaprod';

Query OK, 0 rows affected (0.02 sec)

change password of a user:

mysql> ALTER USER 'dbaprod'@'%' IDENTIFIED BY 'dbaprod';

Query OK, 0 rows affected (0.01 sec)

Change resource option:

mysql> alter user 'dbaprod'@'%' with MAX_USER_CONNECTIONS 10;

Query OK, 0 rows affected (0.01 sec)

Lock/unlock an account:

mysql> alter user 'dbaprod'@'%' account lock;

Query OK, 0 rows affected (0.01 sec)

mysql> alter user 'dbaprod'@'%' account unlock;

Query OK, 0 rows affected (0.01 sec)

Make sure you are connected with root/admin user:

mysql> select current_user();

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

| current_user() |

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

| root@localhost |

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

1 row in set (0.00 sec)

Create normal user if not created.

mysql> CREATE user 'super'@'%' IDENTIFIED BY 'super';

Query OK, 0 rows affected (0.02 sec)

mysql> CREATE user 'super'@'localhost' IDENTIFIED BY 'super';

Query OK, 0 rows affected (0.02 sec)

Grant privileges for making it super user:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'%' WITH GRANT OPTION;

Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' WITH GRANT OPTION;

Query OK, 0 rows affected (0.03 sec)

Reload all the privileges

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.02 sec)

 View the grants:

mysql> show grants for super;

Examples on GRANT command

GRANT ALL PRIVILEGES ON *.* to 'user_name'@'localhost';

GRANT ALL PRIVILEGES ON *.* to 'user_name'@'%';

GRANT ALL PRIVILEGES ON *.* to 'user_name'@'localhost' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* to 'user_name'@'%' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON dbname.* to 'user_name'@'localhost';

GRANT ALL PRIVILEGES ON dbname.* to 'user_name'@'%';

GRANT INSERT,UPDATE,DELETE ON TABLE DBNAME.TABLENAME TO 'user_name'@'localhost';

GRANT INSERT,UPDATE,DELETE ON TABLE DBNAME.TABLENAME TO 'user_name'@'%';

GRANT CREATE ON DATABASE.* to 'user_name'@'localhost';

GRANT CREATE ON DATABASE.* to 'user_name'@'%';

GRANT SELECT , EXECUTE ON DATABASE.* to 'user_name'@'%';

GRANT SELECT , EXECUTE ON DATABASE.* to 'user_name'@'localhost';

REVOKE privileges ON <object> FROM user;

REVOKE DELETE, UPDATE ON <table_name> FROM 'user'@'localhost';

REVOKE DELETE, UPDATE ON <table_name> FROM 'user'@'%';

REVOKE ALL ON <table_name> FROM 'user'@'localhost';

REVOKE ALL ON <table_name> FROM 'user'@'%';

REVOKE SELECT ON <object> FROM 'username'@'localhost';

REVOKE SELECT ON <object> FROM 'username'@'localhost';

show engines;

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

| Engine             | Support | Comment                                                 | Transactions | XA | Savepoints |

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

| ARCHIVE            | YES     | Archive storage engine                                        | NO | NO | NO |

Create a database in MySQL / MariaDB

-- Below commands can be used to create database

MariaDB [(none)]> CREATE DATABASE testdb;

Query OK, 1 row affected (0.009 sec)

MariaDB [(none)]> create database testdb1 character set UTF8mb4 collate utf8mb4_bin;

Query OK, 1 row affected (0.009 sec)

-- View the create database statement used for creating db 

MariaDB [(none)]> SHOW CREATE DATABASE testdb1;

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

| Database | Create Database |

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

| testdb1 | CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |

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

1 row in set (0.007 sec)

View database list

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| testdb |

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

5 rows in set (0.006 sec)

mysql> SELECT * fROM information_schema.schemata;

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

| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |

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

| def          | mysql               | utf8mb4           | utf8mb4_0900_ai_ci | NULL | NO |

mysql> show databases like 'test%';

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

| Database (test%) |

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

| test |

How to connect to MySQL / MariaDB database

hostname$ export PATH=/usr/local/mysql/bin:$PATH

hostname$ which mysql

/usr/local/mysql/bin

SYNTAX - mysql -u user -p

C:\Program Files\MariaDB 10.4\bin>mysql -u root -p

Enter password: ****

Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 19.Server version: 10.4.18-MariaDB 

Find current connection info:

MariaDB [(none)]> \s

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

Below is the alternative command:

MariaDB [(none)]> status;

Get current user and current database:

mariadb> select current_user,database();

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

| current_user  | database() |

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

| root@localhost | test |

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

1 row in set (0.00 sec)

switch to another database:

MariaDB> USE TEST;

Database changed

MariaDB> select database();

Drop database from mysql /MariaDB

C:\Program Files\MariaDB 10.4\bin>mysql -u root -p

Enter password: ****

-- List down databases

MariaDB [(none)]> show databases;

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

| Database.          |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

| testdb             |

| testdb1            |

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

6 rows in set (0.001 sec)

-- Check the status:

MariaDB [(none)]> status

-- Drop database:

MariaDB [(none)]> DROP DATABASE testdb;

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema;

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

How to find timezone info

Check whether time_zone table is updated or not.

MariaDB [(none)]> select * from mysql.time_zone_name;

Empty set (0.000 sec)

Find current sessions/process in MYSQL/MARIA DB

Show all processes in mysql cluster.

mysql> show processlist;

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

| Id | User            | Host       | db  | Command | Time  | State                  | Info |

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

| 5  | event_scheduler | localhost | NULL | Daemon | 34888 | Waiting on empty queue | NULL |

| 23 | root            | localhost | test | Sleep. | 36    | | NULL                 |

| 24 | root.           | localhost | NULL | Query  | 0      | init                   | show processlist |

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

3 rows in set (0.00 sec)

Show processes for a specific user:

mysql> select * from information_schema.processlist where user='root'\G

Show processes for a particular database:

mysql> select * from information_schema.processlist where DB='test';

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

| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |

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

| 23 | root | localhost | test | Sleep | 70 | | NULL |

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

1 row in set (0.00 sec)

Get the processid for the session:

mysql> show processlist;

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

| Id | User            | Host.     | db   | Command | Time  | State                  | Info |

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

| 5 | event_scheduler  | localhost  | NULL | Daemon  | 35192 | Waiting on empty queue | NULL |

| 24 | root            | localhost  | NULL | Query   | 0     | init                   | show processlist |

| 25 | root            | localhost  | test | Sleep.   | 6    |                        | NULL         |

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

3 rows in set (0.00 sec)

mysql> kill 25;

Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;

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

| Id | User            | Host.     | db   | Command | Time  | State                  | Info |

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

| 5 | event_scheduler  | localhost  | NULL | Daemon  | 35192 | Waiting on empty queue | NULL |

| 24 | root            | localhost  | NULL | Query   | 0     | init 

How to kill all sessions of an user

mysql> show processlist;

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

| Id | User            | Host      | db     | Command | Time   | State                  | Info |

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

| 5 | event_scheduler  | localhost | NULL  | Daemon   | 36418 | Waiting on empty queue | NULL |

| 24 | root | localhost | NULL     | Query | 0        | init  | show processlist        |

| 26 | root | localhost | test     | Sleep | 376      |       | NULL                    |

| 27 | root | localhost | testdb.  | Sleep | 347      |       | NULL                    |

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

4 rows in set (0.00 sec)

Generate kill statements for killing all sessions of user root

mysql> SELECT GROUP_CONCAT(CONCAT('KILL ',id,';') SEPARATOR ' ') 'Run the kill statements ----->>> ' FROM information_schema.processlist WHERE user='root'\G

*************************** 1. row ***************************

Run the kill statements ----->>> : KILL 24; KILL 26; KILL 27;

1 row in set (0.00 sec)

mysql> KILL 24; KILL 26; KILL 27;

Uptime of server

MariaDB [(none)]> status;

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

mysql Ver 15.1 Distrib 10.4.18-MariaDB, for Win64 (AMD64), source revision 53123dfa3e365138591fd2f160c6057aca00a3e6

Connection id: 26

Current database:

.............

TCP port: 3306

Uptime: 4 days 19 hours 28 min 14 sec

Alternative command

MariaDB [(none)]> \s

Server startup time:

MariaDB [(none)]> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';

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

| Uptime.  |

+----------+m

| 115h 30m |

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

1 row in set (0.007 sec)

C:\Program Files\MariaDB 10.4\bin>mysqladmin version -u root -p

Enter password: ****

mysqladmin Ver 9.1 Distrib 10.4.18-MariaDB, for Win64 on AMD64 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version 10.4.18-MariaDB

Protocol version 10

Connection localhost via TCP/IP

TCP port 3306

Uptime: 4 days 19 hours 33 min 2 sec

MariaDB [(none)]> show variables like '%data%';

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

| Variable_name                               | Value                               |

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

| datadir                                     | /usr/local/mysql/data/ |

MariaDB [(none)]> SELECT Variable_Value FROM information_schema.GLOBAL_VARIABLES WHERE Variable_Name = 'datadir';

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

| Variable_Value                      |

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

| /usr/local/mysql/data/ |

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

1 row in set (0.001 sec)

mysql> select @@datadir;

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

| @@datadir              |

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

| /usr/local/mysql/data/ |

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

1 row in set (0.00 sec)

 Find current data/time on MySQL / MariaDB

mysql> select CURRENT_TIMESTAMP,current_date,localtime(0),localtimestamp(0);

Find MySQL / MariaDB configuration values

1 . Get Config values from mysql prompt.

MariaDB [(none)]> SELECT VARIABLE_NAME,VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES;

MariaDB [(none)]> SELECT VARIABLE_NAME,VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE Variable_Name = 'port';

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

| VARIABLE_NAME | VARIABLE_VALUE |

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

| PORT          | 3306           |

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

1 row in set (0.001 sec)

MariaDB [(none)]> SELECT VARIABLE_NAME,VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE Variable_Name = 'datadir';

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

| VARIABLE_NAME | VARIABLE_VALUE                      |

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

| DATADIR       | C:\Program Files\MariaDB 10.4\data\ |

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

1 row in set (0.001 sec)

2. Alternatively you can check my.cnf file in /etc folder in Linux

cat /etc/my.cnf

Find the last MySQL / MariaDB Database service restarted / server reboot time

MariaDB [(none)]> status

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

mysql Ver 15.1 Distrib 10.4.18-MariaDB, for Win64 (AMD64), source revision 53123dfa3e365138591fd2f160c6057aca00a3e6

Connection id: 33

Current database:

Current user: root@localhost

SSL: Not in use

...........

Uptime: 6 days 2 hours 3 min 3 sec

-- In older version:

MariaDB [(none)]> select TIME_FORMAT(SEC_TO_TIME(VARIABLE_VALUE ),'%Hh %im') as Uptime from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Uptime';

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

| Uptime   |

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

| 146h 03m |

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

1 row in set (0.009 sec)

-- In latest mysql version:

MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Uptime';

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

| Variable_name | Value |

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

| Uptime        | 525859 |

mysql> show engines;

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

| Engine             | Support | Comment                                                 | Transactions | XA | Savepoints |

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

| ARCHIVE            | YES     | Archive storage engine                                        | NO | NO | NO |

| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO | NO 

SELECT table_schema AS 'Database Name',  

SUM(data_length + index_length) 'Size in Bytes',  

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MB'  

FROM information_schema.tables  

-- WHERE table_schema = 'kellogg_xalientkdndb'  

-- WHERE table_schema in ('huedb', 'rangerdb', 'rangerkmsdb', 'hivedb', 'scm')

-- WHERE table_schema in ('metastore','hive','hue','rangerdb','ranger','rangerkmsdb','scm')

GROUP BY table_schema

order by 1; 

owner of the table:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, CREATE_USER

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'your_table_name';

to change the ownership:

  MYDB.SCHEMA(USER)=> ALTER TABLE weather OWNER TO xyz;

step by step Reading MSSQL DB data from Oracle 19c database using DB link & ODBC Drivers :

 Reading MSSQL DB data from Oracle 19c  database using DB link & ODBC Drivers  :

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

List of steps:

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

1. Root - Install Microsoft SQL server ODBC drivers.

  sudo ACCEPT_EULA=Y yum install -y msodbcsql17

2. Root - Create ODBC entry in /etc/odbc.ini file pointing to remote.

3. Oracle - create init parameter file initora_19c_DB_MSSQL. Ora

4. Oracle - register SID in listener.ora.

5. Oracle- Create entry in tnsnames.ora pointing to SID.

6. Oracle - create DB link in oracle database.

7. Oracle- Optionally - Create synonym.

8. Install Microsoft SQL server ODBC drivers

How to install the Microsoft ODBC Driver for SQL Server on Linux. It also includes instructions for the optional command-line tools for SQL Server and the unixODBC development headers.

Here we are taking up the ODBC 17 drivers. Please go to the below link and ask to unix team to install the driver on the server.

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver17&tabs=redhat18-install%2Credhat17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline

Once installed the package they need to run the ODBC driver using below command.

  sudo ACCEPT_EULA=Y yum install -y msodbcsql17

9. Create ODBC entry in /etc/odbc.ini file pointing to remote

To create an ODBC entry in /etc/odbc.ini that points to a remote database, you need to define a Data Source Name (DSN) within the file. This DSN will contain the necessary parameters to connect to your remote database.

Here is an example of an odbc.ini entry for a remote database.

[DS_MSSQL]

Description = MSSQL

Driver = /usr/lib64/libmsodbcsql-17.so

Server = 20.20.30.30.\MSSQL_instance1

Port = 57500

Database = MSSQL_DB

UID = user_ora2mssql

Explanation of the parameters:

• [DS_MSSQL]: This is the DSN name. You can choose any descriptive name for your connection.

• Description: A brief description of the data source.

• Driver: The name of the ODBC driver you have installed for your specific database SQL Server. Ensure the corresponding driver is correctly installed and configured in /etc/odbcinst.ini.

• Server : The hostname or IP address of your remote database server.

• Port: The port number on which the database server is listening for  SQL Server.

• Database: The name of the specific database you want to connect to on the remote server.

• UID: The username for authenticating with the database.

10. Create init parameter file initora_19c_DB_MSSQL. Ora

To create a Heterogeneous Services (HS) initialization parameter file for connecting to a SQL Server database named ora_19c_DB, you would create a text file named initora_19c_DB_MSSQL.ora and place it in the $ORACLE_HOME/hs/admin directory. This file contains parameters specific to the gateway, not the main Oracle database.

The file should be located in the $ORACLE_HOME/hs/admin directory.

Below is a sample configuration. The specific parameters required will depend on your environment and the specific Oracle Database Gateway version you are using.

vi $ORACLE_HOME/hs/admin/initora_19c_DB_MSSQL.ora

# needed for the database gateway for odbc

# HS init parameters

HS_FDS_CONNECT_INFO = DS_MSSQL

HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so

HS_LANGUAGE= AL16UTF16

HS_FDS_REMOTE_DB_CHARSET=AMERICAN_AMERICA.AL16UTF16

HS_NLS_NCHAR=UCS2

#

# ODBS SPECIFIC ENVIRONMEANT VARIABLES

#

set ODBCINI=/etc/odbc.ini

11. register SID in listener.ora. 

Add the HS SID listener in listener file To register an Oracle System Identifier (SID) for a database instance or a Heterogeneous Services (HS) agent in the listener.ora file, you need to perform static registration. This involves manually adding a SID_DESC entry to the SID_LIST section of the file. 

The listener. Ora file is typically located in the $ORACLE_HOME/network/admin directory

Add a SID_DESC entry:-

Within the SID_LIST_<listener_name> section (by default SID_LIST_LISTENER), add a new SID_DESC block. This block specifies the details for the service you want to register.For an Oracle Database Instance

==================================

LISTENER_ora_19c_DB =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 30.30.30.50)(PORT = 1587))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1587))

    )

  )

 

SID_LIST_LISTENER_ora_19c_DB =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = ora_19c_DB)

      (ORACLE_HOME = /oracle/ora_19c_DB/12201)

    )

    (SID_DESC =

      (SID_NAME = ora_19c_DB_MSSQL)

      (ORACLE_HOME = /oracle/ora_19c_DB/12201)

      (PROGRAM = /oracle/ora_19c_DB/12201/bin/dg4odbc)

    )

  )

12. Create entry in tnsnames.ora pointing to SID.

To create an entry in the tnsnames.ora file pointing to an Oracle SID, follow these steps:

• Locate the tnsnames.ora file: This file is typically found in the $ORACLE_HOME/network/admin directory. If the TNS_ADMIN environment variable is set, it will point to the directory containing the tnsnames.ora file.

• Open the tnsnames.ora file: Use a text editor to open the tnsnames.ora file.

• To create an entry in the tnsnames.ora file for an Oracle Heterogeneous Services (HS) SID, you need to manually edit the file to include a specific connect descriptor. The HS=OK parameter is crucial in the CONNECT_DATA section to direct Oracle Net to use Heterogeneous Services.

TNS_MSSQL.WORLD =

(DESCRIPTION =

    (SDU = 32768)

    (ADDRESS_LIST =

        (ADDRESS =

          (PROTOCOL = TCP)

          (HOST = 30.30.30.50)

          (PORT = 1587)

        )

    )

    (CONNECT_DATA =

       (SID =ora_19c_DB_MSSQL)

         )

        (HS=OK)

  )

• (HS = OK): This mandatory parameter tells Oracle Net to connect to a non-Oracle system using Heterogeneous Services.

13. Oracle - create DB link in oracle database.

• Permissions: 

Ensure the user creating the database link has the necessary CREATE DATABASE LINK privilege. The remote_user in the CONNECT TO clause must exist in the remote database and have privileges to access the objects you intend to use via the link.

• Public vs. Private: 

Database links can be PUBLIC (accessible by all users) or private (owned by the user who created it). To create a public link, add the PUBLIC keyword after CREATE.

• GLOBAL_NAMES parameter: 

The GLOBAL_NAMES initialization parameter can impact database link behavior. If set to TRUE, the database link name must match the global name of the remote database. Consider setting it to FALSE if strict name matching is not desired.

Note :- Before you creating the DB link they need to be create the SQL server authentication user in SQL server then only you make connection between from Oracle to MSSQL.

Create SQL Server Authentication User:- 

• In SQL Server Management Studio (SSMS): 

o Go to Security → Logins → New Login.

o Choose SQL Server Authentication (not Windows Authentication).

o Set a strong password and ensure Enforce password policy is configured as needed.

o Map the login to the required database and assign appropriate roles (e.g., db_datareader.

Syntax :-         CREATE DATABASE LINK my_remote_link     CONNECT TO remote_user IDENTIFIED     BY remote_password    USING 'REMOTE_DB_ALIAS';

Example :- create public database link  mssql_dblink6 connect to "user_ora2mssql" identified by "" using 'TNS_MSSQL';

Select  count(*)  from  "view_test"@mssql_dblink6;

 

14. Optionally - Create synonym.

To create a synonym for a remote object accessed through a database link in Oracle.

Once the database link is established, you can create a synonym for a specific object (table, view, sequence, etc.) in the remote database.

CREATE SYNONYM synonym_name FOR remote_table_name@db_link_n

Example :- Create public synonym mssqlview   For view_test@mssql_dblink6;

After creating the synonym, you can query the remote table using the synonym name as if it were a local object:

Select count(*) from mssqlview.

This will read the Data from MSSQL from Oracle DB

 

 

step by step to read oracle DB data from MSSQL server using linked server in MSSQL database

 Linked Server in MS SQL Server

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

Source : MS SQL 2019 EE on Windows 22

Target : Oracle 19c R2 EE on Linux 8 - IP - 192.168.56.74 Port- 1583 ServiceName- ORA_DB_19c

Pre-Req:

-----------

a. You must have Oracle client installed on MSSQL server.

b. Working Network Connection between both Servers.

   (If required create exception in Firewall.)

c. Remote (Oracle) DB credentials.

d. Proper TNS entry for remote (Oracle) DB.

Steps:

---------

1. Create User in Remote DB Server

Create user oracle identified by oracle123;

grant create session to oracle;

2. Provide Required Permission to User as per your need

grant select any table to oracle;

grant select any dictionary to oracle;

3. Create TNS Entry in Oracle Client TNSNAMES.ora in Source Server (MS SQL Server)

ORA_DB_19c=(DESCRIPTION=

(CONNECT_DATA=

(SERVICE_NAME=ORA_DB_19c))

(ADDRESS=

(PROTOCOL=TCP)(HOST=12.16.53.12)(PORT=1583)

)

)

4. Create Linked Server Using SSMS

You Need 3 Values : 

a. LINKED SERVER NAME -- Any Name you want to use , Just Like any DBLink. --ORACLE_DB

b. Product Name -- Oracle

c. Data Source -- TNS Service Name of Oracle DB

d. Fill Credentials of Remote DB in Security Section --oracle DB user name & password

5. Enable AllowInProcess in SQL Server

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

6. Test Linked Server

7. Fetch records from Oracle DB using Linked Server in MS SQL Server

ORACLE_DB---Linked server name

SELECT * FROM OPENQUERY([ORACLE_DB] , 'select name,open_mode,host_name from v$database,v$instance') ;

SQL script to push the grants to a group in AWS Redshift DB for Read and write access

 1.select 'grant usage on schema ' ||nspname||' to <new_group_name>;' from pg_catalog.pg_namespace 

where nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';  ---1

2.select 'grant select,insert,update,delete on all tables in schema ' ||nspname||' to <new_group_name>;' from pg_catalog.pg_namespace 

where nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';     ---2

3.select 'ALTER DEFAULT PRIVILEGES IN SCHEMA ' ||nspname||' for user "<table_owner>" GRANT select,insert,update,delete ON tables to <new_group_name>;' from pg_catalog.pg_namespace 

where nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';      ---3

SQL script to change the ownership of schema, table ,procedure in AWS Redshift Database

 

SQL script to change the ownership of schema, table ,procedure in AWS Redshift Database

1.change the ownership of schema

select 'alter schema ' ||nspname||' owner to "<new_schema_name>";' from pg_catalog.pg_namespace 

where nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';

2.change the ownership of table

select 'alter table   klg_nga_kla.'||schemaname||'."'||tablename||'" owner to "<new_schema_name>";'

from pg_catalog.pg_tables where schemaname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and schemaname not like 'pg_%';

3.change the ownership of procedure

SELECT 'alter  procedure '||n.nspname||'."'||p.proname||'" owner to "<new_schema_name>";'

    

FROM

    pg_catalog.pg_namespace n

JOIN pg_catalog.pg_proc p ON

    pronamespace = n.oid

join pg_catalog.pg_user b on

    b.usesysid = p.proowner

where

    nspname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and nspname not like 'pg_%';

--add argument on the procedue while changing the ownership

AWS Redshift SQL scripts to find out session and Audit information

 What is the best way to assess tables that need to be vacuumed or analyzed?

This query returns tables where greater than 20% of rows are unsorted or statistics are 20% stale.

SELECT "database", "schema", "table", unsorted, stats_off

FROM svv_table_info

WHERE unsorted > 20

OR stats_off > 20

How can I troubleshoot loading errors?

Selecting from stl_load_errors provides information about errors during loading, and can be helpful for troubleshooting problematic loads.

SELECT *

FROM stl_load_errors

ORDER BY starttime DESC

LIMIT 100;

How to look specifically for failed logins?

SELECT *

FROM stl_connection_log

WHERE event='authentication failure'

ORDER BY recordtime;

Showing successfully authenticated users with the number of successful authentications:

SELECT username, event, COUNT(*)

FROM stl_connection_log

WHERE event = 'authenticated'

GROUP BY 1, 2

ORDER BY 3 DESC;

Showing successfully authenticated users by hourly buckets:

SELECT DATE_PART(YEAR, recordtime) || '-' ||

LPAD(DATE_PART(MONTH, recordtime),2,'0') || '-' ||

LPAD(DATE_PART(DAY, recordtime),2,'0') || ' ' ||

LPAD(DATE_PART(HOUR, recordtime),2,'0') AS hour_bucket, username, COUNT(*)

FROM stl_connection_log

WHERE event = 'authenticated'

GROUP BY 1, 2

ORDER BY 1, 2 DESC;

Showing a list of the connection drivers used by the redshift users:

SELECT username, application_name, COUNT(*) 

FROM stl_connection_log

WHERE application_name != ''

GROUP BY 1,2

ORDER BY 1,2;

Privilege violation logging & monitoring in AWS Redshift

Prior to setting such access controls, you will be able to see queries pulling data from these resources by querying STL_QUERY, as seen below: Retrieving queries access to specific objects in Redshift:

SELECT * FROM STL_QUERY

WHERE userid!=1

AND querytxt LIKE '%customers%'

ORDER BY query DESC

LIMIT 100;

SELECT username,dbname,recordtime

FROM stl_connection_log

WHERE event='authentication failure'

ORDER BY recordtime > '2022-07-08';

 Get the disk based queries information for last 2 days

SELECT q.query, 

       q.endtime - q.starttime             AS duration, 

       SUM(( bytes ) / 1024 / 1024 / 1024) AS GigaBytes, 

       aborted, 

       q.querytxt 

FROM   stl_query q 

       join svl_query_summary qs 

         ON qs.query = q.query 

WHERE  qs.is_diskbased = 't' 

       AND q.starttime BETWEEN SYSDATE - 2 AND SYSDATE 

GROUP  BY q.query, 

          q.querytxt, 

          duration, 

          aborted 

ORDER  BY gigabytes DESC ;

/* Query showing information about sessions with currently running queries */

SELECT s.process AS pid

       ,date_Trunc ('second',s.starttime) AS S_START

       ,datediff(minutes,s.starttime,getdate ()) AS conn_mins

       ,trim(s.user_name) AS USER

       ,trim(s.db_name) AS DB

       ,date_trunc ('second',i.starttime) AS Q_START

       ,i.query

       ,trim(i.query) AS sql

FROM stv_sessions s

  LEFT JOIN stv_recents i

         ON s.process = i.pid

        AND i.status = 'Running'

WHERE s.user_name <> 'rdsdb'

ORDER BY 1;

/* Query shows EXPLAIN plans which flagged "missing statistics" on the underlying tables */

SELECT substring(trim(plannode),1,100) AS plannode

       ,COUNT(*)

FROM stl_explain

WHERE plannode LIKE '%missing statistics%'

AND plannode NOT LIKE '%redshift_auto_health_check_%'

GROUP BY plannode

ORDER BY 2 DESC;

/* query showing queries which are waiting on a WLM Query Slot */

SELECT w.query

       ,substring(q.querytxt,1,100) AS querytxt

       ,w.queue_start_time

       ,w.service_class AS class

       ,w.slot_count AS slots

       ,w.total_queue_time / 1000000 AS queue_seconds

       ,w.total_exec_time / 1000000 exec_seconds

       ,(w.total_queue_time + w.total_Exec_time) / 1000000 AS total_seconds

FROM stl_wlm_query w

  LEFT JOIN stl_query q

         ON q.query = w.query

        AND q.userid = w.userid

WHERE w.queue_start_Time >= dateadd(day,-7,CURRENT_DATE)

AND   w.total_queue_Time > 0

-- and q.starttime >= dateadd(day, -7, current_Date)    

-- and ( querytxt like 'select%' or querytxt like 'SELECT%' ) 

ORDER BY w.total_queue_time DESC

         ,w.queue_start_time DESC limit 35;

/* query showing queries which are waiting on a WLM Query Slot */

SELECT w.query

       ,substring(q.querytxt,1,100) AS querytxt

       ,w.queue_start_time

       ,w.service_class AS class

       ,w.slot_count AS slots

       ,w.total_queue_time / 1000000 AS queue_seconds

       ,w.total_exec_time / 1000000 exec_seconds

       ,(w.total_queue_time + w.total_Exec_time) / 1000000 AS total_seconds

FROM stl_wlm_query w

  LEFT JOIN stl_query q

         ON q.query = w.query

        AND q.userid = w.userid

WHERE w.queue_start_Time >= dateadd(day,-7,CURRENT_DATE)

AND   w.total_queue_Time > 0

-- and q.starttime >= dateadd(day, -7, current_Date)    

-- and ( querytxt like 'select%' or querytxt like 'SELECT%' ) 

ORDER BY w.total_queue_time DESC

         ,w.queue_start_time DESC limit 35;

Shell script to check and record the database size in Redshift Database

Here is the custom script used to check the Redshift DB size and record it every month .This  will  be useful to get the know the DB growth.

1.create the table on DB  to store the DB size

CREATE TABLE admin.rs_db_size_dev (

    db_name    VARCHAR(256) ENCODE zstd,

    db_size_gb NUMERIC(    db_size_gb NUMERIC(37,2) ENCODE az64,

    date       DATE ENCODE az64

)

DISTSTYLE AUTO

2.schedule the job on cron

2 0 12 * *  /redshiftadmin/aws/scripts/dev_db_size_chk.sh DB1_DW1_India >> /redshiftadmin/aws/audit/log/db_size_dev_india.log 2>&1

3.load the script to  the server.

##################################################################################################

## purpose     :script used to collect Redshift dev DB size details                              #

## Author      : Bala P                                                                          #

## Developed   :10-july-2023 V1                                                                  #

##################################################################################################

#

##!/usr/bin/bash

#

export PGHOST=XXXXXXXXXXX.redshift.amazonaws.com

export PGPORT='5439'

export PGDATABASE=$1

export PGUSER=rsdbadmin

export PGPASSWORD='XXXXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

query_result=$(psql -tA -c  "INSERT INTO admin.rs_db_size_dev (db_name, db_size_GB, date)

SELECT     dbase_name, total_GB AS db_size, CURRENT_DATE   FROM ( SELECT   dbase_name,  SUM(megabytes/1024) AS total_GB

FROM  admin.v_space_used_per_tbl   GROUP BY   dbase_name ) AS aggregated_data;")

#

#

if [[ -n "$query_result" ]]; then

##   if [[ -n "$query_result" && $(echo "$query_result" | grep -c 'exec_time_hours > 0.001') -gt 0 ]]; then

#       

recipient="BALAS@abc.com"

       subject="DB size  @ $PGDATABASE in Dev"

       body="DB size in dev cluster\n$query_result"

       echo -e "$body" | mailx -s "$subject" "$recipient"

      fi

4.output of the script

=# select * from admin.rs_db_size_dev;

 db_name    | db_size_gb |    date

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

DB1_DW1_India |    1654.00 | 2024-10-12

DB1_DW1_India |    1671.00 | 2024-11-12

DB1_DW1_India |    1515.00 | 2024-12-12

DB1_DW1_India |    1528.00 | 2025-01-12

DB1_DW1_India |    1573.00 | 2025-02-12

DB1_DW1_India |    1579.00 | 2025-03-12

DB1_DW1_India |    1666.00 | 2025-04-12

DB1_DW1_India |    1787.00 | 2025-05-12

DB1_DW1_India |    1788.00 | 2025-06-12

DB1_DW1_India |    1791.00 | 2025-07-12

DB1_DW1_India |    1788.00 | 2025-08-12

DB1_DW1_India |    1796.00 | 2025-09-12

DB1_DW1_India |    1801.00 | 2025-10-12

DB1_DW1_India |    1801.00 | 2025-11-12

DB1_DW1_India |    1801.00 | 2025-12-12

DB1_DW1_India |     249.00 | 2023-11-10

DB1_DW1_India |     249.00 | 2023-11-10

DB1_DW1_India |     249.00 | 2023-11-12

DB1_DW1_India |     246.00 | 2023-12-12

DB1_DW1_India |     166.00 | 2024-01-12

DB1_DW1_India |     318.00 | 2024-02-12

DB1_DW1_India |     236.00 | 2024-03-12

DB1_DW1_India |     334.00 | 2024-04-12

DB1_DW1_India |     772.00 | 2024-05-12

DB1_DW1_India |     965.00 | 2024-06-12

DB1_DW1_India |    1082.00 | 2024-07-12

DB1_DW1_India |    1528.00 | 2024-08-12

DB1_DW1_India |    1606.00 | 2024-09-12

(28 rows)

Snowflake Database Admin scripts

 Snowflake DB Admin scripts:

Snowflake DB admin commands/scripts

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

To know the current_user

SELECT CURRENT_USER();

To know the current_Role

SELECT CURRENT_ROLE();

To know the current_DB

SELECT CURRENT_DATABASE();

To use another DB

use DWDB1

To list down the DB's

SHOW DATABASES;

select * from information_schema.databases;

To list down the tables,views,users

show tables;

SHOW WAREHOUSES;

SHOW USERS;

show views;

show functions;

show procedures;

SHOW SCHEMAs;

show roles;

we got Oracle database error ORA-38774: cannot disable media recovery - flashback database is enabled

 Issue:

we got Oracle database error  ORA-38774: cannot disable media recovery - flashback database is enabled when tried to turn off the archive log mode .

Root cause:

oracle DB will not allow  to turn off the archive log mode ,until you disable the Flashback.

Solution:

Total System Global Area 8.7242E+10 bytes

Fixed Size                  5295200 bytes

Variable Size            9663677344 bytes

Database Buffers         7.7309E+10 bytes

Redo Buffers              263139328 bytes

Database mounted.

SQL> alter database noarchivelog;

alter database noarchivelog

*

ERROR at line 1:

ORA-38774: cannot disable media recovery - flashback database is enabled

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON

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

DB1       YES

SQL> alter database flashback off;

Database altered.

SQL> select name,flashback_on from v$database;

NAME      FLASHBACK_ON

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

DB1       NO

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

DB1       MOUNTED

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /oracle/DB1/oraarch/DB1arch

Oldest online log sequence     361611

Current log sequence           361616

SQL>  select name,open_mode from v$database;

NAME      OPEN_MODE

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

DB1       READ WRITE

Shell script to find out super user list in AWS Redshift

 ################################################################################################

# purpose     :script used to collect PROD  Redshift super user list for audit

# Author      : Bala P

# Developed   :19-sep-2022 V1

################################################################################################

#!/usr/bin/bash

export PGHOST=XXXX.redshift.amazonaws.com

export PGPORT='5454'

export PGDATABASE=RS_DB1

export PGUSER=rsdbadmin

DBA=balamani@abc.com

export DBA

export PGPASSWORD='XXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

psql  -f $wdir/super_user_list.sql -o $logdir/super_user_list.sql_output.log

mailx -s "Super user list from Redshift PROD cluster " $DBA  < $logdir/super_user_list.sql_output.log

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

 super_user_list.sql

\qecho

select usename,usesuper FROM pg_catalog.pg_user_info where usesuper='true';

check_tbl_ownr_compl.sql

select  schemaname,tablename ,tableowner from pg_catalog.pg_tables where schemaname not in ('public','pg_catalog','catalog_history','information_schema','dbainfo') and tableowner not in ('rsdbadmin') and schemaname not like 'pg_%';

 chk_tbl_owner_public.sql

select schemaname,tablename,tableowner  from pg_catalog.pg_tables  where schemaname='public';

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