目录
- 用户操作语句
- 表空间操作语句
- 数据文件操作语句
- 数据表操作语句
- 数据库属性操作语句
1. 用户操作语句
-
查看scott用户的默认表空间、临时表空间
select username,default_tablespace,temporary_tablespace from dba_users where username = 'SCOTT'; -
查看scott用户的系统权限
select username,privilege,admin_option from user_sys_privs where username = 'SCOTT'; -
查看赋予scott用户的对象权限
select grantee,owner, table_name, t.grantor, t.privilege, t.grantable, t.hierarchy from dba_tab_privs t where t.grantee = 'SCOTT' ; -
查看授予了scott的角色权限
select t.grantee,t.granted_role, t.admin_option, t.default_role from dba_role_privs t where t.grantee = 'SCOTT'; 或者 select * from user_role_privs t -
查看scott用户使用了哪些表空间
select t.table_name, t.tablespace_name from dba_all_tables t where t.owner = 'SCOTT' ; 或者 select table_name, tablespace_name from user_tables; -
查看当前用户拥有的权限
select t.privilege from session_privs t -
查看赋给用户(GDYXHD)于对象操作的一些权限
select * from table_privileges t1 where t1.grantee = 'GDYXHD' -
用户锁定与解锁
# 解锁 alter user scott account unlock; # 锁定 alter user scott account lock; -
查看角色(resource)权限的 系统权限
select * from role_sys_privs t1 where t1.role = 'RESOURCE' -
查看角色(DBA)被赋予的 角色权限
select * from role_role_privs t where t.role = 'DBA' -
查看角色(DBA)被赋予的对象权限
select * from role_tab_privs t1 where t1.role = 'DBA' -
添加用户及用户授权
# 创建用户“DATACENTER”，并指定其表空间 CREATE USER DATACENTER IDENTIFIED BY VALUES 'CD47F3B2976521B1' DEFAULT TABLESPACE DATACENTER TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; # 修改用户角色 ALTER USER DATACENTER DEFAULT ROLE DBA,"CONNECT"; # 用户授权 GRANT DBA TO DATACENTER; GRANT "CONNECT" TO DATACENTER; GRANT INSERT ANY TABLE TO DATACENTER WITH ADMIN OPTION; GRANT DELETE ANY TABLE TO DATACENTER WITH ADMIN OPTION; GRANT UPDATE ANY TABLE TO DATACENTER WITH ADMIN OPTION; GRANT DROP ANY TABLE TO DATACENTER WITH ADMIN OPTION; GRANT SELECT ANY TABLE TO DATACENTER; GRANT UNLIMITED TABLESPACE TO DATACENTER; GRANT CREATE ANY SEQUENCE TO DATACENTER; -
修改用户
# 修改用户密码 alter user customer identified by '密码'; # 修改用户表空间 alter user default tablespace new_tablespace_name; # 修改用户角色 ALTER USER DATACENTER DEFAULT ROLE DBA,"CONNECT"; -
删除用户
# 级联删除用户下的数据表 drop user customer cascade; -
查看所有用户：
select * from all_users;
2. 表空间操作语句
-
查看表空间的名称及大小
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; -
查看表空间物理文件的名称及大小
SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; -
查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name; 或者 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; 或者 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; -
查看所有表空间
select tablespace_name from dba_data_files group by tablespace_name
3. 数据文件操作语句
-
查看回滚段名称及大小
SELECT segment_name, tablespace_name, r.status, max_extents, (initial_extent / 1024) initialextent, (next_extent / 1024) nextextent, v.curext curextent FROM dba_rollback_segs r, v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name; -
查看控制文件
SELECT NAME FROM v$controlfile; -
查看日志文件
SELECT MEMBER FROM v$logfile; -
查看用户数据总量
SELECT sum(bytes/(1024*1024*1024)) "用户数据大小(G)" from dba_segments where owner='datacenter'; 或者 select distinct owner, sum(bytes/(1024*1024*1024)) "用户数据大小(G)" from dba_segments group by owner order by "用户数据大小(G)" desc;
4. 数据表操作语句
-
查询系统所有对象
select owner, object_name, object_type, created, last_ddl_time, timestamp, status from dba_objects where owner=upper('scott') -
查看系统所有表
select owner, table_name, tablespace_name from dba_tables -
查看所有用户的表
select owner, table_name, tablespace_name from all_tables -
查看当前用户表
select table_name, tablespace_name from user_tables -
查看用户表索引
select t.*,i.index_type from user_ind_columns t, user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = "要查询的表" -
查看主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = upper('p') and au.table_name = "要查询的表" -
查看唯一性约束
select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = upper('u') and au.table_name = "要查询的表" -
查看外键
select * from user_constraints c where c.constraint_type = 'r' and c.table_name = "要查询的表" select * from user_cons_columns cl where cl.constraint_name = "外键名称" select * from user_cons_columns cl where cl.constraint_name = "外键引用表的键名" -
查看表的列属性
select t.*,c.comments from user_tab_columns t, user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = "要查询的表"
5. 数据库属性操作语句
-
查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count# FROM all_objects GROUP BY owner, object_type, status; -
查看数据库版本
SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle'; 或 select banner from sys.v_$version; -
查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database; -
查看oracle最大连接数
show parameter processes -
修改最大连接数
sql>alter system set processes=value scope=spfile –重启数据库 sql>shutdown force sql>start force -
查看当前连接数
select * from v$session where username is not null -
查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username -
查看活动的连接数
# 查看并发连接数 select count(*) from v$session where status='active' -
查看指定程序的连接数
# 查看jdbc连接oracle的数目 select count(*) from v$session where program='jdbc thin client' -
查看指定用户的连接数及中止用户连接
# 查看所有用户的当前连接数 select username,count(username) from v$session where username is not null group by username; # 查看某个用户的连接信息 select username, sid, serial# from v$session where username='cif'; # 杀死用户的连接信息 alter system kill session 'sid, serial#'; --说明 sid, serial#为v$session查询出的值 -
查看数据库安装实例(dba权限)
select * from v$instance -
查看运行实例名
show parameter instance_name -
查看数据库名
show parameter db_name -
查看数据库域名
show parameter db_domain 或者 select value from v$parameter where name='db_domain' -
查看数据库服务名
show parameter service_names; 或者 show parameter service; 或者 show parameter names; 或者 select value from v$parameter where name="service_names" -
查看全局数据库名
show parameter global -
查看系统所有的角色
# 系统中所有的角色 select * from dba_roles; # 系统中角色的权限信息 select * from dba_role_privs; # 系统中用户角色权限信息 select * from user_role_privs; -
修改数据库允许的最大连接数
alter system set processes = 300 scope = spfile; -
查看游标数量
Select * from v$open_cursor Where user_name='system'; -
查询数据库允许的最大连接数
select value from v$parameter where name = 'processes'; 或者 show parameter processes; -
查询数据库允许的最大游标数
select value from v$parameter where name = 'open_cursors' -
查询系统用户为每个会话打开的游标数
select o.sid, osuser, machine, count(*) num_curs from v$open_cursor o, v$session s where user_name = 'SYSTEM' and o.sid=s.sid group by o.sid, osuser, machine order by num_curs desc; -
查看数据库高速缓冲区的大小
show sga; # 显示数据库块的大小 show parameter db_block_size; # 显示数据库缓存大小 show parameter db_cache_size; -
缓存顾问操作
缓存区顾问用于启动或关闭统计信息，这些信息用于预测不同缓冲区大小导致的行为特征。
# 查看缓存顾问状态 show parameter db_cache_advice; # 修改缓存顾问状态：ON/OFF/READY alter system set db_cache_advice = OFF; # 查看数据库高速缓冲区的信息 select id,name,block_size,size_for_estimate,buffers_for_estimate from v$db_cache_advice; -
查看Redo日志缓存区
# Redo日志缓存区参数是静态参数，不能进行动态修改 show parameter log_buffer;
