表空间管理为主、附带 权限管理、数据字典

/*
  表空间是逻辑结构，数据文件是物理结构
  一个表空间对应多个段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,bkepler;
	--授权查询任何字典
	grant select any dictionary to gloryzheng,bkepler;
	--创建表权限
	grant create any table to gloryzheng,bkepler;
	--删除表权限
	grant drop any table to gloryzheng,bkepler;
	--授予插入表的权限
	grant insert any table to gloryzheng,bkepler;
	--授予创建存储过程的权限
	grant create procedure to gloryzheng;
	--授予dba角色
	grant dba to gloryzheng,bkepler;
	--回收dba角色
	revoke dba from gloryzheng,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,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,'99990.00')
		   || ''           "TOTAL",To_char(Round(FREE / 1024,'99990.00')
		   || 'G'          "FREE",To_char(Round(( BYTES - FREE ) / 1024,'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,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,round(sum(decode(f.autoextensible,f.bytes)) / power(2,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;