Menu Bar

Wednesday, March 15, 2023

General Questions

 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.


No comments:

Post a Comment