PostgreSQL – Useful Links

PostgreSQLUseful Links

Here are some PostgreSQL references, which may be useful.

Reference Type Link
PostgreSQL Home page & Download https://www.postgresql.org/
PostgreSQL Online Documentation https://www.postgresql.org/docs/manuals/
Citus – Scalability and Parallelism Extension https://github.com/citusdata
Free PostgreSQL Training https://www.enterprisedb.com/free-postgres-training

Related References

The Business Industries Using PostgreSQL

PostgreSQL is an open-source database, which was released in 1996. So, PostgreSQL has been around a long time.   So, among the many companies and industries which know they are using PostgreSQL, many others are using PostgreSQL and don’t know it because it is embedded as the foundation in some other application’s software architecture.  

I hadn’t paid much attaint to PostgreSQL even though it as been on the list leading databases used by business for years.  Mostly I have been focused on the databases my customer were using (Oracle, DB2, Microsoft SQL Server, and MySQL/MariaDB).  However, during a recent meeting I was surprised to learn that io had been using and administering PostrgresSQL embedded as part of another software vendors application, which made me take the time to pay attention to PostgreSQL. Especially, who is using PostgreSQL and what opportunities that may provide for evolving my career? 

The Industries Using PostgreSQL

According to enlyft, the major using the PostgreSQL are Computer Software and Information Technology And services companies.  

  PostgreSQL Consumers Information

Here is the  link to enlyft page, which provides additional information companies and industries using PostgreSQL:

Related References

The Most Important Databases In Business

If you are looking to learn a Structured Query Language (SQL) skills and have marketable database knowledge, then it is time to talk about which are the most important business databases.

Why Learn The Most Important Databases Used By Business Are?

Well, I assume that you are reading this article with an eye toward improving your employment prospects.   If improving your employment prospects is what you are after, then why waste time learning databases skills, learning advanced SQL language chrematistics of a database, or get after a certification, which will have a small market?  The most important or most used databases, if you like, to business will likely be where the most work opportunities will be no matter whether you plan to be a company employee, independent contractor, consultant or like me have moved through all of these at one time or another.

Focus On Market Opportunity

The top five databases have a solid 85% of the market, so, this allows a person to build their skill is a database which will have employment opportunities.  While there are non-business users of databases of other databases not discussed here, most of us will, thorough out our careers, be working in work for or with business to earn our living.  

The Top Five Database Most Popular In Businesses

Now to the heart of this article, in 2019 the top five databases used in a business are listed in order of most popular to least popular are:

Database Name Percent of market
Oracle 25.31%
MySQL 24.04%
Microsoft SQL Server 20.39%
PostgreSQL 9.06%
MongoDB 7.71%

Related References

How To Get A List Of Oracle Database Schemas?

Well, this is one of those circumstances, where your ability to answer this question will depend upon your user’s assigned security roles and what you actually want. 

To get a complete list, you will need to use the DBA_ administrator tables to which most of us will not have access.  In the very simple examples below, you may want to add a WHERE clause to eliminate the system schemas from the list, like ‘SYS’ and ‘SYSTEM,’ if you have access to them.

Example Administrator (DBA) Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM  DBA_OBJECTS

ORDER BY OWNER;

Example Administrator (DBA) Schema List Results Screenshot

Fortunately for the rest of us, there are All user tables, from which we can get a listing of the schemas to which we have access.

Example All Users Schema List

SELECT distinct OWNER as SCHEMA_NAME

FROM    ALL_OBJECTS

ORDER BY OWNER;

Example All Users Schema List Results Screenshot

Related References

Oracle help Center > Database> Oracle > Oracle Database > Release 19

Oracle SQL*Plus Is still Around

It is funny how you cannot work with some for a while because of newer tools, and then rediscover them, so to speak.  The other day I was looking at my overflow bookshelf in the garage and saw an old book on Oracle SQL*Plus and was thinking, “do I still want or need that book?”. 

In recent years I have been using a variety of other tools when working with oracle. So, I really hadn’t thought about the once ubiquitous Oracle SQL*Plus command-line interface for Oracle databases, which around for thirty-five years or more.  However, I recently needed to do an Oracle 18C database install to enable some training and was pleasantly surprised Oracle SQL*Plus as a menu item. 

Now I have been purposely using Oracle SQL*Plus again to refresh my skills, and I will be keeping my Oracle SQL*Plus: The Definitive Guide, after all. 

Oracle Express Edition Error – ORA-65096: invalid common user or role name

While trying to create user in Oracle Database 18c Express Edition I kept getting an “ORA-65096: invalid common user or role name” error, which didn’t make sense to me so after validating my command, that I was signed in as an admin user, and determining that my “CREATE USER” was formatted correctly.  I did some additional research and determined that in the hidden parameter “_ORACLE_SCRIPT” needed to be set to “True” starting with the Oracle Version 12c and higher.

Setting the “_ORACLE_SCRIPT” values

To set the “_ORACLE_SCRIPT” hidden variable to “True” you need to run an “Alter” command. Then you will be able to create the desired user and run your grants commands as usual.

Alter SQL Command

alter session set “_oracle_script”=true;

How to Determine Your Oracle Database Name

Oracle provides a few ways to determine which database you are working in.  Admittedly, I usually know which database I’m working in, but recently I did an Oracle Database Express Edition (XE) install which did not goes has expected and I had reason to confirm which database I was actually in when the SQL*Plus session opened.  So, this lead me to consider how one would prove exactly which database they were connected to.  As it happens, Oracle has a few ways to quickly display which database you are connected to and here are two easy ways to find out your Oracle database name in SQL*Plus:

  • V$DATABASE
  • GLOBAL_NAME

Checking the GLOBAL_NAME table

The First method is to run a quick-select against the GLOBAL_NAME table, which. is publicly available to logged-in users of the database

Example GLOBAL_NAME Select Statement

select * from global_name;

Checking the V$DATABASE Variable

The second method is to run a quick-select a V$database. However, not everyone will have access to the V$database variable.

Example V$database Select Statement

select name from V$database;