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.
With the article “Query to see the size of all Oracle TABLESPACE” it will be possible to identify all the dimensions relating to the TABLESPACE. The selected fields are: TABLESPACE_NAME – Name of the tablespaces TOTAL_AUTO_MB – Total space of the Tablespace in MB TOTAL_NOW_MB – Total allocated space of Read more…
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…
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…
With the article “Query REDO LOG Switch Count,” we can quickly identify the time periods when the database is in full activity. Furthermore, in case you find numerous messages of “Checkpoint not complete” in the database’s alert log like the following message: Thu Nov 26 15:16:35 2019Thread 1 cannot allocate Read more…
In the article “Query REDO LOG file name and status,” we can see the names of the files composing the groups, their sizes, and the status of Oracle redo logs. Redo log files, essential for data recovery in case of corruptions, are sometimes not sized appropriately for the volume of Read more…
In the DDL Objects in ORACLE article, we have the ability to extract the DDL (Data Definition Language) of objects. The DDL of an object is the code by which the object is defined. In some cases, it is necessary to extract the creation code to verify the complete information Read more…
Con l’articolo dbms_ijob remove JOB Oracle, forniamo uno statement che permette di rimuovere definitivamente un JOB dal database Oracle. Il dbms_ijob è un package nascosto che viene richiamato dal package dbms_job. Questo permette la creazione, cancellazione o la messa in broken di un JOB Oracle ossia fermare la schedulazione del Read more…
With the article DBMS_IJOB BROKEN FALSE, we provide a statement that allows enabling a JOB of another user from the BROKEN state. In other words, we re-enable it with the scheduled scheduling as specified during creation. When a scheduled JOB in the Oracle database encounters a failure, it is rerun Read more…
With the article DBMS_IJOB BROKEN, we provide a statement that allows blocking the normal scheduling of a specific JOB. When a scheduled JOB in the Oracle database encounters a failure, the JOB is rerun after 1 minute. If it fails again, it is run after 2 minutes. If it fails Read more…
In the article Query JOB Running, we provide a query that allows identifying the running JOBS of all users in the database. The view that allows access to the information of the running JOBS is the table DBA_JOBS_RUNNING, from which it is possible to retrieve many useful information including the Read more…