Processing select statement in oracle database

Understanding the execution of any query in the Oracle database needs an understanding of different components of the Oracle database such as SGA, Processes, datafiles, memory, etc.

Here, we will discuss the execution of a simple select query like “select * from

 hr.emp

First of all, a connection to the database is required, and to achieve that you need to know database connection details such as port number, hostname, sid, etc. Once you have these details, the user will log in to the database and a connection is established using the listener.

Now user can execute any query, the query sent by the user once reaches the database, gets picked up by the server process, and from here onwards, processing will be carried out by the server process.

The server process will create a hash for the SQL query sent by the user, the hash is just hexadecimal coding in the form of SQL id and SQL hash value.

Now server process searches in the shared pool and checks if any SQL area is present for the user query, if SQL area is already present for this query, if the result finds in SQL area, data will be returned to the user from memory itself, no further process required.

In case the SQL area is not present for this SQL query, that means the query is getting executed the first time in the database. The server process will start building SQL area for the query

Processing of the select query is taken in 3 main parts

• Parsing
• Execution
• Fetching

Parsing

Parsing is also known as validation of the query sent by the user, it will be done in three steps

• Syntax check
• Semantics check
• Privilege check

Syntax Check

            Here, the syntax of the SQL query will be verified as per SQL standards and keywords, sometimes we misspell some keywords such as “SELECT” is written as “SLECT” or “SELCT”, syntax check will throw an error, once the syntax check is passed, it moves to semantic check

Semantic check

            In semantic check, the server process verifies the structure of the query, i.e., columns names, tables, or schema names used will be verified with the help of a dictionary cache

Privilege check

In this stage of parsing, privileges on users will be verified, the client is executing the query using USER1 schema and accessing the table from USER2, so the server process will check if USER1  has access to USER2

Once all these checks are verified as part of the parsing, it will start the execution of the SQL query based on the SQL plan described by OPTIMIZER, OPTIMIZER is an internal utility in the database, that keeps all the statistics of the database objects

The server process will bring data in the buffer cache i.e., memory from datafiles, once data is loaded in the memory, a complete SQL area will be created and the server process will return data to the user, this completes the processing of the SQL query. so, processing the query involves a lot of components of the database

Read More

Snowflake : System defined roles in Snowflake

There are a total 5 default system-defined roles in snowflake, those are

ACCOUNTADMIN

SYSADMIN

SECURITYADMIN

USERADMIN

PUBLIC

Below is a graphical hierarchy of these roles in snowflake

ACCOUNTADMIN is a top-level role in snowflake architecture, it has privileges of both SYSADMIN and SECURITYADMIN

PUBLIC is a lower-level role in snowflake and USERADMIN role inherits all the privileges of PUBLIC and passes them on to SECURITYADMIN

 

Here we will discuss, what users can do when these roles are assigned.

 

ACCOUNTADMIN

inherits privilege of SYSADMIN and SECURITYADMIN

It should be granted to very limited users, and users with this role can do anything with snowflake objects

 

SECURITYADMIN 

USERADMIN role is granted to SECURITYADMIN role

user with this role can manage users and roles in snowflake architecture

user can manage any object grants globally

 

SYSADMIN 

user with this role can create data warehouses and databases 

once custom roles are created, need to assign it to SYSADMIN role

 

USERADMIN

user level role and assigned to individual users 

user with this role can create users and roles

 

PUBLIC

this role is automatically assigned to every user

user will be able to create their own objects

Read More

PostgreSQL : How to check size of the database

As a PostgreSQL database administrator, you may come across situations where you want to check the size of the full database, here is a quick solution for the same

Here is my PostgreSQL cluster details, there is one database called technodb, and I need to get the size of this database, here is how we can check

There is a command called pg_database_size('<database name>'), here is how we can execute it

postgres=# select pg_database_size('technodb');

 pg_database_size

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

          7773319

(1 row)

by default, it will result in bytes and if you want to bring a result in megabytes (MB), here is how you can do it 

postgres=# select pg_database_size('technodb')/1024/1024;

 ?column?

----------

        7

(1 row)

There is another way to check the size of the database, just execute the command \l+ <database name>

postgres=# \l+ technodb

                                                         List of databases

   Name   |  Owner   | Encoding |      Collate       |       Ctype        | Access privileges |  Size   | Tablespace | Description

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

 technodb | postgres | UTF8     | English_India.1252 | English_India.1252 |                   | 7591 kB | pg_default |

(1 row)

and if you want to get the size of all databases together, here is how you can fetch details using pg_database view

 

postgres=# select datname as Database , (pg_database_size(datname))/1024/1024 as Database_size_MB from pg_database;

 database  | database_size_mb

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

 postgres  |                7

 mydb      |                7

 template1 |                7

 template0 |                7

 technodb  |                7

 

[Also read - Types of shutdown in PostgreSQL database]

Read More

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