Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the difference between sysdba, dba and sysoper". In daily operation, I believe many people have doubts about the difference between sysdba, dba and sysoper. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what is the difference between sysdba, dba and sysoper?" Next, please follow the editor to study!

Differences between sys and system users

1) the most important difference is that the importance of stored data is different

The base tables and views of all oracle data dictionaries of sys are stored in sys users. These base tables and views are essential to the operation of oracle and are maintained by the database itself. No user can change them manually. Sys users have roles or permissions such as dba,sysdba,sysoper and are the users with the highest privileges in oracle.

System users are used to store secondary internal data, such as management information about some features or tools of oracle. The system user has normal dba role permissions.

2) the second difference is the difference of permissions

System users can only log in to em as normal, unless you grant it sysdba system permissions or syspoer system permissions.

Sys users have "SYSDBA" or "SYSOPER" system permissions. Login to em can only use these two identities, not normal.

Log in to Oracle as a sys user and execute select * from clients PWFILES users. You can query users with sysdba permissions, such as:

SQL > select * from V_$PWFILE_USERS

USERNAME SYSDBA SYSOPER

SYS TRUE TRUE

Differences in permissions between Sysdba and sysoper systems

What's the difference between normal, sysdba and sysoper?

Normal is an ordinary user.

The other two, you'll know if you examine the authority they have.

Sysdba has the highest system permissions. After logging in, it is sys.

Sysoper is mainly used to start and shut down the database. After sysoper login, the user is public.

Sysdba and sysoper belong to system privilege, also known as administrative privilege, and have some system management level permissions such as database opening and closing. For more information on sysdba and sysoper permissions, please see the following table:

System permissions

Sysdba

Sysoper

Difference

Startup (start the database)

Startup

Shutdown (shut down the database)

Shutdown

Alter database open/mount/backup

Alter database open/mount/backup

None

Create database (create database)

None cannot create the database

Drop database (delete database)

None

Create spfile

Create spfile

Alter database archivelog (archive log)

Alter database archivelog

Alter database recover (restore database)

If system logs in normally, it is actually an ordinary dba user, but if you log in as as sysdba, the result is actually logged in as a sys user, which is similar to the feeling of sudo in Linux, as we can see from the login information. So after as sysdba connects to the database, the objects created are actually generated in sys. The same is true for other users. If you log in with as sysdba, you are also logged in as a sys user. See the following experiment:

SQL > create user strong identified by strong

The user has been created.

SQL > conn strong/strong@magick as sysdba

Connected.

SQL > show user

USER is "SYS"

SQL > create table test (an int)

The table has been created.

SQL > select owner from dba_tables where table_name='test'

No row selected / / because oracle is automatically uppercase when the table is created, so it does not exist when it is looked up in lowercase

SQL > select owner from dba_tables where table_name='TEST'

OWNER

-

SYS

The difference between dba and sysdba

What's the difference between dba and sysdba?

Before I explain this, I need to talk about the process of creating an oracle service.

Create instance → start instance → create database (system tablespace is required)

Startup process

Instance starts → to load database → to open database

Sysdba, which manages oracle instances, does not depend on the complete startup of the whole database. As long as the instance is started, it already exists, logs in as sysdba, loads the database, and opens the database. Only when the database is open, or when the entire database is fully started, does the dba role have a basis for existence!

At this point, the study of "what is the difference between sysdba, dba and sysoper" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.