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

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
# Start the database script
Dbshut
# Stop the database script

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: The following commands require DBA authority.
Create User:
create user Ittbank identified by 123456;
gives the user tablespace permissions:
alter user Ittbank default tablespace ittbank;;
or two commands merged 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? groupby 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
/* Declaration section, generally variable and constant */ ]
[begin
/* Execution section, process structure control,SQL Section */]
[exception
/* Exception Handling Section */]
End

set Serveroutput on// turn on the output switch
Begin
Dbms_output.put_line(' Hello world! ' ) ; // Output Results
End;
Modify the number of connections: (Note: To restart the database)
alter system set Processes=1000?scope=spfile;
Shutdown immediate;
startup;

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