【Oracle】表空间管理

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

/*
  表空间是逻辑结构，数据文件是物理结构
  一个表空间对应多个段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;