【oracle】oracle常用命令汇总

查看数据库状态（普通用户登录要保证数据库是open状态）

?

1	SQL> select status from v$instance;

 创建用户（新用户需要授予连接权限才能连上数据库）

?

1 2 3 4 5 6 7 8 9 10 11 12

SQL> create user user1 identified by redhat; SQL> conn user1/redhat ERROR: ORA-01045: user USER1 lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE. SQL> show user USER is "" SQL> conn / as sysdba Connected. SQL> grant connect to user1; SQL> conn user1/redhat; Connected.

删除用户

?

1	SQL> drop user user1 ;

查看系统拥有哪些用户

?

1	SQL> select * from all_users;

 执行sql文件（PATH表示文件的路径）

?

1	SQL> @PATH/filename.sql

 测试sql语句执行时间

?

1	SQL>set timing on ;

 查看当前数据库名

?

1	SQL> select name from v$database;

 查看侦听状态/关闭侦听/开启侦听

?

1 2 3

[grid@rac01 ~]$ lsnrctl status [grid@rac01 ~]$ lsnrctl stop [grid@rac01 ~]$ lsnrctl start

 关闭数据库

?

1	SQL> shutdown immediate;

 开启数据库

?

1	SQL> startup

 查看当前用户角色

?

1	select * from user_role_privs;

 查看数据文件

?

1	select * from dba_data_files;

 

 查看数据库是否为归档模式

?

1 2 3

SQL> select name,log_mode from v$database; SQL> archive log list; SQL> select archiver from v$instance;

 开启数据库的归档模式（只能在mount状态开启）

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

SQL> alter database archivelog; alter database archivelog * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance   SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount ORACLE instance started.   Total System Global Area  784998400 bytes Fixed Size          2257352 bytes Variable Size         541068856 bytes Database Buffers      239075328 bytes Redo Buffers            2596864 bytes Database mounted. SQL> SQL> alter database archivelog;   Database altered.   SQL> alter database open;   Database altered.   SQL> alter system archive log start;   System altered.

 关闭归档（同样要在mount状态下操作）

?

1	SQL> alter database noarchivelog;

-------------------------------------------------表空间-------------------------------

查看当前存在的表空间

?

1	select * from v$tablespace;

 查看表空间使用情况

?

1	SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; -- 单位是M

 查看用户默认表空间

?

1	SQL> select username,default_tablespace from dba_users;

 创建表空间（数据文件名一定是单引号引起来）

?

1	SQL> create tablespace test_tbsp datafile '/home/oracle/oradata/test_tbsp.dbf' size 100M;

修改用户表空间

?

1	SQL> alter user user1 default tablespace test_tbsp;

 查看表空间是否自动扩展

?

1	SQL> select file_name,autoextensible,increment_by from dba_data_files where tablespace_name='TEST_TBSP';

 表空间开启自动扩展

?

1	SQL> alter database datafile '/home/oracle/oradata/test_tbsp.dbf' autoextend on;

 表空间关闭自动扩展

?

1	SQL> alter database datafile '/home/oracle/oradata/test_tbsp.dbf' autoextend off;