How to view oracle database

Oracle database is one of the most widely used enterprise-level relational database management systems in the world. It provides a safe, reliable, and high-performance data management platform. In enterprise business systems, Oracle databases occupy a very important position. They provide powerful data storage, data management, data backup and data recovery functions. This article will introduce how to view the Oracle database and help administrators better manage and use the database in daily work.

1. View database instances

In Oracle database, each instance represents the process and memory structure of a database during runtime. An Oracle database can have multiple instances, each instance has its own cache area, shared pool, redo log buffer and other structures. Therefore, when managing and using Oracle database, the first step is to check the database instance and confirm which instance we need to operate.

Viewing Oracle instances can be achieved by using command line tools or GUI tools. In the command line, we can use the following command to view:

ps -ef|grep pmon

This command will list all running processes, including the pmon daemon of the Oracle instance. The pmon process is Oracle's process monitoring program, and each instance has its own pmon process. By looking for the pmon process, we can find the instance name.

The output results will include the pmon process and instance name of each instance, such as:

oracle 13158 1 0 10:56 ? 00:00:01 ora_pmon_DB

where DB is the instance name. As you can see, the instance name appears in the command line in uppercase letters, which is the naming convention of the Oracle database.

If we use the GUI tool, we can open Oracle Enterprise Manager (OEM), select Instance->Management in the menu, and you can see the list of all instances.

2. Check the database version

After establishing the instance we need to operate, the next step is to check the database version. The database version is a very important piece of information, which determines the tool version we use, application compatibility and other factors.

In Oracle, you can view the current database version through the following command:

select * from v$version;

This command will display some information about the database version, including database name, version, character set, etc. For example:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

In the above example, we can see that the current database uses Oracle Database 12c Enterprise Edition, the version number is 12.2.0.1.0, and some other system information.

In addition to using the command line, you can also check the database version through OEM. Open the instance in the OEM interface and find the "Database Version" column to view the version information of the current database.

3. View table space

Table space is the basic management unit for managing storage space in Oracle. It contains one or more database files (datafiles), which are where the database actually stores data. When managing and optimizing database performance, we need to pay attention to table space usage. Checking the table space can help us understand the current status of the table space and take timely measures to solve some problems of insufficient space or improper use.

In the Oracle database, you can check the table space usage through the following command:

SELECT tablespace_name, 
       file_name, 
       bytes / 1024 / 1024 AS MB_SIZE, 
       autoextensible, 
       MAXBYTES / 1024 / 1024 AS MAX_MB_SIZE 
FROM   dba_data_files;

This command will list the names, file names, allocated space, and whether automatic Expansion, maximum support space and other information. For example:

SYSTEM /u01/oracle/data/system01.dbf 694.5 YES 32767
USERS /u01/oracle/data/users.dbf 5 YES 32767

In the above example, we can see some basic information about the system table space and user table space, including their respective file names, current sizes, whether they support automatic expansion, the maximum supported size, etc. .

You can also view table spaces in OEM. After entering the OEM interface, find the "Table Space" column and you can view the usage of all table spaces.

4. View database users and permissions

In the Oracle database, in addition to administrator users, other users are also very important management objects. Viewing database users can help us determine whether there are redundant users in the database and what permissions these users have.

In Oracle, you can view the current database user through the following command:

SELECT username, 
       created, 
       account_status 
FROM   dba_users;

This command will list all database users, creation time and account status information. For example:

SYS 27-OCT-03 OPEN
SYSTEM 27-OCT-03 OPEN

In the above example, we can see that there are two users SYS and SYSTEM in the current database.

In addition to viewing users, we can also view database roles and permissions information. In Oracle, a role is a set of users and permissions that provides users with special access rights. The following command can list all roles in the current database:

SELECT * FROM dba_roles;

This command will list all role information, including role name, creation time, role type, etc. For example:

CONNECT 28-SEP-20 DEFAULT
RESOURCE 28-SEP-20 DEFAULT

In the above example, we can see that there are two roles, CONNECT and RESOURCE, in the current database.

In addition, in Oracle, we can also view the permission information of users or roles. The following command can list the permission information of the specified user:

SELECT * FROM dba_sys_privs WHERE grantee = 'user_name';

This command will list the system permission information owned by the user_name user. For example:

SELECT * FROM dba_sys_privs WHERE grantee = 'SCOTT';

GRANTEE GRANTED_ROLE PRIVILEGE ADM COM INH
SCOTT JDEV RESOURCE CREATE CLUSTER NO NO
SCOTT CONNECT CREATE SESSION YES NO NO
SCOTT RESOURCE CREATE VIEW NO NO NO

In the above example, we can see some permission information owned by user SCOTT.

在OEM界面中，我们也可以查看数据库用户和权限信息。打开OEM界面，找到「安全性」一栏，就可以查看数据库用户、角色以及权限信息。

五、查看数据库对象

在Oracle数据库中，对象是数据的抽象概念，它代表着存储在数据库中的数据实体。常见的数据库对象包括表、视图、索引等。查看数据库对象可以帮助我们管理和维护数据库，及时发现一些问题，以便有效地解决。

在Oracle中，可以通过以下命令列出当前数据库中的所有对象：

SELECT owner,
       object_name,
       object_type,
       created
FROM   dba_objects
WHERE  owner NOT IN ('SYS', 'SYSTEM')

这个命令将会列出所有不属于SYS和SYSTEM两个用户的对象，包括对象所有者、对象名称、对象类型以及创建时间。例如：

SCOTT EMP TABLE 02-MAR-18
SCOTT DEPT TABLE 02-MAR-18

以上例子中，我们可以看到SCOTT用户创建了EMP和DEPT两个表。

在OEM界面中，我们也可以查看数据库对象信息。打开OEM界面，找到「数据库对象」一栏，就可以查看所有对象的信息，包括对象名称、对象类型、拥有者等信息。

六、结论

在本文中，我们介绍了如何查看Oracle数据库。通过查看数据库实例、版本、表空间、用户、权限和对象信息，我们可以更好地管理和维护数据库。当然，这些命令和操作只是我们管理Oracle数据库的基础。在日常工作中，我们还需要更深入的了解和掌握一些技巧，以便更好地满足企业的数据库管理需求。

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!