Oracle 11g 常用管理命令（用户、表空间、权限）
本文介绍 Oracle 最常用的基本管理命令，包括创建用户、表空间，权限分配等。
1、启动oracle数据库：
从root切换到oracle用户进入：
|
1 |
su - oracle |
进入sqlplus环境，nolog参数表示不登录：
|
1 |
sqlplus /nolog |
以管理员模式登录：
|
1 |
sqlplus / as sysdba |
启动数据库
|
1 |
startup; |
停止数据库
|
1 |
shutdown immediate |
远程连接数据库
|
1 2 |
sqlplus /nolog conn user/passwd@ip:1521/instance_name as sysdba |
也可以直接运行：
#启动数据库的脚本
|
1 |
dbstart |
#停止数据库的脚本
|
1 |
dbshut |
2、数据库监听：
启动监听服务：
|
1 |
lsnrctl start |
停止监听服务：
|
1 |
lsnrctl stop |
查看监听状态：
|
1 |
lsnrctl status |
3、用户权限管理：
注：以下命令都需要DBA权限。
创建用户：
|
1 |
create user username identified by 123456; |
赋予用户的表空间权限：
|
1 |
alter user username default tablespace username; |
或者两条命令合并为：
|
1 |
create user username identified by 123456 default tablespace username; |
注：刚刚创建完的新用户是没有任何权限的，甚至连登录数据库的权限都没有。这时使用conn 用户名/密码会提示没有权限。在新建一个用户之后还要对这个用户进行授权操作。当然是要使用有能力授权的用户，如sys、system。角色是指由系统权限集合。通常给某个用户授予权限时如果没有角色存在的话，那么需要一条一条的操作，角色的存在就是使得授权变得很方便。通常一个角色由多个系统权限组成。常用的角色有三个connect（7种权限）、dba、resource（在任何表空间建表）。
授予用户管理权限：
|
1 |
grant connect,resource,dba to username; |
删除用户
|
1 |
drop user username cascade; |
注：cascade参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯性的加此参数。
修改用户密码
|
1 |
alter user username identified by newpassword |
注意：在给其他用户修改密码时，需要具有DBA的权限或拥有alter user的系统权限。
查看当前用户的角色
|
1 2 |
select * from user_role_privs; select * from session_privs; |
查看当前用户的系统权限和表级权限
|
1 2 |
select * from user_sys_privs; select * from user_tab_privs; |
查看所有用户所拥有的角色
|
1 |
SELECT * FROM DBA_ROLE_PRIVS; |
查看所有角色
|
1 |
select * from dba_roles; |
oracle 查看当前用户名
|
1 2 |
show user; select user from dual; |
oracle 查看所有用户名
|
1 |
select * from all_users; |
查询当前用户的所有表
|
1 2 |
select * from user_tables; select * from dba_users; |
显示当前用户
|
1 |
show user; |
4、数据表及表空间：
创建表空间：
|
1 |
create tablespace username datafile '/u01/app/oracle/oradata/ORCL/username.dbf' size 300m autoextend on; |
说明：末尾带autoextend on参数表示当表空间大小不够用时会自动扩容，所有建议加上autoextend on参数。
删除表空间：
|
1 |
drop tablespace username including contents and datafiles; |
修改表空间大小（注：修改=可以增大，可以减小。）
|
1 |
alter database datafile '/u01/app/oracle/oradata/ORCL/username.dbf' resize 200m; |
增加表空间大小（注：增加=只能增大，不能减少。）
|
1 |
alter tablespace username add datafile '/u01/app/oracle/oradata/ORCL/username.dbf' size 2048m; |
查询数据库文件：
|
1 |
select * from dba_data_files; |
查询当前存在的表空间：
|
1 |
select * from v$tablespace; |
表空间情况：
|
1 |
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name; |
查询表空间剩余空间：
|
1 |
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name; |
查看表结构：
|
1 |
desc table; |
查看用户当前连接数：
|
1 |
select count(*) from sys.v_$session; |
pl/sql 代码块
|
1 2 3 4 5 6 7 |
[declare /*申明部分，一般是变量及常量*/] [ begin /*执行部分，流程结构控制，sql部分*/] [exception /*异常处理部分*/] end |
|
1 2 3 4 |
set serveroutput on //打开输出开关 begin dbms_output.put_line（'hello world!'）; //输出结果 end; |
修改oracle数据库连接数：（注：要重启数据库）
|
1 2 3 |
alter system set processes=1000 scope=spfile; shutdown immediate; startup; |
查看数据文件
|
1 |
select name from v$datafile; |
查看控件文件
|
1 |
select name from v$controlfile; |
查看日志文件
|
1 |
select member from v$logfile; |
查看temp表空间建立在哪个文件上在并格式化显示结果
|
1 |
select a.file_name from dba_temp_files a； |
或
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SET LINESIZE 1200 COL TABLESPACE_NAME FOR A30 COL FILE_NAME FOR A60 SELECT TABLESPACE_NAME AS TABLESPACE_NAME ,FILE_NAME AS FILE_NAME ,BLOCKS AS BLOCKS ,STATUS AS STATUS ,AUTOEXTENSIBLE AS AUTOEXTENSIBLE ,BYTES/1024/1024/1024 AS "FILE_SIZE(G)" ,DECODE(MAXBYTES, 0, BYTES/1024/1024/1024, MAXBYTES/1024/1024/1024) AS "MAX_SIZE(G)" ,INCREMENT_BY AS "INCREMENT_BY" ,USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES; |
查看表空间对应文件所在位置
|
1 |
select file_name , tablespace_name from dba_data_files; |
设置sql查询结果错行，可以通过设置每行显示宽度，或者是调整指定列显示宽度，linesize 默认是 80，例如：set linesize 100 修改行快为 100，column column_name format a8 修改 column_name 字段宽度为 8 个字符，详细查看文末说明。
查看表空间
|
1 |
select * from dba_tablespaces; |
查看用户和默认表空间的关系
|
1 |
select username,default_tablespace from dba_users; |
Oracle查询用户视图
|
1 |
select * from user_views; |
查询所有函数和储存过程：
|
1 |
select * from user_source; |
查看当前用户连接：
|
1 |
select * from v$Session; |
查看数据库名
|
1 |
SELECT NAME FROM V$DATABASE; |
查看表空间的名称及大小
|
1 2 3 4 |
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; |
查看表空间物理文件的名称及大小
|
1 2 3 4 5 6 |
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; |
查看回滚段名称及大小
|
1 2 3 4 5 6 7 8 9 10 |
SELECT segment_name, tablespace_name, r.status, (initial_extent / 1024) initialextent, (next_extent / 1024) nextextent, max_extents, v.curext curextent FROM dba_rollback_segs r, v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name; |
查看数据库库对象
|
1 2 3 |
SELECT owner, object_type, status, COUNT(*) count# FROM all_objects GROUP BY owner, object_type, status; |
查看数据库的版本
|
1 2 3 |
SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle'; |
查看数据库的创建日期和归档方式
|
1 |
SELECT created, log_mode, log_mode FROM v$database; |
查看表空间的使用情况
|
1 2 3 |
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name; |
|
1 2 3 4 5 6 7 8 9 |
SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes * 100) / a.bytes "% USED ", (c.bytes * 100) / a.bytes "% FREE " FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c WHERE a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name; |
显示表空间使用率
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT a.tablespace_name "表空间名", total "表空间大小", free "表空间剩余大小", (total - free) "表空间使用大小", total / (1024 * 1024 * 1024) "表空间大小(G)", free / (1024 * 1024 * 1024) "表空间剩余大小(G)", (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name |
按数据文件显示表空间使用率
|
1 2 3 4 5 6 7 8 9 10 |
select a.file_id "FileNo", a.tablespace_name "Tablespace_name", a.bytes/1021/1024/1024 "Gb", (a.bytes - sum(nvl(b.bytes, 0)))/1021/1024/1024 "Used", sum(nvl(b.bytes, 0)) "Free", sum(nvl(b.bytes, 0)) / a.bytes * 100 "%free" from dba_data_files a, dba_free_space b where a.file_id = b.file_id(+) group by a.tablespace_name, a.file_id, a.bytes order by a.tablespace_name; |
附：
启动关闭数据库参数解释：
SHUTDOWN有四个参数：NORMAL、TRANSACTIONAL、IMMEDIATE、ABORT。缺省不带任何参数时表示是NORMAL。
SHUTDOWN NORMAL：不允许新的连接、等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
SHUTDOWN TRANSACTIONAL：不允许新的连接、不等待会话结束、等待事务结束、做一个检查点并关闭数据文件。启动时不需要实例恢复。
SHUTDOWN IMMEDIATE：不允许新的连接、不等待会话结束、不等待事务结束、做一个检查点并关闭数据文件。没有结束的事务是自动rollback的。启动时不需要实例恢复。
SHUTDOWN ABORT：不允许新的连接、不等待会话结束、不等待事务结束、不做检查点且没有关闭数据文件。启动时自动进行实例恢复。
另外，对于NORMAL、TRANSACTIONAL、IMMEDIATE，DB Buffer Cache的内容写入了数据文件，没有提交的事务被回滚，所有的资源被释放，数据库被“干净”的关闭。
对于ABORT，DB Buffer Cache的内容没有写入数据文件，没有提交的事务也没有回滚。数据库没有dismount和关闭，数据文件也没有关闭。当数据库启动时，需要通过redo log恢复数据，通过回滚段对事务回滚，对资源进行释放。
STARTUP [FORCE] [RESTRICT] [PFILE= filename] [OPEN [RECOVER][ database] | MOUNT | NOMOUNT]
STARTUP OPEN：STARTUP缺省的参数就是OPEN，打开数据库，允许数据库的访问。当前实例的控制文件中所描述的所有文件都已经打开。
STARTUP MOUNT：MOUNT数据库，仅仅给DBA进行管理操作，不允许数据库的用户访问。仅仅只是当前实例的控制文件被打开，数据文件未打开。
STARTUP NOMOUNT：仅仅通过初始化文件，分配出SGA区，启动数据库后台进程，没有打开控制文件和数据文件。不能任何访问数据库。
STARTUP PFILE= filename：以filename为初始化文件启动数据库，不是采用缺省初始化文件。
STARTUP FORCE：中止当前数据库的运行，并开始重新正常的启动数据库。
STARTUP RESTRICT：只允许具有RESTRICTED SESSION权限的用户访问数据库。
STARTUP RECOVER：数据库启动，并开始介质恢复。
格式化参数
格式化数据的显示格式，可以通过column中有一属性是用来设置显示格式的
column 列名 format 属性
format的属性有以下的几个属性：
an ---- 代表显示的位数，n是一个正整数. an 后面还可以跟参数wra[ppen]|wor[d_wrappend]|tru[ncated]代表的是如果查询出来的数据的位数超过n位的话，是截断还是换行 默认下就是换行。
9 ---- 代表禁止显示前导0
0 ---- 代表禁止显示后导0
$ ---- 代表显示美元符号
L ---- 代表显示本地货币符号。该符号来源于NLS_CURRENCY初始化参数
. ---- 代表小数点位数
, ---- 代表分隔符
S ---- 代表符号位。正数“+”号，负数“－”号。