Common SQL statements for Oracle Database Management

The CHUNKS column indicates the number of available idle blocks in the tablespace (each idle block is composed of some continuous Oracle data blocks). If there are too many idle blocks, such as average to each number

The CHUNKS column indicates the number of available idle blocks in the tablespace (each idle block is composed of some continuous Oracle data blocks). If there are too many idle blocks, such as average to each number

/*
The CHUNKS column indicates the number of available idle blocks in the tablespace (each idle block is composed of several consecutive Oracle data blocks). If there are too many idle blocks, for example, if there are more than 100 data files on average, the fragmentation of the tablespace is serious.
*/
Select tablespace_name,
Count (*) chunks,
Max (bytes/1024/1024) max_chunk
From dba_free_space
Group by tablespace_name;

/*
If the database uses Oracle jobs to complete some scheduled jobs, check the running status of these jobs:
Select job, log_user, last_date, failures
From dba_jobs;
If the FAILURES column is a number greater than 0, the JOB fails to run and further checks are required.
*/
Select job, log_user, last_date, failures from dba_jobs;

/*
DBAs should regularly check the database connection to check whether the number of sessions established with the database is normal. If too many connections are established, the database resources will be consumed. At the same time, DBA may need to manually clean up some "dead" connections.
Note: In the preceding example, sessions with SID ranging from 1 to 7 (the USERNAME column is empty) are the background processes of Oracle. do not perform any operations on these sessions.
*/
Select sid, serial #, username, program, machine, status
From v $ session;

/*
DBAs should regularly check the database connection to check whether the number of sessions established with the database is normal. If too many connections are established, the database resources will be consumed. At the same time, DBA may need to manually clean up some "dead" connections.
Note: sessions with SID ranging from 1 to 7 (the USERNAME column is empty) are the background processes of Oracle. do not perform any operations on these sessions.
*/
Alter system kill session 'sid, SERIAL #';