ORACLE 表空间管理

傷城~ 2023-08-17 16:39 120阅读 0赞

sqlplus / as sysdba
    
    --创建普通数据表空间(前提是目录已存在)
    create tablespace tbs1 
    datafile 'C:\ORADATA\DATAFILE\tbs1.dbf' size 100M 
    extent management local 
    segment space management auto;
    
    col file_name format a50
    set linesize 366
    
    --查询表空间（名称大写）
    select file_name,tablespace_name,autoextensible,bytes 
    from dba_data_files 
    where tablespace_name = 'TBS1' order by substr(file_name,-12);
    
    
    --创建临时表空间
    create temporary tablespace temp_tbs 
    tempfile 'C:\ORADATA\DATAFILE\temp_tbs.dbf' size 100m;
    
    select file_name,tablespace_name,autoextensible,bytes 
    from dba_temp_files 
    where tablespace_name = 'TEMP_TBS';
    
    
    --创建回滚段表空间
    create undo tablespace undo_tbs 
    datafile 'C:\ORADATA\DATAFILE\undo_tbs.dbf' size 100m;
    
    select file_name,tablespace_name,autoextensible,bytes/1024/1024 
    from dba_data_files 
    where tablespace_name = 'UNDO_TBS' order by substr(file_name,-12);
    
    
    --查看系统表空间
    select file_name,tablespace_name,autoextensible,bytes/1024/1024 
    from dba_data_files 
    where tablespace_name like '%SYS%' order by substr(file_name,-12);
    
    --系统表空间与用户表空间都属于永久保留数据
    select tablespace_name,contents from dba_tablespaces where tablespace_name in ('TBS1','TEMP_TBS','UNDO_TBS','SYSTEM','SYSAUX');
    
    ---为用户指定默认表空间
    sqlplus / as sysdba
    drop user ym301 cascade;
    create user ym301 identified by ym301 default tablespace tbs1 temporary tablespace temp_tbs;
    
    --授权（最大权限，用于测试）
    grant dba to ym301;
    
    --用户ym301登录
    connect ym301/ym301;
    
    --在表空间中建表（Segment）
    create table t(id int) tablespace tbs1;
    create table t2(id int);--不指定表空间，就在默认的表空间中创建
    
    
    --查询数据字典，获取区extent信息（默认8个块，区大小64K）
    select segment_name, extent_id, tablespace_name, bytes/1024/1024,blocks from user_extents where segment_name = 'T';
    
    
    --插入数据
    insert into t select rownum from dual connect by level <= 1000000;
    commit;
    select segment_name, extent_id, tablespace_name, bytes/1024/1024,blocks from user_extents where segment_name = 'T';
    
    --查询数据字典，获取区segment信息
    select segment_name, segment_type, tablespace_name,blocks,extents, bytes/1024/1024 from user_segments where segment_name = 'T';
    
    --观察索引段
    create index idx_id on t(id);
    select segment_name, segment_type, tablespace_name,blocks,extents, bytes/1024/1024 from user_segments where segment_name = 'IDX_ID';
    select count(*) from user_extents where segment_name = 'IDX_ID';
    
    
    --调整块大小（默认8K）
    --不能更改系统表空间、已建好的表空间
    --查看参数
    show parameter cache_size;
    
    --设置块大小
    alter system set db_16k_cache_size=100M;
    show parameter 16k;
    --建表空间，启用块
    create tablespace tbs_16k blocksize 16k datafile 'C:\ORADATA\DATAFILE\tbs_16k.dbf' size 100M autoextend on extent management local segment space management auto;
    
    select tablespace_name, block_size from dba_tablespaces where tablespace_name in ('TBS1','TBS_16K');
    
    ---设置区大小
    create tablespace TBS2 datafile 'C:\ORADATA\DATAFILE\TBS2.dbf' size 100M extent management local uniform size 10M segment space management auto;
    
    create table t2(id int) tablespace TBS2;
    
    select segment_name, extent_id, tablespace_name, bytes/1024/1024,blocks from user_extents where segment_name = 'T2';
    
    insert into t2 select rownum from dual connect by level <= 1000000;
    
    --表空间原始空间大小
    select sum(bytes)/1024/1024 from dba_data_files where tablespace_name = 'TBS2';
    
    
    --表空间使用空间情况
    select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'TBS2';
    
    --ORA-30009: Not enough memory for CONNECT BY operation
    
    --增加数据文件，扩大表空间
    alter tablespace TBS2 ADD DATAFILE 'C:\ORADATA\DATAFILE\TBS2_1.dbf' SIZE 50M;
    
    select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files where tablespace_name = 'TBS2';
    
    --修改表空间为自动扩展方式(autoextend on)
    alter database datafile 'C:\ORADATA\DATAFILE\TBS2_1.dbf' autoextend on;
    
    --删除表空间TBS2
    drop tablespace TBS2 including contents and datafiles;
    
    --建表空间时设置自动扩展
    create tablespace TBS3 datafile 'C:\ORADATA\DATAFILE\TBS3.dbf' size 100M autoextend on extent management local segment space management auto;
    --extent management local segment space management auto,Oracle 10g及以上版本，可省略。
    --每次以64K增加，最大不超过5G
    create tablespace TBS3 datafile 'C:\ORADATA\DATAFILE\TBS3.dbf' size 100M autoextend on next 64k maxsize 5G;
    
    --查看当前回滚段表空间（默认UNDOTBS1）
    show parameter undo;
    --数据库有几个回滚段表空间
    select tablespace_name, status from dba_tablespaces where contents = 'UNDO';
    --分别查看各回滚段表空间大小
    select tablespace_name,sum(bytes)/1024/1024 from dba_data_files where tablespace_name in ('UNDOTBS1','UNDO_TBS') GROUP BY tablespace_name;
    
    --切换回滚表空间（注意：回滚表空间可以新建多个，但是数据库当前使用的只能有一个）
    alter system set undo_tablespace=UNDO_TBS scope=both;
    show parameter undo;
    --正在使用时，无法删除 undo tablespace 'UNDO_TBS' is currently in use 
    drop tablespace UNDO_TBS;
    
    --切换临时表空间（注意：临时表空间可以新建多个，可以同时使用）
    --数据库有几个临时表空间
    select tablespace_name, sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;
    --该用户使用的表空间
    select default_tablespace, temporary_tablespace from dba_users where username = 'YM301';
    --其他用户使用的表空间
    select default_tablespace, temporary_tablespace from dba_users where username = 'SYSTEM';
    
    --切换临时表空间
    sqlplus / as sysdba;
    alter user system temporary tablespace TEMP_TBS;
    select default_tablespace, temporary_tablespace from dba_users where username = 'SYSTEM';
    --不同用户使用临时表空间的情况
    select temporary_tablespace, count(*) from dba_users group by temporary_tablespace;
    
    --切换所有用户到指定临时表空间
    alter database default temporary tablespace TEMP_TBS;
    
    --临时表空间组
    create temporary tablespace temp1_1 tempfile 'C:\ORADATA\DATAFILE\temp1_1.dbf' size 100M tablespace group tmp_grp1;
    create temporary tablespace temp1_2 tempfile 'C:\ORADATA\DATAFILE\temp1_2.dbf' size 100M tablespace group tmp_grp1;
    create temporary tablespace temp1_3 tempfile 'C:\ORADATA\DATAFILE\temp1_3.dbf' size 100M tablespace group tmp_grp1;
    select * from dba_tablespace_groups;
    
    --临时表空间转移到组中
    alter tablespace TEMP_TBS tablespace group tmp_grp1;
    select * from dba_tablespace_groups;
    
    --设置用户指定临时表空间组
    alter user ym301 temporary tablespace tmp_grp1;
    
    --查看用户指定的临时表空间
    select temporary_tablespace from dba_users where username='YM301';
    
    select username,session_num,tablespace from v$sort_usage;
    
    
    --查看产生多少日志
    select a.name,b.value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name='redo size';