Snowflake : show databases command optimized

In snowflake, we use the "show databases" command to see details about default and created databases, this command shows a lot of details about databases, what if we just need the names of the database not whole details about them, after digging out I found this "databases" is table present in INFORMATION_SCHEMA schema, so we can query the details from "databases" tables. 

To fetch details you need to select the data warehouse, database, and schema 

 

technosnow#(no warehouse)@(no database).(no schema)>use WAREHOUSE TECHNO_WS;

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

| status                           |

|----------------------------------|

| Statement executed successfully. |

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

1 Row(s) produced. Time Elapsed: 0.644s

technosnow#TECHNO_WS@(no database).(no schema)>use SNOWFLAKE;

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

| status                           |

|----------------------------------|

| Statement executed successfully. |

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

1 Row(s) produced. Time Elapsed: 0.810s

technosnow#TECHNO_WS@SNOWFLAKE.(no schema)>use schema INFORMATION_SCHEMA;

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

| status                           |

|----------------------------------|

| Statement executed successfully. |

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

1 Row(s) produced. Time Elapsed: 0.313s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>desc databases;

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

| name           | type              | kind   | null? | default | primary key | unique key | check | expression | comment                                                         | policy name |

|----------------+-------------------+--------+-------+---------+-------------+------------+-------+------------+-----------------------------------------------------------------+-------------|

| DATABASE_NAME  | VARCHAR(16777216) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | Name of the database                                            | NULL        |

| DATABASE_OWNER | VARCHAR(16777216) | COLUMN | N     | NULL    | N           | N          | NULL  | NULL       | Name of the role that owns the schema                           | NULL        |

| IS_TRANSIENT   | VARCHAR(3)        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Whether this is a transient table                               | NULL        |

| COMMENT        | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Comment for this database                                       | NULL        |

| CREATED        | TIMESTAMP_LTZ(9)  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Creation time of the database                                   | NULL        |

| LAST_ALTERED   | TIMESTAMP_LTZ(9)  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Last altered time of the database                               | NULL        |

| RETENTION_TIME | NUMBER(9,0)       | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | Number of days that historical data is retained for Time Travel | NULL        |

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

7 Row(s) produced. Time Elapsed: 0.402s

 

Here you see the columns for the "databases" table, now you can fetch anything you want

 

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>select database_name from databases;

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

| DATABASE_NAME         |

|-----------------------|

| EXERCISE_DB           |

| SNOWFLAKE_SAMPLE_DATA |

| TECHNODB              |

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

3 Row(s) produced. Time Elapsed: 1.093s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>

[Also Read - Different Editions of Snowflake]

[Also Read - How to Undrop database]

 

Read More

Snowflake : spool output in log file

In almost all databases features, we have a spool file option, we will discuss the same spool option in snowflake

Assume, you need to execute the script which is a little long and the output can not be captured in a single screenshot, so we can make use of spooling the output in the log file. 

once you are ready to execute the script, browse to the location where you want to save a log file, in the snowsql command prompt use the below option (!spool on/off)

C:\Users>cd C:\Users\database

C:\Users\database>

C:\Users\database>snowsql -a **** -u technosnow

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>!spool snowflake_first_log.log

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>SELECT CURRENT_DATABASE();

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

| CURRENT_DATABASE() |

|--------------------|

| SNOWFLAKE          |

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

1 Row(s) produced. Time Elapsed: 0.503s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>SELECT CURRENT_SCHEMA();

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

| CURRENT_SCHEMA()   |

|--------------------|

| INFORMATION_SCHEMA |

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

1 Row(s) produced. Time Elapsed: 0.509s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>!spool off;

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>

In this way, you can re-direct the output of scripts executed in the snowflake database

Read More

MongoDB : How to get config file and log file location

In MongoDB configuration files and log files are important files as far as the daily routine for DBA. We may need to check parameters in the configuration and file and errors in log files. So here we will discuss where and how to check the location of these files

Configuration files (mongod.conf) 

The below command will help to list different files and their location, once you run it, the output will look like the below, but make sure you run it through the admin database, you can see highlighted part shows the location and name of the MongoDB configuration file

[Also read - Database and Collections]

db.runCommand({getCmdLineOpts:1})

> use admin

switched to db admin

> db.runCommand({getCmdLineOpts:1});

{

        "argv" : [

                "C:\\Program Files\\MongoDB\\Server\\5.0\\bin\\mongod.exe",

                "--config",

                "C:\\Program Files\\MongoDB\\Server\\5.0\\bin\\mongod.cfg",

                "--service"

        ],

        "parsed" : {

                "config" : "C:\\Program Files\\MongoDB\\Server\\5.0\\bin\\mongod.cfg",

                "net" : {

                        "bindIp" : "127.0.0.1",

                        "port" : 27017

                },

                "service" : true,

                "storage" : {

                        "dbPath" : "C:\\Program Files\\MongoDB\\Server\\5.0\\data",

                        "journal" : {

                                "enabled" : true

                        }

                },

                "systemLog" : {

                        "destination" : "file",

                        "logAppend" : true,

                        "path" : "C:\\Program Files\\MongoDB\\Server\\5.0\\log\\mongod.log"

                }

        },

        "ok" : 1

}

> 

Log file

In this same command above, you can see the parameter "systemLog" and location is also given there only, marked in red color. 

There is one other to check log file location using mongod.conf file, in the configuration file, you can below the parameter 

# where to write logging data.

systemLog:

  destination: file

  logAppend: true

  path:  C:\Program Files\MongoDB\Server\5.0\log\mongod.log

 

Read More

Granting access on dynamic views (ORA-02030: can only select from fixed tables/views)

There were situations when the application team asks for access to dynamics views such as V$SESSION or similar dynamic views. Access on V$SESSION is required in order to monitor sessions from the application end, so this is a common request

Here, we will discuss, granting access to dynamic views, if you try to grant access to these dynamic views, it will throw ORA error ORA-02030 as snipped below

SQL> grant select on v$session to tech_owner;

grant select on v$session to tech_owner

*

ERROR at line 1:

ORA-02030: can only select from fixed tables/views 

SQL> 

The meaning of this error is, we can assign select access to tables and views only. We will see the object type of this V$SESSION dynamic view 

[Also Read - Oracle SCN]

SQL> select owner, object_name, object_type from dba_objects where object_Name='V$SESSION';

OWNER        OBJECT_NAME          OBJECT_TYPE

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

PUBLIC       V$SESSION            SYNONYM

We can see, V$SESSION is neither view nor table, now we will see base object of this synonym

SQL> select owner, synonym_name, table_owner,

  2  table_name from dba_synonyms where synonym_name='V$SESSION';

OWNER        SYNONYM_NAME      TABLE_OWNER        TABLE_NAME

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

PUBLIC       V$SESSION         SYS                 V_$SESSION 

here we can see the base object for synonym V$SESSION is V_$SESSION, now we will see the type of this object V_$SESSION 

SQL> select owner, object_name, object_type from dba_objects where object_Name='V_$SESSION';

OWNER        OBJECT_NAME          OBJECT_TYPE

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

SYS          V_$SESSION           VIEW 

 We can see V_$SESSION is a view so we can grant access on V_$SESSION to get access on V$SESSION dynamic view  

SQL> grant select on v_$session to tech_owner;

 

Grant succeeded.

Read More

PostgreSQL : How to get data directory location for PostgreSQL instance

Sometimes, you start working on a PostgreSQL instance but forget about the data directory, here we will discuss different methods to know the data directory location

The database parameter related to the data file directory is "data_directory" 

Method 1: pg_settings

If you to know the value of any parameter in PostgreSQL, you can query the table pg_settings, now let's see what the columns present in pg_settings using "\d pg_settings" command

postgres=# \d pg_settings;

               View "pg_catalog.pg_settings"

     Column      |  Type   | Collation | Nullable | Default

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

 name            | text    |           |          |

 setting         | text    |           |          |

 unit            | text    |           |          |

 category        | text    |           |          |

 short_desc      | text    |           |          |

 extra_desc      | text    |           |          |

 context         | text    |           |          |

 vartype         | text    |           |          |

 source          | text    |           |          |

 min_val         | text    |           |          |

 max_val         | text    |           |          |

 enumvals        | text[]  |           |          |

 boot_val        | text    |           |          |

 reset_val       | text    |           |          |

 sourcefile      | text    |           |          |

 sourceline      | integer |           |          |

 pending_restart | boolean |           |          |

From these all columns, there are few important , i.e. name, setting, pending_restart, etc

to know data_directory location using pg_settings use below command

SELECT setting FROM pg_settings WHERE name = 'data_directory';

postgres=# SELECT setting FROM pg_settings WHERE name = 'data_directory';

               setting

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

 C:/Program Files/PostgreSQL/10/data

(1 row)

Method 2 : using show command 

most of parameters values can be retrieved using show command, one limitation is, you should know parameter name or else simply use pg_settings view with like operator

show data_directory command will give you location of data directory 

postgres=# show "data_directory";

           data_directory

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

 C:/Program Files/PostgreSQL/10/data

(1 row)

[Also Read- Physical location of datafiles in postgres]

Method 3 : configuration file

check the configuration file and find data_directory parameter value from it 

Method 4 :  using status of postgres services

using ps -ef|grep postgres on unix/linux system

Read More

Oracle SCN : System Change Number

System Change Number

SCN is a number generated in the database on the occurrence of an event in the database, the event could be

• DML statement execution
• SELECT statement execution
• DDL statement
• Commit statement

Whenever this type of transaction happens, the current timestamp converted into the system change number (SCN)

SCN is useful in MANY DATABASE SCENARIOS SUCH AS

• Instance Recovery
• Roll forward
• Rollback
• Backup and recovery 
• Read consistency

SCN is always incrementing because timestamps always increase.

Read More

Snowflake : Undrop database command

Snowflake database has a lot of features and benefits over the traditional database, one of the features is undrop database, assume you changed your mind after dropping a database, you can undo it using undrop database command,

let's see how exactly it works. this particular feature is based on the time travel feature of the snowflake, a schema, database or table can be restored within the parameter value of "data retention period", the default value of the same is 24 hours or 1 day, and it can set up to 90 days for the enterprise edition.

Undrop feature can be applied to the table, schema or database, here we will discuss database example 

 

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

| DATABASE_NAME         | DATABASE_OWNER | RETENTION_TIME |

|-----------------------+----------------+----------------|

| EXERCISE_DB           | ACCOUNTADMIN   |              1 |

| SNOWFLAKE_SAMPLE_DATA | ACCOUNTADMIN   |              1 |

| TECHNODB              | ACCOUNTADMIN   |              1 |

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

 

These are the current databases in the snowflake instance, now I will drop the technodb snowflake database

 

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>drop database TECHNODB;

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

| status                         |

|--------------------------------|

| TECHNODB successfully dropped. |

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

1 Row(s) produced. Time Elapsed: 0.610s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>select database_name , database_owner, retention_time from databases;

 

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

| DATABASE_NAME         | DATABASE_OWNER | RETENTION_TIME |

|-----------------------+----------------+----------------|

| EXERCISE_DB           | ACCOUNTADMIN   |              1 |

| SNOWFLAKE_SAMPLE_DATA | ACCOUNTADMIN   |              1 |

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

2 Row(s) produced. Time Elapsed: 0.639s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>

 

Here I have dropped a database technodb, you can from the above logs, now we have only two databases instead of three 

 

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>undrop database TECHNODB;

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

| status                                   |

|------------------------------------------|

| Database TECHNODB successfully restored. |

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

1 Row(s) produced. Time Elapsed: 0.491s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA> select database_name , database_owner, retention_time from databases;

 

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

| DATABASE_NAME         | DATABASE_OWNER | RETENTION_TIME |

|-----------------------+----------------+----------------|

| EXERCISE_DB           | ACCOUNTADMIN   |              1 |

| SNOWFLAKE_SAMPLE_DATA | ACCOUNTADMIN   |              1 |

| TECHNODB              | ACCOUNTADMIN   |              1 |

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

3 Row(s) produced. Time Elapsed: 0.783s

technosnow#TECHNO_WS@SNOWFLAKE.INFORMATION_SCHEMA>

 

Here we see, that the database has been restored back with the undrop database command, In this way, we can do it for table and schema as well. 

 

Read More

Snowflake : Using snowsql for snowflake database

A snowflake data warehouse is the latest addition to the trending database technology, In this article, we will be discussing snowsql, a command line tool used to access the snowflake database, there is another way as well to access the database i.e. web-based portal. still, there are many DBA's who love working on the command line instead of a web-based portal. 

Snowsql is supported on the below platforms 

•             Red Hat Enterprise Linux or a compatible operating system.
•             macOS (64-bit).
•             Microsoft Windows (64-bit).

snowsql installer can be downloaded using the below link

download snowsql

follow the traditional approach to install snowsql i.e. next, next and finish 

once the installation is complete, execute the below command, if it gives output instead of error then your snowsql installation is successful

C:\Users>snowsql -v

Version: 1.2.23

C:\Users>

we can see, that snowsql is installed and the version is 1.2.23 

in order to login into the snowflake database, you need to have an account name, username, and password

also read know your snowsql account name

Read More

PostgreSQL : Types of Shutdown

There are different ways to make a database shutdown for every database type whether it is MySQL, Oracle, MongoDB, etc, in PostgreSQL as well, there are 3 types of shutdown based on the signal provided

1.    SIGTERM

2.    SIGINT

3.    SIGQUIT

 

We will discuss them one by one

SIGTERM

This type of shutdown is identified as a smart shutdown

After receiving a shutdown signal, SIGTERM 

disallows new connections

let’s existing connection works normally 

shuts down only after sessions are terminated 

If the server is in online backup mode

It additionally waits until the online backup mode is no longer active, new connections still are allowed for superusers only, as they might need to send a request to disable the are online backup mode

If the server is in recovery mode

The recovery process will be stopped only after all regular sessions are terminated.

 

SIGINT

This mode is termed fast shutdown mode

once a SIGINT signal is sent, it

disallows new connections and sends all existing server processes SIGTERM, which makes them abort respective current transactions and exit promptly 

waits until all server process to exit and finally shuts down. 

if the server is in online backup mode

the backup mode will be terminated and the backup will be made useless

SIGQUIT

This mode is termed immediate shutdown mode

The server will send SIGQUIT to all the child processes and wait for them to terminate  and if it is not terminated within 5 sec, it sends SIGKILL

After forcing SIGQUIT,

the database needs recovery on the next startup

only recommended in case of emergency. 

Read More

PostgreSQL : How to check parameter values in postgresql

As part of some basic administration in PostgreSQL databases, we will learn how to check and modify parameters 

there are a few ways to do it

• using the "Show" command
• querying pg_settings catalog
• by directly checking configuration files

 

Assume you need to check the parameter related to the hba_configuration file, like the location of this file but you are not  sure about the parameter name, I just tried with a few options, and it was not giving me proper value, however, it was throwing syntax error like below

 

postgres-# show hba_config;

ERROR:  syntax error at or near "show"

LINE 2: show hba_config;

        ^

postgres=#

 

postgres=# show hba;

ERROR:  unrecognized configuration parameter "hba"

postgres=#

 

for using the "show" command, you must know the exact name of the parameter, or else you can simply use pg_settings catalog to fetch it, I just tried the "like" parameter in pg_settings

 

postgres=# SELECT name, setting, reset_val  FROM pg_settings where name like '%hba%';

   name   |                     setting                     |                    reset_val

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

 hba_file | C:/Program Files/PostgreSQL/10/data/pg_hba.conf | C:/Program Files/PostgreSQL/10/data/pg_hba.conf

(1 row)

 

now you can check using the "show" command

 

postgres=# show hba_file;

                    hba_file

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

 C:/Program Files/PostgreSQL/10/data/pg_hba.conf

(1 row)

 

 

postgres=# show max_connections;

 max_connections

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

 100

(1 row)

 [Also Read - Installation of PostgerSQL]

Read More