|
|
ManualiOracle.it |
|
|
||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
December - Clone Db
Level (*) : 2 Topic (*) : DBA
You have an Oracle db in the machine machine1. You want to clone it in the machine2 that has exactly the same features of the machine1. you can't stop the db in the machine1. Which is the fastest way to do it?
November - Existing Files
Level (*) : 1 Topic (*) : Prog
We are in Oracle 9i on a Sun Solaris machine. You have to do a PLSQL program that, when is launched, do this: 1. checks if exists the file /store2/errlog/pippo.txt 2. if it's existing, you have to write (by dbms_output) 'The file exists' 3. if it's not existing, you have to write (by dbms_output) 'The file doesn't exist'
October - Log Tables
Level (*) : 3 Topic (*) : Prog
In our database, as in many others, we write information about some important transactions in a log table. This is our log table: create table LOG_MSG (nr_sequence number, procedure_name varchar2(70), message varchar2(200), dt_insert date); alter table LOG_MSG add constraint LOG_MSG_PK primary key (nr_sequence);In this table, our application write about 200.000 rows every day. You want to keep this information for 1 month. You must find a way to access to this information always fast (then do every night a delete of the old rows is not a good solution). Your solution must be suitable for Oracle8i, 9i and 10g and must be suitable also if in your db is not enabled the PARTITIONING option.
September - Datafile Contents
Level (*) : 3 Topic (*) : DBA
You know that the tablespace EXAMPLE has many free space: it's big 100M and has 95M free space. You use the query used in the quiz of August 2007 so you see that you can do the resize of his datafile until his size will be 80M. Then you want to look at inside his datafile contents to discover if it's easy to save more space in it. Then you have to do a query that show the tablespace contents like that (ordered by block_id desc):
... These are just the first rows of the result. This result is useful because now you know that if you rebuild the indexes SH.FW_PSC_S_MV_IDX and LASSI.EXAMPLE_PK then you can resize the datafile until about 17M (of course, before that you rebuild these indexes you should check how much big they are). In the query that you have to find, you have to use two substitution variables: one useful if you want to give the tablespace name, the other one if you want to give the datafile name (especially if the tablespace has more than one datafile).
August - Datafile resize and Dictionary Views
Level (*) : 3 Topic (*) : DBA
You are working in Oracle 9.2. In your machine there is just a little disk free space. You know that the USERS tablespace has many unsed space then you decide to shrink his 2 data files. You must find a SQL query that you can use in order to find how much big are these data files and how much big you can make them. Example: When you launch the SQL query, you must receive a result like this:
And you must find another SQL query for Oracle 8.0 and 8i.
July - Create table and Dictionary views
Level (*) : 2 Topic (*) : Prog
You have the table TAB_A and this table contains columns only with these datatypes: char, varchar2, date and number; and these columns don't have
any default value. You must do a SQL query that you can launch in order to obtain the TAB_ORDERED such as TAB_A but with the column created in alphabetic order. It's not important to take into account the tablespace_name, the constraints and the storage clause. Example: SQL> desc tab_a Name Type Nullable ------- ------------ -------- FILE# NUMBER Y STATUS VARCHAR2(18) Y CHANGE# NUMBER Y TIME DATE Y FLAG CHAR(1) SUM NUMBER(10,2) YAnd when you launch your SQL query, you must obtain a result like this: CREATE TABLE TAB_ORDERED ( CHANGE# NUMBER ,FILE# NUMBER ,FLAG CHAR(1) NOT NULL ,STATUS VARCHAR2(18) ,SUM NUMBER(10,2) ,TIME DATE );
June (2) - SQL and System Views
Level (*) : 2 Topic (*) : DBA/Prog You have many concurrencies in the tables and indexes of the schema MONITOR_DB. Then you decide to modify their initrans like that: - the table A will have initrans 8 and his indexes with initrans 9 - the table XXX will have initrans 6 and his indexes with initrans 7 - the other tables will have initrans 4 and their indexes with initrans 5 You must do ONE single SQL query that compose this.
June - Import, Export and Estimated Time
Level (*) : 3 Topic (*) : DBA/Prog
You are working in Oracle 9.2 and you want to do a long export of a single schema. You must do a SQL query useful in order to estimate when this export will be finished. The SQL query will be launched during the export and it must to take into account how big are the tables and their lobs. For instance, when you launch the query, you must receive a result like that: EXPORT (tables and lobs): Start: 200706191500 Now : 200706191558 Owner: MPH You are doing the table TR3L (Rate of this table already done: 90%) 3872 MB Done 1655 MB Not Done Yet The export will finish on 20070619 1623When you execute this query you have to write: - the time when you have started the import. In the example above: 200706191500 - the schema name that you want to export. In the example above: MPH - the table_name that is exported in the moment that you launch the query. In the example above: TR3L - the rate of the current table that has been already exported (estimation). In the example above: I have estimated 90%. If you don't know it, write 50.
May - Backup and Estimated Time
Level (*) : 3 Topic (*) : DBA/Prog
You are working in Oracle 9.2. and you are doing an hot backup. You do the hot backup (very day) launching a script that do the backup of every tablespace except the temp tablespace. You want to do an SQL query useful to estimate when the backup that now is happening will finish. For example, you want a result like this: Start Backup: 26/05/2007 10.21.19 Now: 26/05/2007 10.24.52 124 M backupped today. 989 M missing. Estimated finish time: 26/05/2007 10.53.11Remember that you work in Oracle 9.2 than you can use the "WITH clause" in order to have a query more efficient.
April - Index Rebuild
Level (*) : 1 Topic (*) : DBA/Prog
You want to do an SQL query that creates the commands useful to do the rebuild of any index (not SYS or SYSTEM's indexes) not partitioned that have more than 100 extents. In that rebuild you want to put the storage and the tablespace that the index had before the rebuild. For example, if you launch the SQL the result is this: alter index MONITOR_DB.XXX2_IDX rebuild storage (initial 786432) tablespace USERS; alter index MONITOR_DB.XXX_IDX rebuild storage (initial 7340032) tablespace PROVA_STRIPE; alter index MONITOR_DB.A_IDX rebuild storage (initial 23068672) tablespace USERS;
March - Use Indexes
Level (*) : 1 Topic (*) : Prog
We are thinking to create an b-tree index in the ID_MOV column on the PIPPO table. We have to calculate if that index could be selective, or not. We don't want to do any statistics and we don't want to look at any dictionary views. Then we decide to do a query and in that query we also want to calculate how many rows are in the PIPPO table. Then you have to find the right query and you have to say one important init parameter that the optimizer use to decide if it is better to do a Full Table Scan or to use an index.
February - Administration
Level (*) : 2 Topic (*) : DBA
After one db creation, it's a good idea to create a "superuser" on the db. This superuser is useful because you can use it in order to do normal administrative taskes (instead of using SYS or SYSTEM) and because in his schema you can put customized procedures or tables that are important to automatic checkes or taskes on the db. Find the syntax in order to create that user, his grants and a dedicated tablespace for it. Do it for Oracle 8.0, Oracle 8i, Oracle 9i and Oracle 10g.
January (2) - Trace and TKPROF
Level (*) : 2 Topic (*) : Prog
In the SCOTT's schema you have the procedure pr_pippo. This procedure do many operations and we would tune it in order to have it faster. Then we decide to do a trace of that procedure and after use the tkprof. How you can do this steps?
January - DOS commands & DB Administration
Level (*) : 2 Topic (*) : DBA We have an Oracle db on a Windows machine not in cluster. We want to create a batch file that every month do these: - rename the alert log and zip it (i.e. from alert_orcl10.log to alert_20070119_01525.zip) - compress the trace files contained in bdump to a unique zip - compress the trace files contained in udump to a unique zip - rename the listener.log and zip it (without stopping the listener service) Warning: ALL these operations can happen while the users are working on the db. This exercise is usefull also because you can see some IMPORTANT activities in the db maintainance (in Windows and in Unix)
(*) 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
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
