1) Someone is running DML on a table and other hand somebody is trying to create INDEX . what happens? will lock occur or not ?
http://www.dba-oracle.com/t_concurrent_dml_ddl.htm
A simultaneous DDL table lock is held for operations that include the INSERT, UPDATE, DELETE,
and the SELECT FOR UPDATE DML operations. DML operations need DDL table locks to ensure that
some other transaction isn’t changing the table definition while modifying data. This means that a
table can’t be altered or dropped while an uncommitted transaction is still holding a table lock on the
table. All INSERT, UPDATE, and DELETE statements impose row exclusive locks.
Oracle uses row level locking for updates, inserts, and deletes, and that it also automatically imposes a row exclusive table lock during these operations.
2) Someone is running DML and somebody is trying to fetch data, how it works ? and which service and process helps here?
3) If I want to move a database from one home to another , how to do that ?
4) Once a data is COMMITTED ,will it still be there in UNDO or not ?
i) After commit, will there still be pre-modified data in UNDO ?
===> After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.
5) User wants to flashback the changes he has done (COMMITED) on the table ?
https://docs.oracle.com/en/database/oracle/oracle-database/tutorial-rew-tab/index.html?opt-release-19c
============================================================
1) Row movement must be enabled on a table before you can use Flashback Table.
SQL> alter table hr.regions enable row movement;
SQL>flashback table hr.regions to timestamp to_timestamp('2016-08-11 07:30:00', 'YYYY-MM-DD HH:MI:SS');
--> First check how many minutes UNDO_RETENTION is set and UNDO_MANAGED ==> must be AUTO (depending on these only flashback can be done, flashback must be set ON )
--> get the time from user , the time he has made those changes should fall under UNDO_RETENTION
--> now check if we can get back the table data or not using below command
SQL > select count(*) from tablename as of timestamp to_timestamp ('01-01-2022 12:45:25' , 'DD-MM-YY HH24:MI:SS
--.> take the export backup of current state of table
--> now ENABLE ROW MOVEMENT ,
--> flashback the table to timestamp
6) You are trying to shut immediate and its hung ? what can we do here ?
7 ) how to check how many rows are deleted (after commited or before commited) ?
8) how to read undo data ?
9) what is READ CONSISTENCY , BLOCK CORRUPTION, SNAPSHOT TOO OLD ERROR , DATA CONSISTENCY , CONSISTENT BACKUP , INCONSISTENT BACKUP ?
10) ROW CHAINING and ROW MIGRATION ?
11) HIGH WATER MARK (LOW/HIGH) ?
12) TABLE REORG/ TABLE SHRINK ?
13) If archive log gets full -> if its 0 free space, then immediately add space, clean the old archives, we're unable to connect to database, and no insert/update works here since no archives are generated ..existing users will be there but no users will be able to connect
14) what terminology we use to rollback a committed transaction ?
15) how do we add extra logfile to log-group or resize ?
16) Types of OPTIMIZERS ( cost based/ rule based) ?
17) Differences between 12c and 19c ?
18) How to rename database and pdb ?
19) How to enable trace? how does it help ?
20) Nologfile = Y
IF system datafile is FULL ?
If TEMP is fill ?
IF user is full ?
If UNDO is full ?
If SYSAUX is full ?
21) flashback_time and flashback_scn?
22) If I reset application schema password , will it be synced in DR ?
23) If I covert DB from Archive to Noarchive for 6 hours and get it back to archive after 6 hours, how will archives get applied on DR ?
24) How can we know that our database is running on shared mode or dedicated mode. Can we configure the database so that we can change the mode according to our need.
https://asktom.oracle.com/pls/apex/asktom.search?tag=dedicated-and-shared-mode
You can see how *sessions* are connected via V$SESSION
SQL> select sid, server from v$session;
SID SERVER
---------- ---------
1 DEDICATED
2 DEDICATED
3 DEDICATED
25) THE MAIN STAGES OF SQL PROCESSING
Parsing, Binding, Execution
26) Parsing
During the parsing stage, Oracle does several things to check your SQL statements:
• Oracle checks that your statements are syntactically correct. The server consults the data dictionary to check whether the tables and column specifications are correct.
• Oracle ensures that you have the privileges to perform the actions you are attempting through your SQL statements.
• Oracle draws up the execution plan for the statement, which involves selecting the best access methods for the objects in the statement.
======
Oracle Lock Types
Oracle locks can be broadly divided into the following types, according
to the type of object that is locked: DML locks, DDL locks, latches, internal locks, and distributed
locks.
DML Locks:
Any Oracle lock mode will permit queries on the table. A query will never block an update,
delete, or insert, and vice versa. An exclusive lock only permits queries on a table, and prevents users
from performing any other activity on it, like updating or deleting data. A row exclusive lock, on the
other hand, allows concurrent access to a table for updating, deleting, and inserting data, but
prevents any user from locking the entire table for exclusive use.
DDL Locks
As you’ve seen, Oracle automatically places DML locks on tables that are in the process of having
some of their rows modified by a transaction. In addition, such a transaction simultaneously holds
a table-level DDL lock on the table, which will prevent other transactions from altering or dropping
the table while its DML transactions aren’t yet completed.
You can also place DDL locks on tables when you are conducting a purely DDL operation,
without any accompanying DML transaction.
Allowing DDL Locks to Wait for DML Locks
SQL> ALTER SESSION SET ddl_lock_timeout = 30;
Q) In our production environment, sometimes table lock occurs. During table lock, i have identified the blocker and holder session, and sqls. While checking one session is on inactive session, and query for both sessions are like "SELECT" statements. How can we track each rows, datafiles level tracking, and actually why/how the one session cause table lock for other session??
The only way a SELECT statement will block another is if it has done: select for update.
Don't forget that for the inactive session, just because the *current* statement is a SELECT does not mean that was the statement the created the lock. A common scenario is:
Session 1:
==========
a- update some rows
b- run some selects
c- go inactive
Session 2:
==========
- blocked because session 1 has not committed the rows from the *update* in (a)
As you can see from the links above, Oracle does *not* keep a list of *rows* locked.
===============
Blocking Locks
A blocking lock occurs when a lock placed on an object by a user prevents or blocks other users from
accessing the same object or objects. The DBA_BLOCKERS table is useful in getting this information—
it tells you which sessions are currently holding locks on objects for which some other object
is presently waiting. You can combine the information in the DBA_BLOCKERS table with that in the
V$SESSION tables, to find out who is holding the blocking session. Here is the SQL statement:
SQL> SELECT a.username, a.program, a.sid, a.serial#
2 FROM v$session a, dba_blockers b
3 WHERE a.sid = b.holding_session;
==========
Deadlocks
Deadlocks occur in any RDBMS when two sessions block each other while each waits for a resource
that the other session is holding. This is a catch-22 situation, because the stalemate can’t be broken
by either session unilaterally. In such circumstances, Oracle steps in, terminates one of the sessions,
and rolls back its statement. Oracle quickly recognizes that two sessions are deadlocked and terminates
the session that issued the most recent request for a lock. This will release the object locks that
the other session is waiting for.
SQL> SELECT sid, blocking_session, username, event
2 FROM v$session
3* WHERE blocking_session_status = 'VALID';
SID BLOCKING_SESSION USERNAME EVENT
--- ---------------- -------- ----------------------------------
24 32 SALAPATI enq: TX - row lock contention
SQL>
The previous query shows that the user with the SID 24 is being blocked by user with the SID 32.
The event column shows the type of lock that the blocking session holds.
===============
Using SQL to Analyze Locks
It’s possible to examine the current locking situation in your instance by using SQL scripts. You may
have to first run the catblock.sql script, located in the $ORACLE_HOME/rdbms/admin directory, before
executing any locking-related SQL scripts for the first time in a database. This script will create
several important locking-related views, such as DBA_LOCKS, DBA_WAITERS, and
DBA_BLOCKERS.
Oracle provides a script called utllockt.sql that gives you a lock wait-for graph in a tree-structured
format showing sessions that are holding locks affecting other sessions.
============
You can also use OEM’s Hang Analysis page (go to Database Control Home Page ➤ Performance
➤ Additional Monitoring Links ➤ Hang Analysis) to find out the exact wait status of the blocking and
waiting sessions. The Hang Analysis page will show you the following:
• Instantaneously blocked sessions
• Sessions in a prolonged wait state
• Sessions that are hung
When there is a severe contention for locks in the instance, the Hang Analysis page will help you
identify the problems much more quickly than running a SQL script, which might actually worsen
the locking problems.