Oracle Database Common Operations commands

User's login:

[Username/password] [@server] [As Sysdba/sysoper] Example: Sys/pass as Sysdba

User's switchover:

[Connect] [Username/password] [@server] [As Sysdba/sysoper] Example: Connect system/pass

Show currently logged in User:

Show user

Display the fields in the data dictionary:

Desc dba_users

Desc table_spaces

Desc user_tablespaces

Desc user_users

Desc dba_data_files

Desc dba_temp_files

Desc Dba_role_privs

To create a user and specify a tablespace:

CREATE USER username identified by password
DEFAULT tablespace Test_data
Temporary tablespace test_temp;

Granting permissions to users:

GRANT
Create SESSION, create any TABLE, create any VIEW, create any INDEX,

CREATE any procedure,alter any TABLE, ALTER any PROCEDURE,

Drop any TABLE, drop no VIEW, drop any INDEX, drop any PROCEDURE,
SELECT any table, INSERT any table, UPDATE all table, DELETE any table
to username;

The role is granted to username:

GRANT role to username;

To set the default or temporary tablespace for a user:

ALTER USER username default| Temporary tablespace tablespace_name

To create a table space:

CREATE [temporary] tablespace tablespace_name tempfile| DataFile ' xx.dbf ' SIZE xx;

Example:

To create a temporary tablespace:

CREATE Temporary tablespace test_temp

Tempfile ' D:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf '

SIZE 32M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

To create a user table space:

　　　　CREATE tablespace Test_data
LOGGING
DataFile ' D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01. DBF '
SIZE 32M
Autoextend on
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;

To modify the state of a tablespace:

To set the online or offline status of a tablespace:

ALTER tablespace tablespace_name online| OFFLINE

To set tablespace read-only or read-write status:

ALTER tablespace tablespace_name READ only| READ WRITE

To modify a table space's data file:

Add data files:

ALTER tablespace tablespace_name ADD datafile ' xx.dbf ' SIZE xx

To delete a data file:

ALTER tablespace tablespace_name DROP datafile ' xx.dbf '

To delete a table space:

DROP tablespace tablespace_name [including CONTENTS]

　　

Oracle Database Common Operations commands