> What is the difference between DB file sequential read and DB File Scattered Read ?
DB file sequential read is associated with index read where as DB File Scattered Read has to do with full table scan.
The DB file sequential read, reads block into contiguous memory and DB File scattered read gets from multiple block and scattered them into buffer cache.
> Which factors are to be considered for creating index on Table? How to select column for index ?
Creation of index on table depends on size of table, volume of data. If size of table is large and we need only few data for selecting or in report then we need to create index. There are some basic reason of selecting column for indexing like cardinality and frequent usage in where condition of select query. Business rule is also forcing to create index like primary key, because configuring primary key or unique key automatically create unique index.
It is important to note that creation of so many indexes would affect the performance of DML on table because in single transaction should need to perform on various index segments and table simultaneously.
> Is creating index online possible ?
YES. You can create and rebuild indexes online. This enables you to update base tables at the same time you are building or rebuilding indexes on that table. You can perform DML operations while the index building is taking place, but DDL operations are not allowed.
Parallel execution is not supported when creating or rebuilding an index online.
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
> How to recover password in oracle 10g ?
You can query with the table user_history$. The password history is store in this table.
> How can you track the password change for a user in oracle ?
Oracle only tracks the date that the password will expire based on when it was latest changed. Thus listing the view
DBA_USERS.EXPIRY_DATE and subtracting PASSWORD_LIFE_TIME you can determine when password was last changed. You can also check the last password change time directly from the PTIME column in USER$ table (on which DBA_USERS view is based). But If you have PASSWORD_REUSE_TIME and/or PASSWORD_REUSE_MAX set in a profile assigned to a user account then you can reference dictionary table USER_HISTORY$ for when the password was changed for this account.
SELECT user$.NAME, user$.PASSWORD, user$.ptime, user_history$.password_date
FROM SYS.user_history$, SYS.user$
WHERE user_history$.user# = user$.user#;
> What is Secure External password Store (SEPS) ?
Through the use of SEPS you can store password credentials for connecting to database by using a client side oracle wallet, thiswall et stores signing credentials. This feature introduced since oracle 10g. Thus the application code, scheduled job, scripts no longer needed embedded username and passwords. This reduces risk because the passwords are no longer exposed and password management policies are more easily enforced without changing application code whenever username and password change.
> Why we need CASCADE option with DROP USER command whenever dropping a user and why "DROP USER" commands fails when we don't use it ?
If a user having any object then 'YES' in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command rollback cannot be performed.
> What is the difference between Redo,Rollback and Undo ?
I find there is always some confusion when talking about Redo, Rollback and Undo. They all sound like pretty much the same thing or at least pretty close. Redo: Every Oracle database has a set of (two or more) redo log files. The redo log records all changes made to data, including both uncommitted and committed changes. In addition to the online redo logs Oracle also stores archive redo logs. All redo logs are used in recovery situations.
Rollback: More specifically rollback segments. Rollback segments store the data as it was before changes were made. This is in contrast to the redo log which is a record of the insert/update/deletes. Undo: Rollback segments. They both are really one in the same. Undo data is stored in the undo tablespace. Undo is helpful in building a read consistent view of data.
You have more than 3 instances running on the Linux server ? How can you determine which shared memory and semaphores are associated with which instance?
Oradebug is undocumented oracle supplied utility by oracle. The oradebug help command list the command available with oracle.
> Why drop table is not going into Recycle bin ?
If you are using SYS user to drop any table then user's object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even we have already SET recycle bin parameter TRUE.
Select * from v$parameter where name = 'recyclebin';
Show parameter recyclebin;
> Temp Tablespace is 100% FULL and there is no space available to add datafiles to increase temp tablespace. What can you do in that case to free up TEMP tablespace ?
Try to close some of the idle sessions connected to the database will help you to free some TEMP space. Otherwise you can also use
'Alter Tablespace PCTINCREASE 1' followed by 'Alter Tablespace PCTINCREASE 0'
> What is Row Chaning and Row Migration ? Row Migration:
A row migrates when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.
A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces.
So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.
> How to find out background processes ?
SQL> select SID,PROGRAM from v$session where TYPE='BACKGROUND';
SQL> select name,description from V$bgprocess;