Database Technology: 2026

Tuesday, July 21, 2026

script to find out DB size in Snowflake database

SELECT

USAGE_DATE,

DATABASE_NAME,

ROUND(AVERAGE_DATABASE_BYTES / POWER(1024, 3), 2) AS DATABASE_GB,

ROUND(AVERAGE_FAILSAFE_BYTES / POWER(1024, 3), 2) AS FAILSAFE_GB,

ROUND(AVERAGE_HYBRID_TABLE_STORAGE_BYTES / POWER(1024, 3), 2) AS HYBRID_TABLE_GB,

ROUND(AVERAGE_ARCHIVE_STORAGE_COOL_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COOL_GB,

ROUND(AVERAGE_ARCHIVE_STORAGE_COLD_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COLD_GB,

ROUND(AVERAGE_ARCHIVE_STORAGE_COOL_FAILSAFE_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COOL_FAILSAFE_GB,

ROUND(AVERAGE_ARCHIVE_STORAGE_COLD_FAILSAFE_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COLD_FAILSAFE_GB

FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY

ORDER BY USAGE_DATE DESC, DATABASE_NAME;

SELECT

USAGE_DATE,

DATABASE_NAME,

ROUND(AVERAGE_DATABASE_BYTES / POWER(1024, 3), 2) AS DATABASE_GB,

ROUND(AVERAGE_DATABASE_BYTES / POWER(1024, 2), 2) AS DATABASE_MB

FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY

ORDER BY USAGE_DATE DESC, DATABASE_NAME;

can we create the 2 object with same name in the Snowflake?

we can create the same name table in Snowflake database.

we create permanent table, temporary table in same database, But when we query the table temporary table takes priority.

create permanent table in public schema in SALES_DB

CREATE TABLE CUSTOMERS

(

ID INT,

NAME STRING,

CITY STRING

);

create temporary table in public schema in SALES_DB

CREATE temporary TABLE CUSTOMERS

(

ID INT,

NAME STRING,

CITY STRING

);

insert some records into temporary table ,when you not mention the schema name by default it will insert to temporary table

INSERT INTO CUSTOMERS (ID, NAME, CITY)

VALUES

(1, 'John', 'New York'),

(2, 'Alice', 'London'),

(3, 'Bob', 'Chennai');

select count(*) from public.CUSTOMERS; --0 records --permanent table

select count(*) from CUSTOMERS; --3 temp records

we can conclude that temporary table will take the precedence or priority when we use same name table in Snowflake database.

Friday, May 22, 2026

pg_cron setup in PostgreSQL

pg_cron is a PostgreSQL extension that lets you run scheduled jobs (like Linux cron) directly inside PostgreSQL.

1.RHEL / CentOS / Rocky Linux install

sudo yum install pg_cron_15

2. Enable pg_cron in PostgreSQL

/var/lib/pgsql/15/data/postgresql.conf

Add:

shared_preload_libraries = 'pg_cron'

cron.database_name = 'postgres'

3. Restart PostgreSQL

pg_ctl

4. Create the Extension

psql -U postgres

Create extension:

CREATE EXTENSION pg_cron;

Verify:

SELECT * FROM pg_extension WHERE extname = 'pg_cron';

5. Schedule Jobs

CREATE EXTENSION pg_cron;

PostgreSQL creates a schema named cron inside the database where the extension was installed (commonly the postgres database).

objects like:

cron.job

cron.job_run_details

cron.schedule()

cron.unschedule()

select * from cron.job;

select * from cron.job_run_details;

select * from cron.schedule();

select * from cron.unschedule();

Important detail:

pg_cron is installed per database, not cluster-wide.

If you created the extension in postgres DB:

Check extension location:

SELECT extname, extnamespace::regnamespace

FROM pg_extension

WHERE extname='pg_cron';

postgres=# select version();

version

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

PostgreSQL 15.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit

(1 row)

postgres=# CREATE EXTENSION pg_cron;

CREATE EXTENSION

postgres=# SELECT * FROM pg_extension WHERE extname = 'pg_cron';

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

24853 | pg_cron | 10 | 11 | f | 1.6 | {24856,24855,24875,24874} | {"","","",""}

(1 row)

postgres=# SELECT extname, extnamespace::regnamespace

postgres-# FROM pg_extension

postgres-# WHERE extname='pg_cron';

extname | extnamespace

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

pg_cron | pg_catalog

(1 row)

postgres=# \dn

List of schemas

Name | Owner

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

admin | postgres

cron | postgres

public | pg_database_owner

(3 rows)

postgres=#

SELECT * FROM pg_available_extensions

postgres-# WHERE name = 'pg_cron';

name | default_version | installed_version | comment

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

pg_cron | 1.6 | 1.6 | Job scheduler for PostgreSQL

(1 row)

Find job ID & name:

SELECT jobid, jobname

FROM cron.job;

SELECT jobid, jobname

postgres-# FROM cron.job;

jobid | jobname

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

1 | db-size-collector

(1 row)

Remove:

SELECT cron.unschedule('db-size-collector');

Check Execution History

SELECT *

FROM cron.job_run_details

ORDER BY start_time DESC

LIMIT 10;

Verify Job Created

SELECT * FROM cron.job;

Wednesday, January 7, 2026

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';

show engines;

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

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

| 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;

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

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

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;

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

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

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

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';

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

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

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

1 row in set (0.00 sec)

Get the processid for the session:

mysql> show processlist;

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

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

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

3 rows in set (0.00 sec)

mysql> kill 25;

Query OK, 0 rows affected (0.00 sec)

mysql> show processlist;

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

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

How to kill all sessions of an user

mysql> show processlist;

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

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

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

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: ****

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;

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

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

| 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;