| ManualiOracle.it | ||
|
||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
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 October 2024 - Postgres database users
Level (*) : 2 Topic (*) : Postgres In a Postgresql database called DB_TEST, you have to create 3 users (called U1, U2, U3) and maybe others in the future. Regarding these users: - they must have the privilege to do SELECTs on all the tables (existing and created in the future) of the public schema. - they can have access to the db from remote machine only on this database from the subnet 172.10.10.x with MD5 method authentication how can you implement this?
August 2024 - Bash script
Level (*) : 2 Topic (*) : DBA/Progr In a Linux machine, you have a postgres database. It logs on the postgres log files all the SQL statements slower than 0.1 seconds. In the home of postgres OS user, you must write a little bash script. When you launch it you must have a result similar to this one: postgresql-07-01.log 201 postgresql-07-02.log 103 postgresql-07-03.log 32 postgresql-07-04.log 78 postgresql-07-05.log 30 ...Conditions: - log directory to check: /opt/lib/pgsql/data/log - filenames to check: postgresql*.log - files must be modified on July 2024 - for each file the script must count how many times there is the string " LOG: duration: " excluding the ones with the string " db_lor " Regarding the above Example: The postgresql-07-05.log file has 30 rows with the string " LOG: duration: " and not " db_lor "
June 2024 - Postgresql Authentication
Level (*) : 3 Topic (*) : Postgres In a postgres server, you want to connect to the lordb database using the database user called lor_user :
$ psql -h localhost -U lor_user -d lordb
Password for user lor_user:
psql: error: could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
FATAL: password authentication failed for user "lor_user"
You get more details from the postgresql log file :
2024-06-14 16:56:06.742 CEST [24428] lordb FATAL: password authentication failed for user "lor_user" 2024-06-14 16:56:06.742 CEST [24428] lordb DETAIL: User "lor_user" does not have a valid SCRAM secret. Connection matched pg_hba.conf line 87: "host all all 127.0.0.1/32 scram-sha-256"Which postgresql dictionary view should you check to be sure regarding the root of the problem? Which are the 2 possible solutions? Which of them is more secured?
April 2024 - Postgresql table partitioning by range
Level (*) : 2 Topic (*) : Postgres In postgres you want to create 2 tables with this "basic structure":
CREATE TABLE t1 (
id INT
,ts TIMESTAMP
,valid VARCHAR(10)
);
ALTER TABLE t1 ADD PRIMARY KEY ( id );
CREATE TABLE t2 (
id_t1 INT
,name VARCHAR(50)
,setting VARCHAR(50)
);
ALTER TABLE t2 ADD UNIQUE ( id_t1, name );
ALTER TABLE t2
ADD FOREIGN KEY (id_t1) REFERENCES t1 (id);
But actually the T2 table must be a partitioned table with the partition key on the ID_T1 column. That partitioning must be BY RANGE and the T2 table must have only 3 partitions (called T2_P0, T2_P1, T2_P2) where the rows will be inserted according to this role: id_t1 = 0-999 -> t2_p0 id_t1 = 1000-1999 -> t2_p1 id_t1 = 2000-2999 -> t2_p2 id_t1 = 3000-3999 -> t2_p0 id_t1 = 4000-4999 -> t2_p1 etcWrite the SQL statements in order to get this configuration.
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 - XOur 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 220And 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 multitenantThis 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 YBut 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 parametersWhy 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, EuropeSo 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.dmpBut you receive always: pg_dump: error: no matching tables were foundWhich 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) violatedWhy? 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 foundThen 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 PlatiniSimple 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 foundWhile 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 DBAs and for Programmers Postgres: regarding the Postgresql database
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
