Ques. 1): What are the components of an Oracle database's logical database structure?
Answer:
The following are the components of Oracle's logical database structure:
Tablespaces: Tablespaces are the logical storage units that make up a database. This tablespace is a collection of logical structures that are linked together. To be more specific, tablespace groupings are linked to logical structures.
Database Schema Objects: A schema is a set of database objects that belong to a single user. Tables, indexes, views, stored procedures, and other objects are among the objects. The user is the account in Oracle, and the schema is the object. It is also possible to have a schema without specifying a user in database platforms.
Ques. 2): What is the connection between the database, tablespace, and data file?
Answer:
An Oracle database has one or more tablespaces, which are logical storage units. Each tablespace in an Oracle database is made up of one or more datafiles. The complete data of databases is stored in these tablespaces. When we talk about datafiles, we're talking about the physical structure that tells the operating system which Oracle software is running.
Ques. 3): What is the difference between DB file sequential read and DB File Scattered Read ?
Answer:
DB File Scattered Read is related to index read, whereas DB File Sequential Read is related to whole table scan. DB File sequential read reads blocks into contiguous memory, whereas DB File dispersed read reads multiple blocks into buffer cache.
Ques. 4): Which variables should be addressed when establishing a table index? How do I choose a column for indexing?
Answer:
The size of the database and the amount of data determine how an index is created. If the table is vast and only a few data points are required for selection or reporting, an index must be created. Cardinality and frequent usage in the where condition of a select query are two primary reasons for selecting columns for indexing. Because configuring main key or unique key immediately creates unique index, the business rule also forces the creation of indexes like primary keys.
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.
Ques. 5): What does Oracle's ANALYZE command do?
Answer:
This command "Analyze" is used to conduct different operations on an index, table, or cluster. The following is a list of Oracle commands that use the ANALYZE command:
The Analyze command is used to find migrated and chained table or cluster rows.
It is used to verify an object's structure.
This assists in gathering statistics about the object that the user is using, which are subsequently put in the data dictionary.
It also aids in the deletion of statistics from the data dictionary that are used by an object.
Ques. 6): What is the DUAL table's data type?
Answer:
The Dual table is a single-column table in the Oracle database. Dummy is a single Varchar2(1) column in this table with the value 'X'.
Ques. 7): Is it possible to create an index online?
Answer:
YES. Indexes can be created and rebuilt online. This allows you to change basic tables while also building or rebuilding indexes on those tables. DML actions are permitted while the index is being built, but DDL operations are not permitted.
When constructing or rebuilding an index online, parallel execution is not supported.
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
Ques. 8): When the R/3 system is active, why is a small dump written during an offline backup?
Answer:
BRBACKUP terminates the database during an offline backup, however the present R/3 system is unaware of this. As a result, the first work process that loses its database connection creates a small dump. Until the database is available again, all work processes move into reconnect mode. As a result, because the database cannot be accessed, one (or more) brief dumps are usually produced during an offline backup.
Ques. 9): How can you track a user's password change in Oracle?
Answer:
Oracle only keeps track of the password's expiration date based on when it was last modified. You may discover when a password was last changed by listing the view DBA USERS.EXPIRY DATE and subtracting PASSWORD LIFE TIME. The PTIME column in the USER$ database (on which the DBA USERS view is based) can also be used to check the last password change time. However, if PASSWORD REUSE TIME and/or PASSWORD REUSE MAX are configured in a profile given to a user account, you can look up the password change date in the dictionary table USER HISTORY$.
SELECT user$.NAME, user$.PASSWORD, user$.ptime, user_history$.password_date
FROM SYS.user_history$, SYS.user$
WHERE user_history$.user# = user$.user#;
Ques. 10): What is Secure External password Store (SEPS) ?
Answer:
You can store password credentials for connecting to databases using SEPS by utilising a client-side Oracle wallet, which also stores signing credentials. This capability has been available since Oracle 10g. Thus, embedded username and passwords were no longer required in application code, scheduled jobs, or scripts. This decreases risk because passwords are no longer accessible, and password management policies can be implemented more quickly without having to alter application code whenever the login and password change.
Oracle RDMS Interview Questions and Answers
Ques. 11): Why do we require the CASCADE option when using the DROP USER command to drop a user, and why do "DROP USER" instructions fail when we don't use it?
Answer:
If a user has an object, you will not be able to delete that user without using the CASCADE option. The DROP USER command with the CASCADE option deletes the user and all associated items. Because this is a DDL command, rollback is not possible after it has been executed.
Ques. 12): What is the difference between Redo, Undo, and Rollback?
Answer:
When it comes to Redo, Rollback, and Undo, I always get a little confused. They all sound pretty much the same, or at least very similar.
Every Oracle database has a collection of redo log files (two or more). The redo log keeps track of all data changes, including both uncommitted and committed ones. Oracle saves archive redo logs in addition to online redo logs. In recovery scenarios, all redo logs are used. Rollback: More specifically, section rollback. The data in rollback segments is saved as it was before any modifications were made. The redo log, on the other hand, is a record of the inserts, updates, and deletions.
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.
Ques. 13): Do you have more than three Linux instances running? How do you figure out which shared memory and semaphores belong to which instance?
Answer:
Oracle provides an undocumented utility called Oradebug. The oradebug help command displays a list of oracle commands.
SQL>oradebug setmypid
SQL>oradebug ipc
SQL>oradebug tracfile_name
Ques. 14): Why aren't all Oracle faults recorded in the Alert Log?
Answer:
In the Alert Log, Oracle only logs critical problems. The majority of Oracle error codes are not recorded (unfortunately this may also include error codes that are genuinely critical). As a result, it's common to discover faults in the Oracle application that aren't visible in the Alert Log.
Ques. 15): There is no room available to add datafiles to enhance temp tablespace because it is completely full. What can you do to free up TEMP tablespace in that case?
Answer:
Closing some of the database's idle sessions will help you free up some TEMP space. You can also make use of:
Alter Tablespace PCTINCREASE 1' ;
'Alter Tablespace PCTINCREASE 0;
Ques. 16): What is the difference between row chaning and row migration?
Answer:
Row Migration: When an update to a row causes it to no longer fit on the block, it migrates (with all of the other data that exists there currently). A migration means that the entire row will be moved, with only the «forwarding address» remaining. As a result, the original block only has the new block's rowid and the entire row is transferred.
Row Chaining: A single database block cannot hold more than one row. For example, if your database uses a 4KB blocksize and you need to insert an 8KB row, Oracle will use three blocks and save the information in pieces. Row chaining can occur under the following circumstances: Tables with a rowsize greater than the blocksize. Chained rows are common in tables containing LONG and LONG RAW columns. Oracle breaks broad tables split into sections, therefore tables with more than 255 columns will have chained rows. Rather of having a forwarding address on one block and data on another, we now have data on two or more blocks.
Ques. 17): How can I erase a data file that I accidentally created?
Answer:
In most circumstances, you can use RESIZE or RENAME to fix a data file that was generated with the improper size or in the wrong location. You have the following alternatives if you want to drop the data file again:
A produced data file can only be erased during a tablespace reorganization up to and including Oracle 9i. There are no other viable alternatives.
As of Oracle 10g, an empty data file can also be dropped with the following command:
ALTER TABLESPACE DROP DATAFILE ' ';
If there are still extents in the data file, this command fails with ORA-03262. In this case, the affected segments must first be relocated so that the extents are released.
Ques. 18): What is the ideal file size for data files?
Answer:
It is impossible to provide a straightforward solution to this topic. In most cases, the size of the data files has no bearing on database activity. However, keep the following considerations in mind:
Make sure the Oracle parameter DB FILES is set high enough. Otherwise, once this limit is reached, new data files cannot be produced.
The fewer the datafiles, the faster they can be restored individually during a backup.
BEGIN BACKUP processes in online backups are likely to take longer the smaller the data files are, and hence the more data files there are.
Data files that are too large aggravate performance problems that are caused by inode locking, since parallel processes may become serialized on the data file inode.
On occasion, size restrictions may prevent the system from using data files that exceed a certain size (often 2GB).
Ques. 19): Why does the order of the online redo logs occasionally change?
Answer:
This redo log becomes the next redo log if the next online redo log is still archived and another redo log is available for overwriting. The order of the online redo logs is now altered. Because the alternative would be an archiver stuck, at least briefly, this behaviour is preferable.
Only if many archive processes run in parallel and do not run again when the redo logs are archived may this situation arise. To avoid this issue, see if the archiver's performance (I/O tuning) can be improved. You must also avoid getting an archiver stuck due to a fully operational archive file system.
This problem cannot occur if the number of archiver processes is limited to one by LOG_ARCHIVE_MAX_PROCESSES
Ques. 20): On my system, why do Oracle processes run as the sid>adm user?
Answer:
By default, the UNIX PS command displays the true user, not the actual user. As a result, having adm as the displayed user for Oracle processes is not a problem. The only thing that matters is that the Oracle executable has the appropriate permissions.
