表空间管理为主、附带 权限管理、数据字典
/*
表空间是逻辑结构，数据文件是物理结构
一个表空间对应多个段segment 段可以对应多个数据文件、跨磁盘
一个段对应多个盘区 extent 一个盘区只能位于一个数据文件上
一个盘区对应多个 数据块 block
*/
--通过数据字典查询 表空间
select * from dba_data_files; --可以看出 一个表空间对应多个数据文件
/*
默认表空间
example 存放各种样例
sysaux system的辅助空间。主要用于存储数据字典之外的其他数据对象，这样可以减少system表空间的负荷
system 存放数据字典，包括表、视图、存储过程的定义等
temp 存放sql语句处理的表和索引的信息。数据排序就用这个表空间
undotbs1 存放撤销数据的表空间
users 通常用于存放 应用系统所使用的数据库对象
SYSTEM 表空间
进行数据库操作所必须要求的表空间
包含数据字典的信息、存储过程和数据库触发子的定义
包括系统回滚段
可以存储用户数据，但最好不要存储用户数据
非SYSTEM 表空间
可以包括回滚段、临时段、应用数据、应用索引和用户空间
提供了一种更加灵活的数据库管理基础
*/
select owner,count(1) from dba_segments group by owner;
--创建表空间语法
create tablespace <tablespace name>
data files 数据文件名以及大小
storage 设置表空间的存储参数 (存储子句)
bring online after creation 表空间在线
leave offline after creation 表空间离线
--查看表空间信息
select * from dba_tablespaces;
--查看数据文件的信息
select file_name,tablespace_name from dba_data_files;
--创建表空间 例句
create tablespace user_space
datafile 'D:\app\Administrator\oradata\bonc\DB_TEST.DBF'
size 30M
default storage
(
initial 10k
next 50k
minextents 1
maxextents 99
pctincrease 10
)
online;
--创建临时表空间 例句
CREATE TEMPORARY TABLESPACE DB_TEMP
TEMPFILE 'D:\app\gloryzheng\oradata\dbz\DB_TEMP.DBF'
SIZE 320M
AUTOEXTEND ON
NEXT 320M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
--删除表空间
drop tablespace user_space including contents cascade constraints;
--使表空间在线
set tablespace users online;
--修改表空间
alter tablespace user_space
add datafile 'D:\app\Administrator\oradata\bonc\DB_TEST2.DBF'
rename datafile 'D:\app\Administrator\oradata\bonc\DB_TEST.DBF' to 'D:\app\Administrator\oradata\bonc\DB_TEST1.DBF'
default storage
online
offline
normal
temporary
immediate;
begin backup
end;
--删除用户并级联删除
drop user tourdb cascade;
--为tourdb 创建表空间
CREATE TABLESPACE DB_ZHENG
LOGGING
DATAFILE 'D:\app\Administrator\oradata\orcl\DB_ZHENG.DBF'
SIZE 320M
AUTOEXTEND ON
NEXT 320M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
--创建用户并指定表空间
CREATE USER tourdb IDENTIFIED BY ch7YH3vd
ACCOUNT UNLOCK
DEFAULT TABLESPACE DB_DATA
TEMPORARY TABLESPACE DB_TEMP;
--修改密码
alert user scott identified by tiger;
--增加表空间的配额
alter user gloryzheng quota unlimited on DB_ZHENG;
alter user gloryzheng quota 10m on db_zheng;
--查看当前用户的权限
select * from user_sys_privs where privilege like '%grant%';
--查看所有用户的权限
select * from user_tab_privs where owner like'%gloryzheng%';
--查看字符集
select * from v$nls_parameters t where t.PARAMETER ='NLS_LANGUAGE' or t.PARAMETER ='NLS_CHARACTERSET';
--数据库服务器字符集
select * from nls_database_parameters;
--客户端字符集
select * from nls_instance_parameters;
--session字符集
select * from nls_session_parameters;
--在环境变量中 新建 系统变量 变量名：NLS_LANG 变量值 为服务器字符集中 NLS_LANGUAGE的值+.+NL_CHARACTERSET的值 即可解决字符集问题
--授权用户 对表操作
--会话权限
grant create session to gloryzheng, tour,tourdb,bkepler;
--授权查询任何表
grant select any table to gloryzheng, tour,tourdb,bkepler;
--授权查询任何字典
grant select any dictionary to gloryzheng, tour,tourdb,bkepler;
--创建表权限
grant create any table to gloryzheng, tour,tourdb,bkepler;
--删除表权限
grant drop any table to gloryzheng, tour,tourdb,bkepler;
--授予插入表的权限
grant insert any table to gloryzheng, tour,tourdb,bkepler;
--授予创建存储过程的权限
grant create procedure to gloryzheng;
--授予dba角色
grant dba to gloryzheng,tour,tourdb,bkepler;
--回收dba角色
revoke dba from gloryzheng, tour,tourdb,bkepler;
--删除角色
drop role xxx
--查看用户相关 数据字典
select table_name from user_tables; 或 select *from tab;--查看用户所拥有的表
select view_name from user_views; --查看用户所拥有的视图
select trigger_name from user_triggers;--查看用户所拥有的触发器
select sequence_name from user_sequence;--查看用户拥有的序列
select index_name from user_indexs;--查看用户拥有的索引
select *from session_privs;--查看用户所拥有的权限
conn scott/tiger;--切换用户
conn scott/tiger as sysdba;--将用户赋予某种角色登录
conn system/unis; select username from dba_users;--查看所有用户
--登录SQL Plus
sqlplus 用户名/密码@db1 as sysdba;
--导出用户文件 用户名/密码为登录现有数据库所用
exp scott/Manager123@localhost:1521/db1 file=d:/scott.dmp
--如果导出表时 用户是dba角色 那么导入表时 用户也应该是dba角色 用户名、密码为目标数据库所有
imp 用户名/密码@SID （目标数据库名） file=dmp文件路径 fromuser=导出用户名 touser=导入用户名
--查询表空间使用情况
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
--查询表空间的free space
select tablespace_name, count(*) AS extends,round(sum(bytes) / 1024 / 1024, 2) AS MB,sum(blocks) AS blocks from dba_free_space group BY tablespace_name;
--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;
--查询表空间使用率
--例句1
SELECT total.tablespace_name,
Round(total.MB, 2) AS Total_MB,
Round(total.MB - free.MB, 2) AS Used_MB,
Round(( 1 - free.MB / total.MB ) * 100, 2)
|| '%' AS Used_Pct
FROM (SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name,
Sum(bytes) / 1024 / 1024 AS MB
FROM dba_data_files
GROUP BY tablespace_name) total
WHERE free.tablespace_name = total.tablespace_name;
--例句2
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
( total - free ) "表空间使用大小",
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;
--例句3
SELECT TABLESPACE_NAME "TABLESPACE",
To_char(Round(BYTES / 1024, 2), '99990.00')
|| '' "TOTAL",
To_char(Round(FREE / 1024, 2), '99990.00')
|| 'G' "FREE",
To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
|| 'G' "USED",
To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
|| '%' "PERCENT"
FROM (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,
Floor(A.BYTES / ( 1024 * 1024 )) BYTES,
Floor(B.FREE / ( 1024 * 1024 )) FREE,
Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
FROM (SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME TABLESPACE_NAME,
Sum(BYTES) FREE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME);
--WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称
ORDER BY Floor(10000 * USED / BYTES) DESC;
--例句4
select tablespace_name,
max_gb,
used_gb,
round(100 * used_gb / max_gb) pct_used
from (select a.tablespace_name tablespace_name,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / power(2, 30),
2) used_gb,
round(a.maxbytes / power(2, 30), 2) max_gb
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name tablespace_name,
round(sum(nvl(p.bytes_used, 0)) / power(2, 30), 2) used_gb,
round(sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) / power(2, 30),
2) max_gb
from v$temp_space_header h, v$temp_extent_pool p, dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name)
order by 4;
