Manuali Oracle - Quizzes 2021

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

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

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