How to view oracle database

Oracle database is a very popular relational database management system, mainly used for enterprise-level applications and data processing tasks. In the Oracle database, administrators need to frequently check the status and content of the database to ensure that it is running correctly and providing high-availability services. This article will introduce how to view the basic information, status and content of the Oracle database.

1. View database instance information

To view basic information about an Oracle database instance, you can use SQL*Plus or Oracle Enterprise Manager (OEM).

After using SQL*Plus to connect to a database instance, you can use the following command to view the instance name, version, startup time, and current connected user:

SELECT INSTANCE_NAME, VERSION, STARTUP_TIME, USERNAME
FROM v$instance;

In addition, you can also use the following command to view the instance CPU And memory usage:

SELECT name, value
FROM v$osstat
WHERE name IN ('NUM_CPUS', 'NUM_CPU_CORES', 'PHYSICAL_MEMORY_BYTES');

If you are using Oracle Enterprise Manager, you can view the basic information of the database instance, including version, startup time, status, etc., from the "Instance" tab on the main interface.

2. View database space status

Database space refers to the logical and physical storage space used when storing and managing data in the Oracle database. To view the database space status, you can use the following SQL statement:

SELECT tablespace_name, total_space, free_space, (total_space-free_space) used_space
FROM (
  SELECT tablespace_name, SUM(bytes) free_space
  FROM dba_free_space
  GROUP BY tablespace_name
), (
  SELECT tablespace_name, SUM(bytes) total_space
  FROM dba_data_files
  GROUP BY tablespace_name
)
WHERE tablespace_name NOT LIKE '%TEMP%'
ORDER BY tablespace_name;

This SQL statement will display the total space and free space for each table space, and calculate the value of the used space.

3. View database performance and health

Oracle database has many performance and health metrics, including number of connections, response time, resource usage, etc. The following are some commonly used monitoring instructions:

• View the current number of connections:

SELECT COUNT(*) FROM v$session;

• View the query response time:

SELECT SQL_ID, EXECUTIONS, ELAPSED_TIME/1000000 sec,
  BUFFER_GETS, DISK_READS, ROWS_PROCESSED
FROM v$sql
ORDER BY ELAPSED_TIME DESC;

• View disk I/O activity:

SELECT name, value 
FROM v$sysstat 
WHERE name IN ('physical reads', 'physical writes');

In addition, Oracle also provides many tools such as Enterprise Manager and Diagnostics Pack for viewing database performance and health.

4. View log files

Log files are files used to record system and application events in the Oracle database. They help administrators view system warnings and errors and track troubleshooting.

You can use the following SQL command to view the log file:

• View the warning log:

SELECT TIMESTAMP, MESSAGE FROM v$system_warnings;

• View the error log:

SELECT TIMESTAMP, MESSAGE FROM v$diag_alert_ext 
WHERE MESSAGE_TEXT LIKE '%ERROR%';

In Oracle Enterprise Manager, you can view system warning and error information in the "Warnings and Reactions" tab.

5. View sessions and locks

Session refers to the session information and status that is maintained in Oracle database in contact with client requests. To view the status and content of the current session, you can execute the following command:

SELECT s.sid, s.status, s.username, s.osuser, s.machine, 
       s.program, s.logon_time, m.sql_text
FROM v$session s 
LEFT OUTER JOIN v$sql m ON s.sql_id = m.sql_id
WHERE s.type != 'BACKGROUND';

This SQL statement will return the SID, status, user name, operating system user, computer name, program name, login time, and current session The SQL statement being executed (if any).

Locking is an important tool for controlling concurrent access in Oracle database. To view the current lock situation, you can use the following command:

SELECT l.session_id, s.username, s.osuser, s.machine, s.program, 
       l.lock_type, l.mode_held, l.mode_requested, o.object_type, 
       o.owner || '.' || o.object_name
FROM v$lock l 
JOIN v$session s ON l.session_id = s.sid 
JOIN dba_objects o ON l.id1 = o.object_id;

This SQL statement will display the currently locked session ID, user name, operating system user, computer name, program name, lock status, holding mode, request Schema, object type, and object name.

Summary

In the Oracle database, viewing basic information, status and content is something that system administrators often need to do. This article introduces some SQL commands and tools that can help administrators quickly view the status and contents of the database, as well as monitor performance and health. At the same time, Oracle Enterprise Manager also provides a very rich graphical interface and automated management tools, which can further help administrators better manage Oracle databases.

The above is the detailed content of How to view oracle database. For more information, please follow other related articles on the PHP Chinese website!