| ManualiOracle.it | ||
|
||
| ||||||||||||||||||||||||||||||||||||||||||||||
|
If it is not specified, the commands must be executed connected as SYS. 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 Oracle version (eg. Oracle 8i is quite different than Oracle 12c!) - it is not warranted to be error-free in all the Oracle environments December - Table name
Level (*) : 1 Topic (*) : DBA You are connected in Oracle 11g as SYS user. You want to search some information regarding the table called T1, so you launch this: select * from dba_tables where table_name = 't1';But you don't receive any result, why? Solution
Because usually in the "DBA_" views you should search the information using the Uppercase, so you should launch:
select * from dba_tables where table_name = 'T1';
November - Redo log switches
Level (*) : 2 Topic (*) : DBA You want to discover how many redo log switches your db has done during the last 8 days. You want the result in tabular format divided in day and hour. For example, you want a result like this: DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 ----------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- -- 11/25, SAT 0 0 0 1 0 0 0 1 2 0 0 0 5 1 1 0 1 1 11/26, SUN 0 0 0 15 0 0 0 0 4 1 6 5 5 2 0 1 6 3 11/27, MON 1 0 0 20 0 0 0 0 1 4 6 1 3 9 0 5 5 1 11/28, TUE 1 0 0 30 0 0 0 0 2 1 2 0 7 4 0 1 1 0 11/29, WED 1 0 0 12 0 0 0 0 0 0 0 0 0 1 0 0 4 0 11/30, THU 1 0 0 10 0 0 0 0 0 0 0 0 0 0 0 0 2 0 12/01, FRI 0 0 0 3 0 0 0 0 0 1 9 4 1 0 1 20 10 0 12/02, SAT 0 0 0 2 2 0 0 2 1 0 5 1 1 7 9 1 0 0This means that today, 2 December, between 2pm and 3pm the db has done 9 redo log switches. In the example above, last column is "17" only because of the website layout: the actual query should be done until the column "23".
October - Index monitoring
Level (*) : 3 Topic (*) : DBA You want to discover if the index LORIS.PK_EMP is used or not. In order to do this, connected as SYS, you launch the following SQL: ALTER INDEX loris.pk_emp MONITORING USAGE;After this, still connected as SYS, in the V$OBJECT_USAGE view you don't see any record regarding to the PK_EMP index. Why? And which query can you use in order to check if the index is used?
September - Oracle users
Level (*) : 1 Topic (*) : DBA You are connected in an Oracle 11g database. You have to find a query useful in order to know all the db users, their account status and when they have been created. For example, you must have a result like this: USERNAME ACCOUNT_STATUS CREATED ----------- ----------------- --------- SYS OPEN 30-MAR-10 SCOTT OPEN 30-MAR-10 LORIS OPEN 04-AUG-14 SYSTEM EXPIRED(GRACE) 30-MAR-10 OUTLN EXPIRED & LOCKED 30-MAR-10 ... Solution
Connected as SYS (or another DBA user) you could use the static system view called DBA_USERS:
SELECT username, account_status, created FROM dba_users;
August - Combined query
Level (*) : 2 Topic (*) : DBA/Progr In your db, you want to find all the tables with determinated "pattern names". Example:
SELECT owner, table_name
FROM dba_tables
WHERE table_name LIKE 'ICOLD%'
OR table_name LIKE '%ROLE_DATA%';
You have to find the query that does the same thing writing only one WHERE condition (then without using the "OR").
July - Table stats
Level (*) : 1 Topic (*) : DBA In your db, under the LASSI schema, there is a table called T1 with about 1 million rows. How to discover when has been done last statistics on this table? If its statistics are old, how can you do new simple statistics of T1? Solution
To find last statistics time, launch : SELECT table_name, last_analyzed FROM dba_tables WHERE table_name = 'T1' AND owner = 'LASSI';To analyze the table, launch : exec dbms_stats.gather_table_stats ( 'LASSI', 'T1')In this way, Oracle will do the statistics: it is ok because of the table is not big.
June - Redo log problem
Level (*) : 2 Topic (*) : DBA In your db you want to drop a redo log file: SQL > ALTER DATABASE DROP LOGFILE MEMBER 'C:\ORA\ADMIN\ORADATA\ORCL\REDO01.LOG'; ALTER DATABASE DROP LOGFILE MEMBER 'C:\ORA\ADMIN\ORADATA\ORCL\REDO01.LOG' * ERROR at line 1: ORA-00361: cannot remove last log member C:\ORA\ADMIN\ORADATA\ORCL\REDO1.LOG for group 1. You got it because of in your db the redo log files aren't multiplexed. So you try this: 18:14:19 ORCL > ALTER DATABASE DROP LOGFILE GROUP 1; ALTER DATABASE DROP LOGFILE GROUP 1 * ERROR at line 1: ORA-01623: log 1 is current log for instance orcl (thread 1) - cannot drop ORA-00312: online log 1 thread 1: 'C:\ORA\ADMIN\ORADATA\ORCL\REDO01.LOG' So why this happens? And how can you drop this redo log file?
May - Oracle roles
Level (*) : 1 Topic (*) : DBA Find a query in order to discover which Oracle users have the DBA role. Solution
Connected as SYS (or another "DBA user") you could use the static system view called DBA_ROLE_PRIVS:
SELECT * FROM dba_role_privs WHERE granted_role = 'DBA';
April - Oracle Exadata
Level (*) : 2 Topic (*) : DBA You are connected in an Oracle exadata. You have to find a query in order to know some info about its cells. For example, you have to receive a result like this: KERNEL_VERSION MAKE_MODEL N cells N Instances ------------------------------ ---------------------------------------------------- ------- ----------- 2.6.39-400.248.3.el6uek.x86_64 Oracle Corporation SUN SERVER X4-2L High Performance 3 2
March - Oracle 12c multitenant environment
Level (*) : 2 Topic (*) : DBA You are connected in an Oracle 12c database with multitenant configuration: you are connected as SYS in the CDB. You have to find the SQL*Plus commands or SELECTs in order to receive results like these: CON_NAME ------------- CDB$ROOT NAME OPEN_MODE --------- ---------------- OR2017 READ WRITE INSTANCE_NAME VERSION STATUS ---------------- -------------- ------- or2017 12.1.0.2.0 OPEN NAME OPEN_MODE ----------- ---------- PDB$SEED READ ONLY PDB2017 MOUNTED
February - Dates
Level (*) : 2 Topic (*) : Progr You have a table like this: CREATE TABLE data (aod DATE); insert into data values (to_date(20150331,'yyyymmdd')); insert into data values (to_date(20150630,'yyyymmdd')); insert into data values (to_date(20150930,'yyyymmdd')); insert into data values (to_date(20151231,'yyyymmdd')); insert into data values (to_date(20160331,'yyyymmdd')); insert into data values (to_date(20160630,'yyyymmdd')); insert into data values (to_date(20160930,'yyyymmdd')); insert into data values (to_date(20161231,'yyyymmdd'));Then it contains the End Of the Quarter of different years. You have to find a query in order to have a result like this: AOD Q-1 EOY-1 ----------- ----------- ----------- 31-MAR-2015 31-DEC-2014 31-DEC-2014 30-JUN-2015 31-MAR-2015 31-DEC-2014 30-SEP-2015 30-JUN-2015 31-DEC-2014 31-DEC-2015 30-SEP-2015 31-DEC-2014 31-MAR-2016 31-DEC-2015 31-DEC-2015 30-JUN-2016 31-MAR-2016 31-DEC-2015 30-SEP-2016 30-JUN-2016 31-DEC-2015 31-DEC-2016 30-SEP-2016 31-DEC-2015It means that of each AOD, you have to find : - the end of the previous Quarter - the end of the Year of the previous Year
January - Column Indexed
Level (*) : 1 Topic (*) : DBA You are connected on the db thru the SYS user. You must find a query in order to know the COLUMN_NAMEs where you have the index called FACT1_IDX (under the LASSI schema). Solution
Connected as SYS (or another DBA user) you could use the static system view called DBA_IND_COLUMNS:
SELECT *
FROM dba_ind_columns
WHERE index_owner = 'LASSI' and
index_name = 'FACT1_IDX';
(*) Level: 1: easy solution 2: medium solution 3: hard (or long) solution Topic: DBA: quiz usually suitable for DBAs Progr: quiz usually suitable for PL/SQL or SQL Programmers DBA/Progr: quiz suitable for DBAs and for Programmers
| |||||||||||||||||||||||||||||||||||||||||||||
