Manuali Oracle - Quizzes 2022

ManualiOracle.it

> Quizzes > Quizzes 2022 -- 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

December - Postgres sequence

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

In your postgres installation, you have the sadmin and sowner users.
The sadmin user is connected to a database called db10 and launch this:

  create sequence seq_prova;

You want that sowner that use this sequence.
How can do it?
And which query should do sowner in order to get the "next value" of that sequence?

November - Oracle RAC connection

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

You want to increase the maximum number of O.S. user processes that can simultaneously connect to the database so you decide to change the PROCESSES initialization parameter:

  alter system set processes=2600 scope = spfile;

After that, you do the db bounce: shutdown and startup. But you receive this error:

  SQL> startup
  ORA-00093: pga_aggregate_limit must be between 7800M and 100000G
  ORA-01078: failure in processing system parameters

Why pga_aggregate_limit? And why "7800M"?
How can you solve that issue in order to startup again the database?

October - Oracle secure database connection

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

You have created an Oracle 19c database in an Oracle Linux 8 machine.
And you have an Oracle client in your Windows machine.

Between these machines, you want to have a secure database connection using TCPS protocol (TCP/IP with SSL/TLS).
So you configure wallets, certificates (and exchanging them), Oracle server files and client server files.

Now you have to open the port you want to use for the secure connection (for instance, the port 2484).
Which commands you have to launch in the server in order to open it in a permanent way?

After that, how can you check that the connections are actually using the TCPS protocol ?
Write one check to do in the server and one check to do in the client.

September - Oracle RAC connection

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

You are connected into an Oracle RAC.
This database is composed by 2 instances.
You have to find a single query in order to get info regarding both instances and in which of those you are currently conneced.

Result example:

 INST_ID  INSTANCE_NAME  PARALLEL  ARCHIVER STARTUP          HOST_NAME      NOTE
--------- -------------- --------- -------- ---------------- -------------- ------------------
  1       db21           YES       STARTED  24/09/2022 11:48 host1.example  -> Current Instance
  2       db22           YES       STARTED  24/09/2022 11:48 host2.example

August - Oracle analytic function

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

You have 3 tables: they say in which area (continent) are the countries:

 CREATE TABLE countries
 (id  INT PRIMARY KEY,
  name  VARCHAR2(30));

 CREATE TABLE areas
 (id  INT PRIMARY KEY,
  name  VARCHAR2(30));

 CREATE TABLE countries_where
 (id  INT PRIMARY KEY,
  id_country INT,
  id_area  INT,
  CONSTRAINT fk1  FOREIGN KEY (id_country) REFERENCES countries,
  CONSTRAINT fk2  FOREIGN KEY (id_area) REFERENCES areas);


 INSERT INTO countries  VALUES (1, 'Italy');
 INSERT INTO countries  VALUES (2, 'Russia');
 INSERT INTO countries  VALUES (3, 'China');
 INSERT INTO countries  VALUES (4, 'Egypt');

 INSERT INTO areas  VALUES (1,'Europe');
 INSERT INTO areas  VALUES (2,'Asia');
 INSERT INTO areas  VALUES (3,'Africa');

 INSERT INTO countries_where  VALUES (1, 1, 1);
 INSERT INTO countries_where  VALUES (2, 2, 1);
 INSERT INTO countries_where  VALUES (3, 2, 2);
 INSERT INTO countries_where  VALUES (4, 3, 2);
 INSERT INTO countries_where  VALUES (5, 4, 2);
 INSERT INTO countries_where  VALUES (6, 4, 3);

You have to write a SQL query in order to have all the countries and its areas.
The result must be this:

 Countries and Areas
 ---------------------
 China: Asia
 Egypt: Africa, Asia
 Italy: Europe
 Russia: Asia, Europe

So we want 2 different SORTs:
- one by country name
- one by area name (for each country with more than one area)

July - Oracle Json data type

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

You are connected in an Oracle 19c database.
You try to create this table:

  SQL> CREATE TABLE t1
    2      (c1 INT, c2 JSON);
      (c1 INT, c2 JSON)
                  *
  ERROR at line 2:
  ORA-00902: invalid datatype

But you receive that error.
Why? How can you avoid it?
Is there some other Oracle version where you can launch this SQL?

June - Postgresql dump

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

Inside a database called db_reporting, you have a schema created like this:

  create schema "LASSI";

In it, you have a table called JOU.
You want to do a dump of this table:
Connected as postgres O.S. user, you try:

  pg_dump -t lassi.jou db_reporting > jou.dmp
  pg_dump -t LASSI.jou db_reporting > jou.dmp
  pg_dump -t "LASSI".jou db_reporting > jou.dmp
  pg_dump -t 'LASSI.jou' db_reporting > jou.dmp

But you receive always:

  pg_dump: error: no matching tables were found

Which is the right syntax to do it?

May - Postgresql migration

Level (*) : 3
Topic (*) : Postgres

In the srv_1 server, you have a postgres database cluster in postgres 11.
In it, you have a small database called db_x. Its owner is a database user called user_x

In the srv_2 server, you have a postgres database cluster in postgres 12.
It is empty: just having the default users and databases.

You want to migrate db_x from srv_1 to srv_2.
In order to do it, you decide to use pg_dumpall, pg_dump and psql commands.

Which steps should do?

April - Postgresql system catalog

Level (*) : 3
Topic (*) : Postgres

In your postgres database db_reporting, you have a partitioned table with 2 indexes:

  CREATE TABLE log_ms
  (  id  SERIAL
    ,dt_insert  TIMESTAMP
    ,source   VARCHAR (255)
    ,step VARCHAR (30)
  )
  PARTITION BY RANGE (dt_insert);

  CREATE TABLE log_ms_202201  PARTITION OF log_ms
  FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');

  CREATE TABLE log_ms_202202  PARTITION OF log_ms
  FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
  
  CREATE TABLE log_ms_202203  PARTITION OF log_ms
  FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');

  CREATE TABLE log_ms_202204  PARTITION OF log_ms
  FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');

  CREATE TABLE log_ms_202205  PARTITION OF log_ms
  FOR VALUES FROM ('2022-05-01') TO ('2022-06-01');

  CREATE UNIQUE INDEX  ON log_ms_202201 (id);
  CREATE UNIQUE INDEX  ON log_ms_202202 (id);
  CREATE UNIQUE INDEX  ON log_ms_202203 (id);
  CREATE UNIQUE INDEX  ON log_ms_202204 (id);
  CREATE UNIQUE INDEX  ON log_ms_202205 (id);

  CREATE INDEX  ON log_ms_202201 (dt_insert);
  CREATE INDEX  ON log_ms_202202 (dt_insert);
  CREATE INDEX  ON log_ms_202203 (dt_insert);
  CREATE INDEX  ON log_ms_202204 (dt_insert);
  CREATE INDEX  ON log_ms_202205 (dt_insert);

You have to write 2 queries in order to maintain that table every month.
These queries must read the postgresql system catalog given these result (today is 4th May 2022):

 Creation of its next partition (table and indexes):
 CREATE TABLE log_ms_202206 PARTITION OF log_ms FOR VALUES FROM ('2022-06-01') TO ('2022-07-01');
 CREATE INDEX ON log_ms_202206 (id);
 CREATE INDEX ON log_ms_202206 (dt_insert);

 Drop of its indexes oldest than 3 months:
 DROP INDEX log_ms_202202_id_idx;
 DROP INDEX log_ms_202202_dt_insert_idx;

March - Oracle and Postgres: SQL tuning

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

This issue can happen in Oracle and in Postgres.
The solution can be the same in both RDBMS, BTW here we see the Postgres case.

You have these tables:
- T_VRO : about 220 rows
- T_VRS : 320.000 rows and 120.000 of these rows have the column called C1 with 'EXTERNAL' value.
In this table, you also have one highly selective index on the ORDERID column

You have this query:

 select orderId, c2, c3
 from T_VRO
 where orderId not in
  (select orderid
   from T_VRS
   where c1 = 'EXTERNAL');

The result has about 150 rows.
The timing of its execution is 2 seconds and this is its execution plan:

                             QUERY PLAN
 --------------------------------------------------------------------------
 Seq Scan on t_vro  (cost=0.00..1796442.36 rows=112 width=25)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
     ->  Materialize  (cost=0.00..15816.61 rows=117948 width=10)
           ->  Seq Scan on t_vrs  (cost=0.00..14650.88 rows=117948 width=10)
                 Filter: ((c1)::text = 'EXTERNAL'::text)

You have to re-write that query in order to get faster result.

In particular you can get the result in 2 milli-seconds (1000 times faster than the original SQL!) and this will be the execution plan:

                             QUERY PLAN
-------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=0.42..3687.78 rows=1 width=25)
   ->  Seq Scan on t_vro  (cost=0.00..11.23 rows=223 width=25)
   ->  Index Scan using t_vrs_orderid_idx on t_vrs  (cost=0.42..23.31 rows=2 width=10)
         Index Cond: ((orderid)::text = (t_vro.orderid)::text)
         Filter: ((c1)::text = 'EXTERNAL'::text)

February - Postgres: Barman error

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

You use the barman tool in order to do the physical postgres backup.
To check the connection between the barman server and the postgres server (here called cluster1), from the barman server you launch this command:

 barman@db01:~$ barman check cluster1
 Server cluster1:
  PostgreSQL: OK
  superuser or standard user with backup privileges: OK
  PostgreSQL streaming: OK
  wal_level: OK
  replication slot: OK
  directories: OK
  retention policy settings: OK
  backup maximum age: OK (interval provided: 1 day, latest backup age: 7 hours, 1 second)
  compression settings: OK
  failed backups: FAILED (there are 1 failed backups)
  ...

Why you have the "failed backups: FAILED" message?
How can you solve it?

January - Oracle error

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

You have a table created like this:

 CREATE TABLE t1
  (id INT,
   descr VARCHAR2(30),
 CONSTRAINT t1_pk PRIMARY KEY (id));

It has already some rows. You try to insert a row but you receive an error:

 SQL> INSERT INTO t1
   2* VALUES (1, 'test');

 Error starting at line : 1 in command -
 INSERT INTO t1
 VALUES (1, 'test')
 Error report -
 ORA-00001: unique constraint (ADMIN.T1_PK) violated

Why?

Solution

You get this error on the primary key constraint because of in the table you have already a record with id = 1

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

Topic:
DBA: 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


© Copyright 2005-Today Loris ASSI
This website is not connected with Oracle Corporation or the official Oracle website. Some words used here are registered trade marks of their owners.