Oracle 11g Common management commands (user, table space, permissions)

PS: The following are the most common basic management commands for Oracle 11g, including creating users, table spaces, permission assignments, and so on. The following commands I have verified the operation, and added my summary and instructions.

1. Start the Oracle database:

Switch from root to Oracle user access:
su - oracle

Entering the Sqlplus environment, the Nolog parameter indicates no login:
sqlplus /nolog

Log in in Administrator mode:
sqlplus / as sysdba

Start the database
startup;
Stopping the database
shutdown immediate
Remote Connection Database
sqlplus /nolog
conn sys/[email protected]:1521/orainstance as sysdba
You can also run directly:
dbstart
#启动数据库的脚本
dbshut
#停止数据库的脚本

Reference:
startup [Force][restrict][nomount][migrate][quiet]
[Pfile=]
[Mount [exclusive] x |
Open
]

shutdown

NOTE: To configure Oracle under Linux as a service self-boot, see:
(original) CentOS Linux configuration Oracle 11GR2 for System Services auto-start

2, database monitoring:

To start the monitoring service:
lsnrctl start
To stop the monitoring service:
lsnrctl stop
To view the listening status:
lsnrctl status
3, User Rights Management:

Note: DBA authority is required for the following commands.
To create a user:
create user ittbank identified by 123456 ;
To give the user tablespace permissions:
alter user ittbank default tablespace ittbank;;
or two commands to merge into:
create user ittbank identified by 123456 default tablespace ittbank;

Note: New users that have just been created do not have any permissions or even permissions to log on to the database. At this point, using the conn username/password will prompt for no permissions. This user is also authorized after a new user is created. Of course, you want to use a user with the ability to authorize, such as SYS, System. A role is defined by a collection of system permissions. Usually when a user is granted permission, if no role exists, then a single action is required, and the role exists to make authorization very convenient. Typically a role consists of multiple system permissions. The commonly used roles are three connect (7 permissions), DBA, resource (in any table space).

To grant user administrative privileges:
grant connect,resource,dba to ittbank ;

Delete User
drop user“name”cascade;
NOTE: The Cascade parameter is a cascade delete all objects of the user, often encountered such as the user has an object without this parameter user can not delete the problem, so habitually add this parameter. The quotation marks for "name" are all the same.

Modify User Password
password ittbank(in the case where the user is already connected) or
alter user ittbank identified by newpassword
Note: When you modify a password for another user, you need to have DBA authority or have alter user's system permissions.

View the role of the current user
select * from user_role_privs;
select * from session_privs;
View system and table-level permissions for the current user
select * from user_sys_privs;
select * from user_tab_privs;
Querying user tables
select name from dba_users;
Modify User Password
alter user "name" identified by "password";
Show Current User
show user;

4. Data Sheet and table space:

To create a table space:
create tablespace ittbank datafile ‘/u01/app/oracle/oradata/ORCL/ittbank.dbf‘ size 300m autoextend on;
Note: The end with the Autoextend on parameter indicates that when the tablespace size is not sufficient, it will automatically expand, all the recommendations plus the autoextend on parameter.

To delete a table space:
drop tablespace ittbank including contents and datafiles;
Modify table space Size (Note: Modify = can be increased and can be reduced.) ）
alter database datafile ‘/u01/app/oracle/oradata/ORCL/ittbank.dbf‘ resize 200m;
Increase table space Size (Note: increase = can only increase, cannot be reduced.) ）
alter tablespace ittbank add datafile ‘/u01/app/oracle/oradata/ORCL/ittbank.dbf‘ size 2048m;
Querying database files:
select * from dba_data_files;
To query the table space that currently exists:
select * from v$tablespace;
Table Space Conditions:
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
Query table space remaining space:
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
To view the table structure:
desc table;
To view the number of users ' current connections:
select count(*) from sys.v_$session；
pl/sql
[declare
/*申明部分，一般是变量及常量*/]
[ begin
/*执行部分，流程结构控制，sql部分*/]
[exception
/*异常处理部分*/]
end

set serveroutput on //打开输出开关
begin
dbms_output.put_line（‘hello world!‘）; //输出结果
end;
Modify the number of connections: (Note: To restart the database)
alter system set processes=1000 scope=spfile;
shutdown immediate;
startup;

This article turns from http://www.ha97.com/4981.html

Oracle 11g Common management commands (user, table space, permissions)