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

June - 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:
 __________________ ________________
 INDEX              TBL_IDX
 TABLE              TBL_DATA

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

May - 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:
  __________ _____________ __________________________
  CITIES       ID           "ADMIN"."SEQ_5"."NEXTVAL"
  OBJECTS      ID           "ADMIN"."SEQ_3"."NEXTVAL"
  EVENTS       ID_EVE       "ADMIN"."SEQ_4"."NEXTVAL"

April - 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

  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.

Why? How can you solve it?

March - 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


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

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;


February - 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 - 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

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 authorized by Oracle Corporation and it is not connected to the official Oracle website.
Some words used here are registered trade marks of their owners.