Nuovo-Logo-ScriptDBA.com
  • Home
  • About
    • Our Story
    • Resume
  • How to
    • SQL Query In the SQL Query category, you will find numerous queries useful for administering the Oracle database. A SQL Structure Query Language query is a query to a database through SQL code, which is a standard language used to retrieve and manage information from relational databases. Oracle is an RDBMS Relational Database Management System or a DBMS Data Base Management System based on the relational model. This allows you to retrieve data from multiple tables by relating them to each other in order to have the result in a single output. A query consists of a code structure consisting of 2 phases: selection and origin. SELECT * FROM all_users; The third phase follows, which is the condition. We apply a filter to the queries to limit the rows to be recovered: SELECT * FROM all_users WHERE username = ‘ALEX’; This is a simple query that allows us to retrieve the information recorded in the all_users table relating to the ALEX user. On the Foundations of Oracle course, the SQL language in all its forms will be illustrated in detail.
      • Oracle sessions management
        • Oracle SID session query
        • Oracle User Sessions Query
        • Query Oracle sessions to date and time
        • Query to see the RMAN sessions
        • Query Oracle sessions processes transactions cursors
        • Query sessions holding LOCK
        • Query Oracle LOCK and number of LOCKED sessions
        • Query to Identify Sessions Blocked by Another Session
        • KILL Oracle USER sessions on RAC
        • KILL Oracle USER sessions
        • Query disconnect session Oracle
      • Oracle performance statement analysis
        • Query to extract the statement from SQL_ID
        • Query to extract statement from a subsequent SQL_ID EXPLAIN PLAN FOR the statement
      • ASM space management
        • Query to View ASM DiskGroup Spaces
        • ASM LUN CANDIDATE Query
        • Query to view the remaining time of REBALANCE
      • Tablespace management
        • Query size datafiles of a tablespace
        • Query to see the size of all Oracle TABLESPACE
        • Query to retrieve disk space allocated by DATAFILES but not used
        • Query to View TEMPFILE and TEMP TABLESPACE Sizes
        • Query to retrieve disk space allocated by DATAFILES but not used
      • Oracle object management
        • Oracle Objects Query
        • Query for INVALID Objects
        • Query for Compiling INVALID Objects
      • Oracle database Management of LOB objects
        • Oracle Table LOB Query
        • LOB Segment Oracle Query
      • Oracle INDEX management
        • Index Size Query
        • Partitioned Indexes Oracle Query
        • UNUSABLE Indexes Query in Oracle
        • UNUSABLE Partitioned Indexes Query in Oracle
        • Dynamic Query for Generating REBUILD INDEX Command
      • EXTENT management of an Oracle object
        • Query EXTENT Oracle number and size
        • Query EXTENT TABLE Oracle
        • Query Oracle objects with few EXTENTS
      • Users management
        • Query for Oracle User Password Status
        • Query for Oracle User Roles
        • Query for Oracle User Object Privileges
        • DDL for User Creation Roles and Privileges
        • Assignment of DEFAULT_TABLESPACE Quota
        • Query USER PRIVILEGES replicated
      • REDO LOG management
        • Query REDO LOG file name and status
        • Query REDO LOG Switch Count
      • DDL extract Oracle objects
        • DDL Objects in ORACLE
      • Oracle JOBS management
        • Query JOB Oracle status
        • Query JOB Running
        • DBMS_IJOB BROKEN
        • dbms_ijob remove JOB Oracle
        • DBMS_IJOB BROKEN FALSE
    • Comandi CRS
      • CRSCTL commands – CRS Oracle
        • CRS stop
        • CRS start
        • CRS STOP ALL: Complete shutdown of all CRS cluster resources
        • CRS START ALL: Complete startup of all CRS cluster resources
        • Disable CRS
        • Enable CRS
      • SRVCTL commands – CRS Oracle
        • srvctl stop database
        • srvctl start database
        • srvctl stop instance
        • srvctl start instance
        • srvctl stop listener
        • srvctl stop service
        • srvctl start service
        • srvctl relocate service
    • Tutorial Work in progres.
      • Linux Work in progres.
        • Basic Linux commands
      • Oracle Work in progres.
        • Oracle Multitenant Database Architecture
        • How to Extend an Oracle TABLESPACE
        • Export e import in data pump
      • SQL Server
  • English
    • Italian

Oracle sessions management

In the Oracle sessions management category, you will find numerous queries to identify useful information such as the USER, MACHINE, and PROGRAM of origin for a session. Additionally, it is possible to easily identify sessions that generate LOCK situations. Lastly, there are dynamic queries for creating the kill session command via the session’s SID and SERIAL.

An Oracle session is a logical entity that is generated when a login is made to the database. A single connection can have 0, 1, or more sessions established on it, thus generating multiple sessions.

When a table is the subject of DML operations, a LOCK occurs on the table. This prevents other operations from modifying the table. If other sessions need to write to the table that is the subject of DML operations, they are blocked until the operation that generated the LOCK is finished.

LOCKs can cause significant delays and sometimes to unlock the situation, it may be necessary to terminate the session that is keeping the table locked. Therefore, to unlock the LOCK situation, it is necessary to execute the KILL session command.

In the “Oracle SESSION Management” category, you will find numerous queries to identify useful information such as the USER, MACHINE, and PROGRAM of origin for a session. Additionally, it is possible to easily identify sessions that generate LOCK situations. Lastly, there are dynamic queries for creating the kill session command via the session’s SID and SERIAL.

Query Oracle sessions processes transactions cursors
Oracle sessions management

Query Oracle sessions processes transactions cursors

With the article “Query Oracle sessions processes transactions cursors”, we have the possibility to identify the maximum number of sessions or processes or transactions or cursors, their usage and percentage. Several times I have been contacted by the monitoring group due to the increase in the number of sessions. If Read more…

By Alessandro Caredda, 2 years7 March 2023 ago
Query Oracle LOCK and number of LOCKED sessions
Oracle sessions management

Query Oracle LOCK and number of LOCKED sessions

In the article “Query Oracle LOCK and number of sessions” will be of great help. Especially when the application group is stuck due to database errors and the manager is bombarding you with questions just to make you waste more time. In highly accessed databases, it may happen that, among Read more…

By Alessandro Caredda, 2 years7 March 2023 ago
Query Oracle sessions to date and time
Oracle sessions management

Query Oracle sessions to date and time

In the article “Query Oracle sessions to date and time”, we can control Oracle sessions before a certain date or time using a specific query. Sometimes, it can be difficult to locate the oldest sessions in the database due to the high number of sessions. The article provides a solution Read more…

By Alessandro Caredda, 2 years7 March 2023 ago
Query sessions holding LOCK
Oracle sessions management

Query sessions holding LOCK

With the article “Query sessions holding LOCK,” we can identify the session that is blocking access to other sessions on the contested object. We have already discussed in previous articles about disputes in the Oracle database: Query LOCK and number of Oracle sessions and… The main cause of disputes within Read more…

By Alessandro Caredda, 2 years7 March 2023 ago
Query to see the RMAN sessions
Oracle sessions management

Query to see the RMAN sessions

With the article “Query to see the RMAN sessions”, we have the possibility to verify if the RMAN backup is running or not. In case potential backup issues are reported, we can check if the backup schedule time matches the access time of the RMAN sessions on the database. If Read more…

By Alessandro Caredda, 2 years7 March 2023 ago
Oracle User Sessions Query
Oracle sessions management

Oracle User Sessions Query

With the article “Oracle User Sessions Query”, we retrieve information about the LOGON, STATUS, SID, software, and machine of all Oracle sessions for a particular user. The first lines of the query concern the formatting of the columns and will help us have a better visualization of the output as Read more…

By Alessandro Caredda, 2 years7 March 2023 ago
Uncategorized

Oracle SID session query

In the Oracle SID session query article talking about of Query to retrieve information about Oracle sessions having the session SID. This query is widely used by me when I have to analyze sessions that are creating problems, so if we have to identify, for example, a session that is Read more…

By Alessandro Caredda, 5 years21 March 2020 ago
Oracle sessions management

KILL Oracle USER sessions

If you’re looking to perform “KILL Oracle user sessions,” you’re in the right place! During the deletion phase of a SCHEMA, it often happens that sessions connected to the database hinder the process, generating the classic error ORA-01940: cannot drop a user that is currently connected. This situation typically occurs Read more…

By Alessandro Caredda, 6 years28 July 2019 ago
Oracle sessions management

KILL Oracle USER sessions on RAC

In the article KILL Oracle USER sessions on RAC is much loved by me because of its precious use, since through the INST_ID field, we are able to execute the KILL on each instance that makes up the Oracle RAC. In the numerous requests that arrive from the clients that Read more…

By Patrizia Gardis, 6 years2 March 2019 ago
Oracle sessions management

Query to Identify Sessions Blocked by Another Session

Query to Identify Sessions Blocked by Another Session Well, before diving into the article “Query to Identify Sessions Blocked by Another Session,” we’d like to introduce this website to you if you’re seeing it for the first time! Scriptdba is the first vertical website on Oracle databases completely in Italian. Read more…

By Patrizia Gardis, 6 years2 March 2019 ago
Login
  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
Recent Posts
  • Query to view the remaining time of REBALANCE
  • Query to extract statement from a subsequent SQL_ID EXPLAIN PLAN FOR the statement
  • Query size datafiles of a tablespace
  • Query to retrieve disk space allocated by DATAFILES but not used
  • Query Oracle sessions processes transactions cursors
Recent Comments
    Categories

    • About
    • Contacts
    • Download ScriptDBA.com
    • Home
    • How to
    • Pivacy Policy
    • References
    Hestia | Developed by ThemeIsle
    Don't have an account yet? Register Now

    Multilingual WordPress with WPML