> Quizzes > Quizzes 2023 -- Oracle / Postgresql -- Best Pratices / How to

In the page Prices and Donations, you can find how to have the solutions of the quizzes.

Nella pagina Prezzi e Contatti potete vedere come avere le soluzioni dei quiz.

If it is not specified, the commands must be executed connected as a database "superuser".
About the solutions:
- could be more than one. Here is one solution that often takes into account of the performance tuning
- they could change because of the database version
- it is not warranted to be error-free in all the database environments

November - Postgres dictionary

Level (*) : 2
Topic (*) : Postgres

In your database cluster (postgres version 13), you have a database called DB1 with owner USER_1.
You want to duplicate it in the same database cluster so you launch this command:
  OWNER user_1;
After this operation you see that in the DB1 there is a search_path and in DB2 there isn't.

You have to write a SQL query (from Postgresql dictionary) in order to get a result like this:
  ALTER DATABASE db1 SET search_path TO user_1;
You'll modify it (and you'll launch it) in this way:
  ALTER DATABASE db2 SET search_path TO user_1;

October - Oracle DUAL table

Level (*) : 2
Topic (*) : Oracle

Using the DUAL table you see some strange behavior.
So you see this (these SQL statements are launched from SQL*Plus):
  SQL > create table prova  as select * from dual;

  Table created.

  SQL > select * from prova;


  SQL > select * from dual;

Our Dual has 1 or 2 rows?
How is it possible to get this situation?
How can you solve it?

September - Postgres boolean values

Level (*) : 2
Topic (*) : Postgres

You have a postgres table with a boolean column data type.
You want to insert some boolean-ish values on it.

What is its behavior when you want to insert values such as:
0, 1, 2, 99, t, 't', true ?

August - Postgres physical backups checks

Level (*) : 2
Topic (*) : Postgres

You have a postgres database cluster and you do the physical backups of it by using the pgbackrest tool.
You want to check that its WAL files are being archived in the correct way.

So you want 2 outputs like these:
  Service        : WAL_ARCHIVES
  Returns        : 0 (OK)
  Message        : 7 WAL archived
  Message        : latest archived since 6m8s
  Long message   : latest_archive_age=6m8s
  Long message   : num_archives=7
  Long message   : archives_dir=/opt/postgres/pgbackrest_repo/archive/sta/13-1
  Long message   : min_wal=000000010000000000000015
  Long message   : max_wal=00000001000000000000001B
  Long message   : latest_archive=00000001000000000000001B
  Long message   : latest_bck_archive_start=00000001000000000000001B
  Long message   : latest_bck_type=full
  Long message   : oldest_archive=000000010000000000000015
  Long message   : oldest_bck_archive_start=000000010000000000000015
  Long message   : oldest_bck_type=full

  WAL_ARCHIVES OK - 7 WAL archived, latest archived since 6m8s | latest_archive_age=368s ...

The first output is useful for "human" reading.
The second output (here shortened) can be used by Nagios tool.

Which tool can you use to have these outputs?
And which commands can provide the above outputs?

July - Oracle SQL*Plus commands

Level (*) : 1
Topic (*) : Oracle

You are connected to the database thru the SQL*Plus tool.
You want to see the username you are using and the linesize of your connection, so for example you want results like these:
 USER is "SYS"
 linesize 220


To get this information you can use the SHOW (or SHO) command like this:
 SQL > sho user
 USER is "SYS"

 SQL > sho lin
 linesize 220
And SHOW ALL lists the settings of all SHOW options, except ERRORS and SGA.

June - Oracle X$ tables

Level (*) : 2
Topic (*) : Oracle

Sometimes, objects such as DBA_TABLES are called system tables.
But actually the DBA_TABLES is a system view based on system tables such as sys.user$, sys.ts$ and other objects such as x$ksppcv, x$ksppi.

In the DBA_OBJECTS, DBA_TABLES, DBA_INDEXES you can't find objects like x$ksppcv and x$ksppi.
Why? Where you can find the list of the objects like them?

May - Oracle CDB or non-CDB

Level (*) : 3
Topic (*) : Oracle

You have a lot of databases, some of them are CDB, some are non-CDB.
You have to write a query in order to have these 3 possible results (it is important last row, here called "ENV"):
  NAME         VAL
  ------------ -------------------------------------
  Conn Name    CDB$ROOT
  CDB Name     ORCDB
  ENV          DB multitenant. Connected to CDB$root

  NAME         VAL
  ------------ -------------------------------------
  Conn Name    ORCLPDB1
  CDB Name     ORCDB
  ENV          DB multitenant. Connected to a PDB

  NAME         VAL
  ------------ -------------------------------------
  Conn Name    ORCLCDB
  CDB Name
  ENV          DB non multitenant
This query can be launched from ANY user (so you don't need to be a "DBA").
The first 3 rows are just examples and they are directly extracted from the database, last row ("ENV") is calculated using a DECODE (or a CASE).

April - Postgresql connections

Level (*) : 2
Topic (*) : Postgres

You have a database cluster that contains many databases.
Regarding its database called db01, how can you check the maximum number of concurrent connections that can be made to it?
And how can you set this value to 50 concurrent connections?

March - Oracle users

Level (*) : 2
Topic (*) : DBA

You are connected in a non-CDB database and in it there are 2 application users called U1 and U2.

Looking at the DBA_USERS you get this information:
  ------------ -----------------
  U1           N
  U2           Y
But both users weren't created by Oracle-supplied scripts.
The U2 user has wrongly ORACLE_MAINTAINED = Y.

How was possible to create an Oracle user like this ?
Is there a way to change that user in order to have ORACLE_MAINTAINED = N ?

February (2) - Index issue using Oracle datapump

Level (*) : 3
Topic (*) : DBA

In Oracle, you do an export (expdp) of one schema and the impdp of it in another database.

The export and the import logs are without errors but you want to check if all the objects have actually been copied.
Looking at the DBA_OBJECTS, you find out that you have all the constraints but the target database has not some indexes existing in the source database.

This can be not a real problem.

BTW You have to find:
1. Which could be the reason? And which MOS Oracle note write about that issue?
2. How can you find out all the indexes could have this issue (without checking all the index names)?
3. Say 2 methods to avoid it next time you'll do an impdp? And which MOS Oracle note write about these methods?

February - LOB issue using Oracle datapump

Level (*) : 2
Topic (*) : DBA

In Oracle, you do an export (expdp) of one schema and the impdp of it in another database.

After these operations, you check if all the objects have been copied.
Looking at the DBA_OBJECTS, you find out that the target database has not some LOBs existing in the source database.

Probably the most common reason of that is existing from Oracle

You have to find:
1. Which could be this reason? and why looking at the DBA_OBJECTS this issue happens from and not before?
2. Looking at only the source database, how can you find out the LOBs could have this issue?
3. How can you avoid it next time you'll do an expdp?

We are not in the case described in the following MOS Oracle note:
Orphaned Lobs after marking LOB column unused (Doc ID 461651.1)

January - Oracle SQL hints

Level (*) : 2
Topic (*) : DBA / Progr

In Oracle, you have this table and indexes:
    empno    INTEGER,
    ename    VARCHAR(10),
    job      VARCHAR(9),
    mgr      INTEGER,
    hiredate TIMESTAMP,
    sal      NUMERIC,
    comm     NUMERIC,
    deptno   INTEGER,
    constraint emp_pk primary key (empno)

  CREATE INDEX emp_idx
  ON emp (ename);
You want to launch this SQL statement:
  FROM emp e
  WHERE ename like '%po%' and empno in (1,2,3);
And you see that its execution plan is using the EMP_PK index but you don't like it.

So, you have to re-write 2 times the above SELECT using hints:
- one query must use one hint in order to use the EMP_IDX index
- the other one must use another hint in order to have a Table Access Full on the EMP table

And, in SQL*Plus, how can you check these 3 execution plan?

1: easy solution
2: medium solution
3: hard (or long) solution

quiz usually suitable for Oracle DBAs
Progr: quiz usually suitable for PL/SQL or SQL Programmers
DBA/Progr: quiz suitable for Oracle DBAs and for Programmers
Postgres: regarding the Postgresql database


This website is not connected with Oracle Corporation or the official Oracle website.
Some words used here are registered trade marks of their owners.