Shulou(Shulou.com)05/31 Report--

This article is to share with you about the common commands of oracle 11g. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. Monitor

Initiate monitoring

Lsnrctl start

Stop monitoring

Lsnrctl stop

View monitoring status

Lsnrctl status

two。 Start

Use oracle users to enter

Su-oracle

Run the sqlplus command to enter the sqlplus environment. Parameter nolog means not logging in.

Sqlplus / nolog

Enter in administrator mode

Conn / as sysdba

Start the database

Startup

Stop the database

SHUTDOWN IMMEDIATE

Remotely connect to the database

Sqlplus / nolog

Conn sys/sys@IP:1521/orainstance as sysdba

You can also run it directly:

Dbstart / / start the database script

Dbshut / / stop the database script

Reference:

STARTUP [FORCE] [RESTRICT] [NOMOUNT] [MIGRATE] [QUIET]

[PFILE=]

[MOUNT [EXCLUSIVE] x |

OPEN

]

SHUTDOWN

3. User management

Create a user

Create user "username" identified by "userpassword"

Note: table space can be taken after it.

Delete user

Drop user "username" cascade

Note: the cascade parameter is cascaded to delete all the objects of the user. It is often encountered that if the user has an object and does not add this parameter, the user cannot delete it, so it is customary to add this parameter.

Authorization

Grant connect,resource,dba to "username"

View the role of the current user

Select * from user_role_privs

Select * from session_privs

View the system permissions and table-level permissions of the current user

Select * from user_sys_privs

Select * from user_tab_privs

Query user table

Select username from dba_users

Modify user password

Alter user "username" identified by "password"

Show current user

Show user

4. Table and table space

Create a tablespace

CREATE TABLESPACE data01 DATAFILE'/ oracle/oradata/db/DATA01.dbf' SIZE 500m

Delete tablespace

DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES

Modify tablespace size

Alter database datafile'/ path/NADDate05.dbf' resize 100m

Increase tablespace

ALTER TABLESPACE NEWCCS ADD DATAFILE'/ u03 SIZE oradata SIZE 4896m

Query database files

Select * from dba_data_files

Query the currently existing tablespace

Select * from v$tablespace

Tablespace situation

Select tablespace_name,sum (bytes) / 1024 Universe 1024 from dba_data_files group by tablespace_name

Query tablespace remaining space

Select tablespace_name,sum (bytes) / 1024 Universe 1024 from dba_free_space group by tablespace_name

View table structure

Desc table

Modify the number of connections: to restart the database

Alter system set processes=1000 scope=spfile

Shutdown immediate

Startup

View the number of users' current connections

Select count (*) from sys.v_$session

PL/SQL

[DECLARE

/ * declaration part, usually variables and constants * /]

[BEGIN

/ * execution part, process structure control, SQL part * /]

[EXCEPTION

/ * exception handling part * /]

END

SET SERVEROUTPUT ON / / turn on the output switch

BEGIN

DBMS_OUTPUT.PUT_LINE ('Hello worldview'); / / output the result

The above are the common commands of oracle 11g, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.