ManualiOracle.it
  
 
> Quizzes > Quizzes 2020-2024 -- 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 Quiz 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


Febuary 2024 - Oracle tool

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


Your db is an Oracle 19c Enterprise Edition and it has performance problems.
You'd like to use AWR/ADDM/ASH and their views but your company haven't pay the license to use them.

Which other Oracle tool can you use, for free, to get the performance information to tune the database?
Which M.O.S. notes talk about that tool?
And which are the most important commands to install and use that tool?



December 2023 - Oracle SQL statistics

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


From an AWR report, you see a very "bad SQL statistics" (many buffer gets, CPU time, etc) on a SQL statement starting with DELETE EMP2...

Checking this EMP2 table, you find out that it has less then 13.000 rows, no high average row length, no indexes, no chained/migrated rows, no LOBs and no fragmentation.

What can be the reason why this SQL is so bad?
Write a TEST CASE to prove your hypothesis.
And find how can you probably reduce/solve this issue.



November 2023 - 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:
  CREATE DATABASE db2
  TEMPLATE db1 
  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 2023 - Oracle DUAL table

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


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;

  D
  -
  X
  y

  SQL > select * from dual;

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



September 2023 - 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 2023 - 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 2023 - Oracle SQL*Plus commands

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


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

Solution

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 2023 - Oracle X$ tables

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


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 2023 - Oracle CDB or non-CDB

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


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 2023 - 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 2023 - 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:
  USERNAME     ORACLE_MAINTAINED 
  ------------ -----------------
  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) 2023 - 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 2023 - 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 11.2.0.3.

You have to find:
1. Which could be this reason? and why looking at the DBA_OBJECTS this issue happens from 11.2.0.3 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 2023 - Oracle SQL hints

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


In Oracle, you have this table and indexes:
  CREATE TABLE emp (
    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:
  SELECT *
  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?



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



December 2021 - Postgres loading

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


You are connected in Postgres 12 and you have to load a file called numbers_list.txt and containing a lot of telephone numbers like these:
3123456789
3012345678

You have to load them in the C1 column on a table called T_2C like this:
  Column  |         Type          | Collation | Nullable | Default

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

  c1      | character varying(20) |           | not null | 
 
  c2      | character varying(20) |           | not null |

In every rows, the C2 column must contain the string "Offer 1".
How can you do this loading?



November 2021 - Postgres upgrade

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


Your postgres db is in a Debian server and the db version is 12.4. You want to upgrade it to the version 12.9.

Which steps you should do in order to do that upgrade?



October 2021 - Postgres and in Oracle catalogs

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


You have to find two queries (one in Oracle and one in Postgres) in order to get the owner and the name of the table with longer name where you are connected:
- if you are in Oracle: in your schema
- if you are in Postgres: in the "public" schema on the database where you are



September 2021 - Oracle constraints

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


You are trying to DELETE a record from C_CLUB table but you get an error:

  SQL> delete c_club where id = 1;

  Error starting at line : 1 in command -

  delete c_club where id = 1

  Error report -

  ORA-02292: integrity constraint (ADMIN.CLUB_HI_FK) violated - child record found

Then now you have to find 2 queries from the Oracle dictionary in order to get:
1. the child table having the CLUB_HI_FK constraint
2. the columns in C_CLUB and in the "child table" used by the CLUB_HI_FK constraint



August 2021 - Oracle index rebuild

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


You are connected in a database (not PRODUCTION env) and in your schema all the indexes are not partitioned.
Write a query reading a static data dictionary view in order to obtain the SQL commands to rebuild all your indexes bigger than 64 KB.

For instance, launching that query you have to obtain a result like this:
  alter index PUBL_UQ rebuild;
  alter index T_VAL_UQ rebuild;
  alter index CALC_H_PK rebuild;
  alter index CLUB_IDX rebuild;
Solution

To get these information, you should use the USER_SEGMENTS:
  SELECT 'alter index ' || segment_name || ' rebuild;'
  FROM user_Segments  
  WHERE segment_type = 'INDEX' and bytes > 64*1024;



July 2021 - Oracle functions

Level (*) : 1
Topic (*) : Prog


You have a table T1 with 99 rows created like this:
  CREATE TABLE t1 
   (c1 INT,
    c2 INT,
    CONSTRAINT t1_pk PRIMARY KEY (c1)
   );

  INSERT INTO t1
  SELECT ROWNUM,
         DBMS_RANDOM.VALUE (0,10000)
  FROM all_objects
  WHERE rownum<100;
You have to find a query in order to get these information:
- the number of total rows of T1
- sum of C2 values
- average values of C2 (with one digit after the comma)

Result example:
   COUNT       SUM       AVG
  _______ _________ _________
      99     420439    4246.9
Solution

To get these information, you should use this query:
  SELECT count(*) count, 
         sum(c2) sum, 
         round(avg(c2),1) avg
  FROM t1;



June 2021 - Oracle tablespaces

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


You have to find a query in order to get the tablespace names occupied from the objects of your schema.
For instance, you must have a result like this:
     SEGMENT_TYPE    TABLESPACE_NAME
 __________________ ________________
 INDEX              TBL_IDX
 LOBINDEX           TBL_IDX
 LOBSEGMENT         TBL_DATA
 TABLE              TBL_DATA
 TABLE PARTITION    TBL_DATA
Solution

To get these information, you can use the USER_SEGMENTS:
 SELECT distinct segment_type, tablespace_name
 FROM user_segments
 ORDER BY 1;



May 2021 - Oracle column data default

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


You are connected to the db thru the user called ADMIN.
You have to find a query in order to get the columns with a data default value.

For instance, you have to receive a result like this:
   TABLE_NAME  COLUMN_NAME              DATA_DEFAULT
  __________ _____________ __________________________
  CITIES       ID           "ADMIN"."SEQ_5"."NEXTVAL"
  OBJECTS      ID           "ADMIN"."SEQ_3"."NEXTVAL"
  EVENTS       ID_EVE       "ADMIN"."SEQ_4"."NEXTVAL"



April 2021 - Postgres psql error

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


You are connected on the db server.
When you launch psql you get the following error/warning:
psql:/var/lib/postgresql/.psqlrc:8: error: unterminated quoted

Example:
  postgres@db_server:~$ psql
  Expanded display is used automatically.
  psql:/var/lib/postgresql/.psqlrc:8: error: unterminated quoted string
  psql (12.4 (Debian 12.4-1.pgdg90+1))
  Type "help" for help.

  postgres=#
Why? How can you solve it?



March 2021 - Find Tables info in Postgres and in Oracle

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


You have these tables in 2 different RDBMS:
- T_PG in Postgres
- T_ORA in Oracle

For both RDBMS, you have to find:
- their describe (column names and data types)
- their number of rows

Solution

Using Psql for Postgres and SQLcl for Oracle, an easy solution is:

Describe:
In Postgres, use the "\d" command:

  \d t_pg

  Column  | Type    | Collation | Nullable | Default
  --------+---------+-----------+----------+---------
  id      | integer |           | not null |
  name    | text    |           |          |

In Oracle, use the "desc" command:

  desc t_ora

     Name       Null?            Type
  _______ ___________ _______________
  ID      NOT NULL    NUMBER(38)
  NAME                VARCHAR2(10)
Count of rows:

In both RDBMS you can use the "count(*)" function:
  SELECT count(*)
  FROM t_ora;

     COUNT(*)
  ___________
         100



February 2021 - Oracle Segment types size

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


You have to write a query on system views that shows:
- the segment types in your schema
- their total size
- the percentage of their size compared to the total size.

For instance:
  TYPE      KB      TOT   PERCENT
  _____ _______ ________ _________
  IND     4416    13184        33
  LOB     1216    13184         9
  TAB     7552    13184        57



January 2021 - Oracle primary key names

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


You have to write a PL/SQL anonymous block in order to get all the primary key names in your schema and the names of their tables.

For instance, you must see something like:
  The primary key of the TAB1 table is called TAB2_PK
  The primary key of the CITIES table is called PK_CITIES
  The primary key of the TAB_VAL table is called TAB_VAL_PK



December 2020 - MERGE command

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


You have a sequence called SEQ and a table called CALC_HIST:
  CREATE SEQUENCE seq;

  CREATE TABLE calc_hist
   (id  INT  DEFAULT seq.NEXTVAL
   ,name  VARCHAR2(10)
   ,season  VARCHAR2(10)
   ,competition VARCHAR2(10)
   ,match  INT
   ,gol   INT
   ,CONSTRAINT calc_hist_pk PRIMARY KEY (id)
   ,CONSTRAINT calc_hist_uq UNIQUE (name, season, competition)
   );
In it, there are these 2 rows:
  INSERT INTO calc_hist (name, season, competition, match, gol)
  VALUES ( 'Ronaldo', '2018/19', 'A', 31, 21 );

  INSERT INTO calc_hist (name, season, competition, match, gol)
  VALUES ( 'Ronaldo', '2019/20', 'A', 8, 5 );
You have to write a MERGE sql command in order to INSERT or UPDATE the data inside that table.
For instance after launcing twice that SQL (with different values), you have to see 3 rows like these:

 ID    NAME    SEASON   COMPETITION     MATCH    GOL
 __ _______ __________ ______________ ________ ______
  1 Ronaldo   2018/19    A                  31     21
  2 Ronaldo   2019/20    A                  33     31
  4 Ronaldo   2020/21    A                  16     15


November 2020 - Oracle aggregate function

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


You have a table called MANAGERS:
  CREATE TABLE managers
   (id  INT
   ,season  VARCHAR2(10)
   ,name  VARCHAR2(10)
   ,match  INT
   );
In it, there are these rows:
  INSERT INTO managers
  VALUES ( 1, '1998/99', 'Lippi', 33 );

  INSERT INTO managers
  VALUES ( 1, '1998/99', 'Ancelotti', 20 );

  INSERT INTO managers
  VALUES ( 1, '1999/00', 'Ancelotti', 52 );

  INSERT INTO managers
  VALUES ( 1, '2000/01', 'Ancelotti', 42 );
You have to write a query finding the seasons with more then 50 matches.

The result must be like this:
  MATCH WHO_WHEN
  ----- ------------------------
     53 Lippi, Ancelotti 1998/99
     52 Ancelotti 1999/00


October 2020 - Oracle Date function

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


In your db there is this table with 4 rows:
  CREATE TABLE players (
   name varchar2(30),
   date_birth varchar2(10),
   date_100_gol varchar2(10) );

  INSERT INTO players VALUES ('Baggio','1967-02-18','1994-02-27');
  INSERT INTO players VALUES ('Del Piero','1974-11-09','2001-06-10');
  INSERT INTO players VALUES ('Platini','1955-06-21','1986-09-21');
  INSERT INTO players VALUES ('Trezeguet','1977-10-15','2005-09-14');
You have to write a function called fz_age_100_gol.
The result of this function must be the difference (in years and days) between the above dates.

Example:
  SELECT fz_age_100_gol ( date_birth, date_100_gol ) || ' ' || name  age_100_gol
  FROM players
  ORDER BY 1;

  AGE_100_GOL
  ----------------
  26 213 Del Piero
  27   9 Baggio
  27 334 Trezeguet
  31  92 Platini
Simple part of that issue is the "day alignment": the number "9" of Baggio must be under the "3" of Del Piero and not under the "2".



September 2020 - Char data

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


You have this table:
  CREATE TABLE t_char 
   (id INT, descr VARCHAR(30));
In it these is this row:
  SELECT * FROM t_char;

   id |     descr         
  ----+--------------
    1 | He's the best 
Write the SQL command that can be used to insert that row.

Solution

In Oracle and in postgres, to do that insert you can use this simple SQL:
  INSERT INTO t_char VALUES (
    1, 'He''s the best' );
Then to write a single quote, you must enter 2 single quotes.



August 2020 - Foreign Key problem

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


This quiz is valid for Oracle, Postgres and others RDBMS.
You have these tables and constraints:
  CREATE TABLE   t1 (
   cod	VARCHAR(15)
  ,nome	VARCHAR(30)
  ,cognome	VARCHAR(30)
  ,nick_1	VARCHAR(30)
  ,CONSTRAINT t1_pk PRIMARY KEY (cod)
  );

  CREATE TABLE   t2 (
   id  INT
  ,cod_pers	VARCHAR(20)  
  ,cod_ruolo	VARCHAR(20)
  ,CONSTRAINT t2_pk PRIMARY KEY (id)
  );

  ALTER TABLE t2
  ADD CONSTRAINT t2_fk
  FOREIGN KEY (cod_pers)
  REFERENCES t1;
You have these rows:
  insert into t1 values (
  'Mancini','Roberto','Mancini','R.Mancini' );

  insert into t2 values (
  1, 'Mancini','CALC');

  insert into t2 values (
  2, 'Mancini','ALLEN');
You want to modify the parent row:
  update t1
  set cod = 'R_Mancini'
  where cod = 'Mancini';
But you can't and in Oracle you get this error:
  ORA-02292: integrity constraint (ADMIN.T2_FK) violated - child record found
While in Postgres, you get this:
  ERROR:  update or delete on table "t1" violates foreign key constraint "t2_fk" on table "t2"
  DETAIL:  Key (cod)=(Mancini) is still referenced from table "t2".
Which SQL command, can you use to avoid it?
(you don't want to use deferred constraints)



July 2020 - Postgres parameter

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


You have installed postgres version 12 in the machines called db01, db02.
They are in master-slave streaming replication configuration (db02 is a hot standby db).

For the first time, you are doing a switch over between them: some macrostep you have done:
- promote db02 to master
- pg_rewind on db01

Before starting the new slave (db01), you must change one its parameter.
Which one?
Which is a simple way to do it?



June 2020 - Postgres startup error

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


You have installed postgres version 12 in the machines called db01, db02.
You want to configure a master-slave streaming replication between them (db02 must be a hot standby db).

You are trying to use same steps you have used in postgres 10 and 11.
But when you try to startup the slave db in db02, you can't do it and receive the error:
 FATAL: XX000: using recovery command file "recovery.conf" is not supported

Why?
How can avoid it?



May 2020 - Oracle update

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


You have a table called T_UPD with these 2 columns:
  col_1  INT
  col_2  VARCHAR2(100)
Inside it, you have rows like these:
  col_1	col_2
  -----  --------------------
      1  123 is the old value
      2  77 is the old value
      3  the old value is 43
  ....
   9999  the old value is 5436
In the column "col_2", you want to replace the string "old value" with "new value" for all the rows with "col_1 > 10".
How can you do it?

Solution

To change the string, you should use the "replace" function.
Then this is the update:
  UPDATE t_upd
  SET col_2 = replace(col_2, 'old value', 'new value')
  WHERE col_1 > 10;




April 2020 - Oracle virtual column

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


You are in Oracle 18c and you want to create a table T1 with 3 colums:
 - c_1 : it must be a virtual column valued with as 'c_2 || '-' || c_3'
 - c_2 : varchar2(10)
 - c_3 : varchar2(10)
Write the SQL to create that table.
And write a query reading the Oracle dictionary to see that C_1 is a virtual column.



March 2020 - Oracle JSON

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


You are in Oracle 18c and you have a table called FILMS with a JSON column:
 CREATE TABLE   films  (
   id	INT  DEFAULT seq.NEXTVAL
  ,nome	VARCHAR2(40)
  ,cod_stato	VARCHAR2(9)
  ,anno		VARCHAR2(9)
  ,js_soldi	VARCHAR2(255) 
  ,CONSTRAINT films_pk PRIMARY KEY (id)
  ,CONSTRAINT films_js_chk CHECK (js_soldi IS JSON)
 );

The "js_soldi" column contains keys such as "gros_ita_eur", "gros_w_usd", "costo_usd", etc.
Here there are some of its rows:
  NOME       JS_SOLDI
  ---------- ------------------------------------------------------
  Tolo Tolo  {"gros_ita_eur" : 46174075}
  Ben-Hur
  Avatar     {"gros_ita_eur" : 65666319, "gros_w_usd" : 2789958507}

You have to find a query in order to get the 3 highest grossing films:
In the table, this info has the key called "gros_w_usd" (you must divide its value by 1000000 to have the result in million dollars).

Example of the result:
  MILLION_DOLLARS NOME
  --------------- -----------------
             2798 Avengers: Endgame
             2790 Avatar
             2187 Titanic




February 2020 - Postgres JSONB and Unix time stamp

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


In your db, there is a table called CUST.
It is like this:
                    Table "public.cust"
    Column   |  Type   | Collation | Nullable | Default 
  -----------+---------+-----------+----------+---------
   id        | integer |           | not null | 
   name      | text    |           |          | 
   birthinfo | jsonb   |           |          | 
  Indexes:
     "cust_pk" PRIMARY KEY, btree (id)

In it, there is data like this:
   id |   name     |             birthinfo                        
  ----+------------+------------------------------------------------------
    1 | Loris Assi | {"city": "Augusta", "date": 203644800000, "pr": "SR"}

The date information is in Unix time stamp format (milliseconds from January 1st, 1970).
You have to find a query in order to convert that date in 'yyyymmdd' format.

Then the query result should be like this:
   id |    name    | birthdate 
  ----+------------+-----------
    1 | Loris Assi | 19760615




January 2020 - Postgres backup error

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


Using Barman, we launch a backup of a Postgres cluster database called "c3".
We receive the following error:
 barman@dblor:~$ barman backup c3
 Starting backup using postgres method for server c3 in /u1/barman/c3/base/20191219T105926
 Backup start at LSN: 16/FF34BB88 (0000000100000016000000FF, 0034BB88)
 Starting backup copy via pg_basebackup for 20191219T105926
 ERROR: Backup failed copying files.
 DETAILS: data transfer failure on directory '/u1/barman/c3/base/20191219T105926/data'
 pg_basebackup error:
 pg_basebackup: initiating base backup, waiting for checkpoint to complete
 pg_basebackup: could not initiate base backup: SSL SYSCALL error: EOF detected
 pg_basebackup: removing contents of data directory "/u1/barman/c3/base/20191219T105926/data"

Which could be the reason of that error?
And what you can do in order to avoid it?



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

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

   

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