ManualiOracle.it
  
 
> 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


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

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



(*)
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

   

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.