ManualiOracle.it
  
 
> Quizzes > Quizzes 2012 - Oracle Best Pratices
 
 
 
 
 


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 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 8.0 is quite different than Oracle 11g!)
- it is not warranted to be error-free in all the Oracle environments


December - Fast Copy

Level (*) : 2
Topic (*) : DBA



Your db is in ARCHIVELOG mode.
You want to copy the table T1 (table with 100 million rows) in the new table T2 (these tables have the same describe).
How can you do this in a very fast way?
In the meantime, you want that nobody do changes in the T1, the users can use it just for SELECTs.
How can you do this?



November - ASM

Level (*) : 2
Topic (*) : DBA



Your db uses ASM.
You want to get bigger the tablespace TBS_1. Then, you have to check if you have enough free space on the data diskgroup.
How can you check this information? And after that, how can you add 8 Gigabytes to the tablespace TBS_1?



October - Index Details

Level (*) : 2
Topic (*) : DBA



The developers' group ask you to investigate this problem: they launch the procedure pr_problem and they receive the error "ORA-01555: snapshot too old".
Which could be the problem? How can you find which is the problem? And which solutions can you apply?



September - Index Details

Level (*) : 3
Topic (*) : DBA



In the db, you have a table called ORDERS.
You have to find the queries that shows its index details. Example:
The summary of its indexes:

 TABLENAME        INDEXNAME          STATUS  INDEX_TYPE            TABLESP_NAME UNIQUENES
 ---------------- ------------------ ------- --------------------- ------------ ---------
 BLEVEL DistinctKeys LAST_ANAL SAMPLE_SIZE  INI_TRANS PARTITIONED
 ------ ------------ --------- ----------- ---------- -----------
 LASSI.ORDERS     LASSI.IDX_OR       N/A     NORMAL                             NONUNIQUE
      0	1782052	19-SET-12      136113            YES

 LASSI.ORDERS     LASSI.IDX_PROVA    VALID   NORMAL                TBS_INDEX    NONUNIQUE
      2 1863974 19-SET-12       41188         30 NO

 LASSI.ORDERS     LASSI.PK_ORDER     VALID   NORMAL                TBS_INDEX    UNIQUE
      2 1778278 19-SET-12      134368         12 NO

 LASSI.ORDERS     LASSI.CLIENT_NAME  VALID   FUNCTION-BASED NORMAL TBS_INDEX    NONUNIQUE
      2 423444 19-SET-12      264726         12 NO


Its indexed columns:

 TABLENAME                INDEXNAME              Column_name        C_Position   C_length
 ------------------------ ---------------------- ------------------ ---------- ----------
 LASSI.ORDERS             LASSI.IDX_OR           OM_SITE_ID                1         22
 LASSI.ORDERS             LASSI.IDX_PROVA        ORDER_ID                  1         22
 LASSI.ORDERS             LASSI.IDX_PROVA         ACCOUNT_NUMBER           2         60
 LASSI.ORDERS             LASSI.IDX_PROVA          TECHNOLOGY              3         22
 LASSI.ORDERS             LASSI.IDX_PROVA           STATUS                 4         22
 LASSI.ORDERS             LASSI.PK_ORDER         ORDER_B                   1         22
 LASSI.ORDERS             LASSI.CLIENT_NAME      SYS_NC00036$              1         60


The expression of its "function-based" indexes:

 TABLENAME          INDEXNAME              COLUMN_EXPRESSION            COLUMN_POSITION
 ------------------ ---------------------- ---------------------------- ---------------
 LASSI.ORDERS       LASSI.CLIENT_NAME      UPPER("CLIENT_LAST_NAME")                  1


The details of its partitioned indexes:

 INDEXNAME    PARTITION_NAM HIGH_VALUE         PARTITION_POSIT TABLESP_NAM STATUS IniTrans
 ------------ ------------- ------------------ --------------- ----------- ------ --------
 LASSI.IDX_OR P136419656_0  13641965, MAXVALUE               1 TBS_INDEX   USABLE        2
 LASSI.IDX_OR P370794502_0  37079450, MAXVALUE               2 TBS_INDEX   USABLE        2
 LASSI.IDX_OR P_MX          MAXVALUE, MAXVALUE               3 TBS_INDEX   USABLE        2


The metadata of its indexes: here is one of them:

 CREATE UNIQUE INDEX "LASSI"."PK_ORDER" ON "LASSI"."ORDERS" ("ORDER_B")
 PCTFREE 10 INITRANS 12 MAXTRANS 255 COMPUTE STATISTICS
 STORAGE(INITIAL 9437184 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "TBS_INDEX"




August - Expdp Problem

Level (*) : 2
Topic (*) : DBA



Your db is in Oracle 11.2.0.3. You launch expdp and you immediately receive:
UDI-31623: operation generated ORACLE error 31623
 ORA-31623: a job is not attached to this session via the specified handle
 ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3263
 ORA-06512: at “SYS.DBMS_DATAPUMP”, line 4488
 ORA-06512: at line 1
What can you do in order to avoid it? Which metalink note explain it?



July - Sequence problem

Level (*) : 1
Topic (*) : DBA/Prog



You have the sequence LASSI_SEQ and now it is like this:
  select LASSI_SEQ.nextval 
  from dual;

  NEXTVAL
  -------
    10089
In this moment, nobody is using this sequence and you want that its nextval value will be 0.
You must do it without dropping the sequence.
How?

Solution

You have to do these steps:
- Do an ALTER on the sequence:

   alter sequence LASSI_SEQ 
   increment by -10089 minvalue 0;

- Check the nextval value:

   select LASSI_SEQ.nextval 
   from dual;

    NEXTVAL
   --------
          0

- Do another ALTER on the sequence:

   alter sequence LASSI_SEQ 
   increment by 1;




June - Statistic problem

Level (*) : 2
Topic (*) : DBA



You are in Oracle 10g and you have the partitioned table LASSI.PT.
Usually, you don't want that Oracle analyze that table.

Now you want to analyze it:
 exec DBMS_STATS.GATHER_TABLE_STATS ('LASSI', 'PT'); 
 *
 ERROR at line 1:
 ORA-20005: object statistics are locked (stattype = ALL)
 ORA-06512: at "SYS.DBMS_STATS", line 10640
 ORA-06512: at "SYS.DBMS_STATS", line 10664
 ORA-06512: at line 1
So, what you can do to analyzed it?
And how can you avoid again that Oracle automatically could analyzed it?



May - Profile problem

Level (*) : 2
Topic (*) : DBA



You are in Oracle 10g and you have the Oracle user LASSI with the profile PROF_1.
To the user LASSI, you want to give the password lassi1 but you receive the error
  ORA-28007: the password cannot be reused
You want to use only that password and you don't want to change the properties of PROF_1.
So what you can do?



April - Table describe

Level (*) : 2
Topic (*) : DBA



You have the table t1 and t2. Those tables have many columns. You have to find a query that checks their describes in order to see if these are identical: COLUMN_NAME, DATA_TYPE, DATA_LENGTH, COLUMN_ID.
E.g.:
 create table LASSI.t1
  (c1 char,
  c2 number,
  c3 date);

 create table LASSI.t2
  (c1 varchar2(10),
  c2 number);

 create table LASSI.t3
  (c1 char,
  c2 number,
  c3 date);
If you launch that query between t1 and t2, you receive:
 COLUMN_NAME            DATA_TYPE           DATA_LENGTH  COLUMN_ID     CNT_T1     CNT_T2
 ---------------------- ------------------- ----------- ---------- ---------- ----------
 C1                     CHAR                          1          1          1          0
 C1                     VARCHAR2                     10          1          0          1
 C3                     DATE                          7          3          1          0
and if you launch the query between t1 and t3, you receive:
 no rows selected





March - Oracle Password

Level (*) : 2
Topic (*) : DBA



You are in Oracle 11g, you want to know the hashed password (someone call it "encrypted password", but it's not totally right) of the user LASSI.
In the previous Oracle versions, you could check the column PASSWORD on the DBA_USERS. But in 11g, this value is always null.
So, what you can do in order to see that information?



February - Moving Segments

Level (*) : 3
Topic (*) : DBA/Progr



You want to move all segments contained in the tablespace TBS_SOURCE to the tablespaces TBS_TARGET_IDX and TBS_TARGET_DT.
You don't want to use exp/imp, expdp/impdp or online table Redefinition.

In TBS_SOURCE there are these objects types:
- INDEX --> you want them to TBS_TARGET_IDX
- LOBINDEX --> you want them to TBS_TARGET_DT
- LOBSEGMENT --> you want them to TBS_TARGET_DT
- TABLE --> you want them to TBS_TARGET_DT

You have to find a query (from dictionary) that create the correct DDL to move those objects.
For example, when you launch it, you have to see a result like this:

 SEGMENT_TYPE   DDL
 -------------- -------------------------------------------------------------------------
 INDEX          alter index LA.SYS_C0015774 rebuild tablespace TBS_TARGET_IDX;
 INDEX          alter index LA.IDX_LOG_ID_FOUR rebuild tablespace TBS_TARGET_IDX;
 LOBINDEX
 LOBINDEX
 LOBSEGMENT     alter table CT.LOG_B move lob (XML) store as (tablespace TBS_TARGET_DT);
 LOBSEGMENT     alter table LA.LOG_M move lob (XML) store as (tablespace TBS_TARGET_DT);
 TABLE          alter table LA.LOG_EVENTS_BK move tablespace TBS_TARGET_DT;
 TABLE          alter table LA.LOG_POLICY move tablespace TBS_TARGET_DT
And you have to explain why you don't need a DDL to move the LOBINDEXes?



January - resource busy error

Level (*) : 2
Topic (*) : DBA



You are in Oracle 10g and you launch this command:
 alter table EMP initrans 5; 

 ERROR at line 1:
 ORA-00054: resource busy and acquire with NOWAIT specified
 ORA-06512: at line 3
You have to write an anonymous block that tries to do this operation 10 times.
For example, you launch it and, at the third attempt, the command was successful, so on the screen you see:
 Success: No
 Success: No
 Success: YES
Otherwise, which Oracle feature can you use if you are in Oracle 11g?




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

   

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.