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.

- The UNDO tablespace or the undo retention are small: in these cases, the DBA have to get
  bigger the UNDO tablespace or the undo retention.
  If, using the db, they receive often the ORA-01555 probably this is the problem.

- They have a big transaction: in this case, the developers should use more “commit” or
  “rollback” in the code

- In the pr_problem there is a big query so you should check its explain plan and if you
  think the explain plan is not good, the developers (or you) should do the performance
  tuning of the query

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"

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

alter system set streams_pool_size=40M;

  select LASSI_SEQ.nextval 
  from dual;

  NEXTVAL
  -------
    10089

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

 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

 SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'PT';

 STATT
 -----
 ALL
 ALL

 2 rows.

 exec DBMS_STATS.unlock_table_STATS ('LASSI', 'PT')

 STATT
 -----


 2 rows.

 exec DBMS_STATS.gather_table_stats ('LASSI', 'PT')

 exec DBMS_STATS.lock_table_STATS ('LASSI', 'PT');

 SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'PT';

 STATT
 -----
 ALL
 ALL

  ORA-28007: the password cannot be reused

 alter user LASSI profile DEFAULT;
 alter user LASSI identified by lassi1; 
 alter user LASSI profile PROF_1;

You have the tables T1 and T2. Those tables have many columns.
You have to find a query that checks their structures 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") 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?

Solution

In Oracle 11g, you can find this information on SYS.USER$:

  SELECT name, password, ctime created
  FROM sys.user$;

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 (*) : 3
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 (waiting for 0,2 seconds between every attempt).
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

© 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.