41). A table has been created with below settings. What will be size of 4th extent ?

storage (initial 200k

next 200k

minextents 2

maxextents 100

pctincrease 40)

What will be size of 4th extent ?

“NEXT” Specify in bytes the size of the next extent to be allocated to the object.

Percent Increase allows your segment to grow at an increasing rate.

The first two extents will be of a size determined by the Initial and Next parameter (200k)

The third extent will be 1 + PCTINCREASE/100 times the second extent (1,4*200=280k).

AND The fourth extent will be 1 + PCTINCREASE/100 times the third extent (1,4*280=392k!!!), and so on…


42). What is DB Buffer Cache Advisor ?

The Buffer Cache Advisor provides advice on how to size the Database Buffer Cache to obtain optimal cache hit ratios.

Member of Performance Advisors –> Memory Advisor pack.


43). What is STATSPACK tool ?

STATSPACK is a performance diagnosis tool provided by Oracle starting from Oracle 8i and above.

STATSPACK is a diagnosis tool for instance-wide performance problems; it also supports application tuning activities by providing data which identifies high-load SQL statements.

Although AWR and ADDM (introduced in Oracle 10g) provide better statistics than STATSPACK,

users that are not licensed to use the Enterprise Manager Diagnostic Pack should continue to use statspack.

More information about STATSPACK, can be found in file $ORACLE_HOME/rdbms/admin/spdoc.txt.


44). Can you change SHARED_POOL_SIZE online ?

YES. That’s possible.

SQL>alter system set shared_pool_size=500M scope=both;

System altered.

It’s a lot quicker to bounce the instance when changing this.


45). Can you Redefine a table Online ?

Yes you can. In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:

  • Improve the performance of queries or DML
  • Accommodate application changes
  • Manage storage

Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table.

The mechanism is called online table redefinition.

When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process.

The table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users.

Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.

You can perform online table redefinition with the Enterprise Manager Reorganize Objects wizard or with the DBMS_REDEFINITION package.


46). Can you assign Priority to users ?

YES. This is achievable with Oracle Resource Manager.

DBMS_RESOURCE_MANAGER is the packcage to administer the Database Resource Manager.

The DBMS_RESOURCE_MANAGER package maintains plans, consumer groups, and plan directives. It also provides semantics so that you may group together changes to the plan schema.


47). You want users to change their passwords every 2 months. How do you enforce this ?

Oracle password security is implemented via Oracle “profiles” which are assigned to users.

PASSWORD_LIFE_TIME – limits the number of days the same password can be used for authentication

First, start by creating security “profile” in Oracle database and then alter the user to belong to the profile group.


1) creating a profile:

create profile all_users

limit

PASSWORD_LIFE_TIME 60

PASSWORD_GRACE_TIME 10

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_REUSE_MAX 0

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LOCK_TIME UNLIMITED;


2) Create user and assign user to the all_users profile

SQL>create user chuck identified by norris profile all_users;


3) To “alter profile” parameter, say; change to three months:

SQL>alter profile all_users set PASSWORD_LIFE_TIME = 90;


48). How do you delete duplicate rows in a table ?

There is a few ways to achieve that:

  • Using subquery to delete duplicate rows:

DELETE FROM table_name WHERE rowid NOT IN (SELECT max(rowid) FROM table_name GROUP BY id);

More ways:

  • Use RANK to find and remove duplicate table rows
  • Use self-join to remove duplicate rows
  • Use analytics to detect and remove duplicate rows
  • Delete duplicate table rows that contain NULL values

source: http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm


49). What is Automatic Management of Segment Space setting ?

Oracle9i New Feature Series: Automatic Segment Space Management

Automatic Segment Space Management (ASSM) introduced in Oracle9i is an easier way of managing space in a segment using bitmaps.

It eliminates the DBA from setting the parameters pctused, freelists, and freelist groups.

ASSM can be specified only with the locally managed tablespaces (LMT).

Oracle uses bitmaps to manage the free space. Bitmaps allow Oracle to manage free space more automatically.

Here is an example:

CREATE TABLESPACE example

DATAFILE ‘/oradata/ORA_SID/example01.dbf’ SIZE 50M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M

SEGMENT SPACE MANAGEMENT AUTO;

The storage parameters PCTUSED, FREELISTS and FREELIST GROUPS specified while creating a table are ignored by Oracle on a LMT ASSM tablespace. Oracle does not produce an error.

One huge benefit of having ASSM is to reduce the “Buffer Busy Waits” you see on segments.

Beware:

Using ASSM can hinder database DML performance, and most Oracle experts will use manual freelists and freelist groups.


50). What is the difference between DELETE and TRUNCATE statements ?

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows.

If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

DELETE will cause all DELETE triggers on the table to fire.


TRUNCATE removes all rows from a table. A WHERE clause is not permited. The operation cannot be rolled back and no triggers will be fired.

As such, TRUCATE is faster and doesn’t use as much undo space as a DELETE.