"SYS" is a magic user

“SYS” is a magic User?

Answer: Yes, but why? Why everyone recommended to “don’t use sys” user for database activities.

Because:

1. SYS (SYSDBA) is super and top most privileged user in Oracle Database.

2. We Can’t LOCK user SYS account.

Consider:
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user scott account lock;

User altered.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
SQL> conn scott/tiger
ERROR:
ORA-28000: the account is locked

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter user SYS account LOCK;

User altered.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Pr
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>

3. We Can’t set TRANSACTION READ ONLY for SYS user.

Consider:
SQL> conn scott/tiger
Connected.
SQL> create table X as select * from all_objects where rownum =1;

Table created.

SQL> set TRANSACTION READ ONLY;

Transaction set.

SQL> delete from X;
delete from X
*
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY
transaction


SQL> conn sys as sysdba
Enter password:
Connected.
SQL> create table X as select * from all_objects where rownum =1;

Table created.

SQL> set TRANSACTION READ ONLY;

Transaction set.

SQL> delete from X;

1 row deleted.

SQL> commit;

Commit complete.

4. When we use SYS (SYSDBA) user.

1. When we can’t do database work with ANY OTHER user except SYS (SYSDBA)

1. Database Full Recovery (FULL, UNTIL CANCEL, UNTIL TIME, UNTIL SCN)
2. Change Database Character Set
3. Create Database
4. Drop Database

Note: For above work we must need SYSDBA (SYS) user.

When we use SYS (SYSOPER) user

1. Database Full Recovery (Not incomplete recovery)
2. Perform Database SHUTDOWN/STARTUP
3. Create SPFILE
4. Alter database OPEN/MOUNT/BACKUP
5. Includes the RESTRICTED SESSION privilege
6. Alter database ARCHIVELOG

Note: For above work we should use SYSOPER (SYS) user. And except above work if we want to perform normal database activity operation then Create Separate User and Grant DBA role and use that user.