PostgreSQL : How to describe table in psql

For the DBA's using a conventional database such as oracle, they have a habit of using DESC or DESCRIBE but in PostgreSQL, it's in different ways, we will discuss the same here

There are 3 ways to describe a table

• using \d
• using \d+
• using view INFORMATION_SCHEMA.COLUMNS

 [Also Read- Basic Administration in PostgreSQL]

suppose you want to see the columns in the pg_roles table from the PostgreSQL database

\d  will details of columns in particular table as shown below 

\d+ is an advanced version of \d, it provides you the definition of the table as well. 

You have another way to describe a table i.e. using SQL query on INFORMATION_SCHEMA.COLUMNS catalog, there are a number of columns in this table, you can limit what you want to see, select * will  give full details, however, I will only select the column name and its data type 

select * from INFORMATION_SCHEMA.COLUMNS;

select column_name, data_type from INFORMATION_SCHEMA.COLUMNS where table_name = 'pg_roles';

Read More

Oracle : ORA-01017, ORA-02063 preceding line from DATABASE_LINK

while creating a database link, I accidentally provided the wrong password of the remote user and while accessing the database link, it got the below error

 

SQL> select * from TECH_AS_OWNER.AS_GARD@asgard_link;

select * from TECH_AS_OWNER.AS_GARD@asgard_link

                                    *

ERROR at line 1:

ORA-01017: invalid username/password; logon denied

ORA-02063: preceding line from ASGARD_LINK

 

Error is quite clear as the password is invalid for a remote account, you have a couple of options to correct it, 

one way is to drop the existing database link and create it again and the other way is to alter the database link, 

 

we will discuss the second option, here i.e., altering the database link.  

to alter the database link, you must have certain privileges as mentioned in the below table 

 

Type of database link	Privilege required
Private	ALTER DATABASE LINK
Public	ALTER PUBLIC DATABASE LINK

 

Below are the commands to alter the database link 

--grant privilege alter database link

SQL> grant alter database link to tech_owner;

 

SQL> select grantee, privilege from dba_sys_privs where grantee='TECH_OWNER'; 

GRANTEE                        PRIVILEGE

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

TECH_OWNER                     ALTER DATABASE LINK

TECH_OWNER                     CREATE DATABASE LINK

 

SQL> ALTER DATABASE LINK asgard_link

  2  CONNECT TO TECH_AS_OWNER IDENTIFIED BY User#123; 

Database link altered.

 

--verify if database link is working

 

SQL> select sysdate from dual@asgard_link; 

SYSDATE

---------

29-AUG-22 

 

Similarly we can perform activity for public database link

Read More

Oracle : Database Links - Create, Use and Drop

A database link is an important schema object in the oracle database, we should know details about it in order to implement it in real-time applications,

Assume you have two different applications with two different databases, and while generating reports, you need to fetch and merge data from both the database, it’s a good idea to use a database link, i.e. connecting 2 databases together

We will discuss more

Generally, there are 2 types of database links,

• Public 
• Private 

Public - database link, which can be accessed by all users in the databases, 

Private- database link, which can be accessed by only specific users

 The syntax for creating database links is quite similar 

 create [Public/private] database link <link name>

 Explanation with example 

 Assume below details 

	Database	Schema	Connection link
Source details	technodb	tech_owner	technodb
Target details	asgard	tech_as_owner	asgard

 We will be creating database link asgard_link in database technodb, connecting to the asgard database, and will try to fetch data from a remote database

Create a private database link

Connect to the database using userid tech_owner and execute create database link command, before executing the command remember that, you need to have to create database link privileges assigned.

SQL> select grantee, privilege from dba_sys_privs where grantee='TECH_OWNER';

 

GRANTEE                        PRIVILEGE

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

TECH_OWNER                     CREATE DATABASE LINK

You can see, tech_owner has create database link privileges

Now create a private database link 

SQL> show user

USER is "TECH_OWNER"

SQL> create database link asgard_link

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard';

 

Database link created.

 

Here,

asgard_link – is the database link name

tech_as_owner is a remote user and its password

‘asgard’ – connection string to connect remote database 

Verifying if the database link is working

Execute select query to fetch data from remote database using db link, if returns value, it working fine or in case of error, need to rectify

 SQL> show user

USER is "TECH_OWNER"

SQL> select sysdate from dual@asgard_link;

SYSDATE

---------

29-AUG-22

 

Create a public database link

Similarly, we can create a public database link using the below statements, make sure you have create public database link  privilege granted

SQL> grant create public database link to tech_owner; 

Grant succeeded.

SQL> show user

USER is "TECH_OWNER" 

SQL> create public database link asgard_link_pub

  2  connect to tech_as_owner identified by User#123

  3  using 'asgard'; 

Database link created.

 Now try accessing it through different accounts, you will be able to fetch the data,

SQL> show user

USER is "TECH_OWNER"

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22

SQL> conn /as sysdba

Connected.

SQL>  select sysdate from dual@ASGARD_LINK_PUB ; 

SYSDATE

---------

29-AUG-22 

How to drop database link

to drop the private database link, you need to connect using a username account and then execute the drop command,

SQL> show user

USER is "TECH_OWNER"

SQL> drop database link ASGARD_LINK; 

Database link dropped.

 To drop the public database link, you need to mention the public identifier, like below

 SQL> drop public database link ASGARD_LINK_PUB ; 

Database link dropped.

 

Read More

Snowflake : Different editions of snowflake

 As we know, the snowflake is a very hot data warehouse technology today. we will see a different aspect of a snowflake in different articles, here we will discuss its different editions available for use. 

The below image gives an idea about different editions of snowflake

so basically, there are a total of 4 editions of snowflake, in the trail account setup, snowflake won't show you about the fourth edition i.e. Virtual Private, as this is the very highest level of secure edition and is available for paid versions only. 

Standard Edition: This is a basic, introductory edition, standard features are available, not available for large-scale companies, and is enough for small-scale industries and educational institutes

Features (not limited to this) 

 

• automatic data encryption, 
• data warehouse support, 
• time travel up to 1 day 
• disaster recovery up to 7 days
• secure data share, 
• premium support 24/7

 

Enterprise Edition:  This is designed for large-scale industries with all features of the standard level edition 

Features

• All benefits of the standard edition 
• Multi-cluster data warehouse
• time travel- up to 90 days
• materialized views support
• column level security 

[Also read: Snowflake account name]

Business Critical:  with all the features of the enterprise edition, this business-critical edition is designed for the highest data protection and is good for industries with very highly sensitive data such as banking 

 

Features 

• All enterprise features 
• extended support

Virtual Private large: This level of edition comes with the highest level of protection and security
Features 

• All business-critical level features
• dedicated virtual servers 

[Also Read -Snowflake: Introduction and Architecture]

Read More

Snowflake : Introduction and Architecture

Snowflake is a very trending data warehouse technology due to its benefits and features over traditional database technology, here we will have a brief introduction to snowflake data warehouse and its architecture 

Snowflake is true software as a service provided with an advanced data platform.    

snowflake does not use traditional database technology; it is designed with a new SQL engine with innovative architecture designed for the cloud.

snowflake is a completely cloud-based data warehouse technology 

 

Now we will see why snowflake is a true SaaS (Software as Service) 

• There is no hardware to select, install, configure or manage 
• No software to install, configure or manage
• maintenance, upgrade, and patching all is handled by snowflake itself

 

Snowflake cannot be run on an on-premise or hosted server

There are two main parts of the snowflake 

• Compute - to fulfill the need for resources to compute or to perform operations
• Storage - service to store data

  

Snowflake Architecture 

Snowflake architecture consists of three key layers  

1. Storage 
2. Query processing
3. Cloud services 

 

Database Storage 

The database storage layer is basically to store and manage snowflake data, snowflake reorganizes data into internally optimized, compressed, and columnar formats. snowflake stores data in cloud storage such as AWS, GCP, or Azure.

data objects managed and stored by snowflake not are directly visible or accessible by customers of snowflake

 [Also read -How to get account name in snowflake ]

Query Processing (muscle of the system)

The query processing layer performs the execution of queries, this layer consists of virtual warehouses, Virtual warehouse is a virtual compute resource, that can be used to process all of the queries.

each virtual house is independent of the other, so the performance of one virtual warehouse does not impact the other's performance 

 

Cloud Services (the brain of the system) 

This cloud service layer is the combination of different services that are carried out across snowflake. 

Below are the services managed by this cloud service layer

• access and authentication
• infrastructure management
• metadata management
• query parsing and optimization
• security

[Also read -Setting AWS demo account]

Read More

AWS : Setup free demo account

We have discussed the basics of AWS in a previous article, now let's create an amazon web service free demo account

Pre-requisites 

• A valid email id
• A valid payment option such as a credit or debit card

once you are ready, open https://aws.amazon.com in any web browser and click on Create an AWS account

• On the next screen, put the email id and account name.

• On the next screen, amazon asks to verify the email id, and enter the verification code, once you enter the verification code, you need to set a password

• In the next step, enter the below details

• full name
• phone number
• country
• address
• and tick the terms and conditions. 

• On the next page, we need to enter billing information, such as credit/debit card and your PAN is optional here
• On next page, confirm your identity using a phone text message or phone voice call

• On the next page, select your plan

• Once done, you are all set to use amazon web services

'

now you can simply log in to AWS services using aws.amazon.com and click on sign in option and enter the details

Read More

Flashback query in oracle

You must have heard about the time machine in movies or magazines, just think of an oracle providing the same feature with its databases, and that is called a flashback. 

We can go back in time to query data from the table, of course, there are certain limits to it, but it’s possible, we will learn this with an example. 

Here is the table employee, with 8 rows 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

and this particular snapshot is captured at the below timings 

 

SQL> select sysdate from dual;

 

SYSDATE

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

2022-07-21 23:37:12

 

Now, deleted a couple of rows using the below query

 

SQL> delete from employee where emp_id>305;

 

2 rows deleted.

 

SQL> commit;

 

Commit complete.

 

now we can see number of rows in the table 

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         6

 

so basically, 2 rows are deleted from the table, what if you want to read those 2 deleted rows, you have the option to use the flashback query option, below is the exact format to achieve it

Check the number of rows based on timestamp, as we saw we had 8  rows preset in the table at 23:37 but later we deleted them, based on this timing, check the number of rows

 

SQL> select count(*) from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS');

 

  COUNT(*)

----------

         8

 

see here, we were able to fetch data based on the flashback option, this is just reading, what if you want to insert data back to the table, use the below commands

 

SQL> insert into employee select * from employee as of timestamp TO_DATE('2022-07-21 23:37:12','YYYY-MM-DD HH24:MI:SS') where emp_id>305;

 

2 rows were created.

 

SQL> commit;

 

Commit complete.

 

now check the count in the table, you will be surprised to see, that data is inserted back to the table.

 

SQL> select count(*) from employee;

 

  COUNT(*)

----------

         8

 

Read More