Knowledge about common oracle database system users, logon statements, and logon users.

Knowledge about common oracle database system users, logon statements, and logon users.
Common system users (permission arrangement)

Sys> system> sysman> scott

Scott default password tiger

Logon statement:

System/password or connect sys/password as sysdba

View logon users

View Current user: show user

View user data dictionary: desc dba_users

View the user information in the data dictionary: select username from dba_users;

Enable scott user

Enable user: alter user username account unlock;

Data Dictionary viewed by administrator users: dba_tablespaces, user_tablespaces, dba_users, and user_users data dictionary viewed by common users: user_tablespaces

Destcba_tablespaces

View the user's tablespace (in the corresponding data dictionary)

Select tablespace_name from dba_tablespaces | user_tablespaces;

View the default or temporary tablespace of a user (SYSTEM)

Select default_tablespace, temporary_tablespace from dba_users where username = 'system ';

Note: When searching, the field value must be in uppercase!

Set the user's default or temporary tablespace

Alter user username default | temporary tablespace tablespace_name;

For example, alter user system default tablespace system;

Create a tablespace [temporary tablespace]

CREATE [TEMPORARY] TABLESPACE tablespace_name DATAFILE | TEMPFILE 'xxx. dbf' SIZE xx;

Example: create tablespace test1_tablespace DATAFILE 'test1file. dbf' SIZE 10 m;

Example: create temporary tablespace temptest1_tablespaceTEMPFILE 'tempfile1. dbf' SIZE 10 m;

Find the path of the tablespace data file (using the data dictionary dba_data_files)

Desc dba_data_files

Select file_name from dba_data_files where tablespace_name = 'test1 _ tablespace ';

Note: When searching, the field value must be in uppercase!

View the tablespace status

Destcba_tablespaces

Select status from dba_tablespaces where tablespace_name = 'test1 _ tablespace ';

Note: When searching, the field value must be in uppercase!

Modify tablespace

Modify the tablespace status

OFFLINE: alter tablespace tablespace_name ONLINE | OFFLINE;

For example, alter tablespace test1_tablespace OFFLINE;

READ/WRITE status: alter tablespace tablespace_name read only | read write;

Example: alter tablespace test1_tablespaceREAD WRITE; (Note: equal to ONLINE)

Add tablespace data files

Alter tablespace tablespace_name add datafile 'xx. dbf' SIZE xx;

Example: alter tablespace test1_tablespaceADD DATAFILE 'test2 _ file. dbf' SIZE 10 m;