精华内容
下载资源
问答
  • Oracle临时表空间通过Oracle表空间的讲解,我们了解到表空间是Oracle数据库存储数据和对象的逻辑容器,那临时表空间呢?Oracle临时表空间主要是存储数据库的排序操作、临时表、中间排序结果等临时对象。例如,我们...

    Oracle临时表空间

    通过Oracle表空间的讲解,我们了解到表空间是Oracle数据库存储数据和对象的逻辑容器,那临时表空间呢?

    Oracle临时表空间主要是存储数据库的排序操作、临时表、中间排序结果等临时对象。例如,我们进行大数量级的排序操作时,当数据库内存不够时,就会写入临时表空间,当操作完成后,临时表空间就会自动清空释放。Oracle经常使用到临时表空间的操作有:create index(创建索引)、group by(分组查询)、order by(排序时)、集合运算时(union、minus、intersect)、多表连接查询时,当数据库内存不足时,会用到临时表空间。

    创建临时表空间

    Oracle数据库在安装完后就会创建一个默认的临时表空间temp。Oracle创建临时表空间的语法结构和创建持久化表空间一样,只是多了关键字temporary进行创建临时表空间。

    创建临时表空间语法:create temporary  tablespace tempname

    tempfile 'filename'

    size m;

    语法解析:

    1、create temporary tablespace:表示创建临时表空间,tempname表示创建临时表空间的名字。

    2、filename:指定临时表空间数据文件的位置。

    3、size m:表示临时表空间的大小。

    案例1、创建临时表空间temp1,代码如下:create temporary  tablespace temp1

    tempfile 'E:\APP\ADMIN\ORADATA\ORCL\temp1.DBF'

    size 50m;

    创建好临时表空间temp1,我们可以通过数据字典dba_temp_files进行查询临时表空间的信息,查询代码如下:select t.TABLESPACE_NAME, --表空间名

    t.FILE_NAME, --文件名

    t.AUTOEXTENSIBLE, --是否自动扩展

    t.BYTES / 1024 / 1024 as tsize, --表空间初始大小

    t.MAXBYTES / 1024 / 1024 msize, --表空间最大扩展到多少

    b.CONTENTS, --表空间类型

    b.EXTENT_MANAGEMENT --表空间管理模式

    from dba_temp_files t, dba_tablespaces b

    where t.TABLESPACE_NAME = b.TABLESPACE_NAME

    1547046031743_656972.png

    展开全文
  • oracle 临时表空间的增删改查1、查看临时表空间(dba_temp_files视图)(v_$tempfile视图)select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;select status,enabled, ...

    oracle 临时表空间的增删改查

    1、查看临时表空间(dba_temp_files视图)(v_$tempfile视图)

    select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

    select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys用户查看

    2、缩小临时表空间大小

    alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TELEMT\TEMP01.DBF' resize 100M;

    3、扩展临时表空间:

    方法一、增大临时文件大小:

    SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m;

    方法二、将临时数据文件设为自动扩展:

    SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;

    方法三、向临时表空间中添加数据文件:

    SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m;

    4、创建临时表空间:

    SQL> create temporary tablespace temp1 tempfile ‘/u01/app/oracle/oradata/orcl/temp11.dbf’ size 10M;

    5、更改系统的默认临时表空间:

    --查询默认临时表空间

    select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

    --修改默认临时表空间

    alter database default temporary tablespace temp1;

    所有用户的默认临时表空间都将切换为新的临时表空间:

    select username,temporary_tablespace,default_ from dba_users;

    --更改某一用户的临时表空间:

    alter user scott temporary tablespace temp;

    6、删除临时表空间

    删除临时表空间的一个数据文件:

    SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ drop;

    删除临时表空间(彻底删除):

    SQL> drop tablespace temp1 including contents and datafiles cascade constraints;

    7、查看临时表空间的使用情况(GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询)

    GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小

    dba_temp_files视图的bytes字段记录的是临时表空间的总大小

    SELECT temp_used.tablespace_name,

    total - used as "Free",

    total as "Total",

    round(nvl(total - used, 0) * 100 / total, 3) "Free percent"

    FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used

    FROM GV_$TEMP_SPACE_HEADER

    GROUP BY tablespace_name) temp_used,

    (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total

    FROM dba_temp_files

    GROUP BY tablespace_name) temp_total

    WHERE temp_used.tablespace_name = temp_total.tablespace_name

    8、查找消耗资源比较的sql语句

    Select se.username,

    se.sid,

    su.extents,

    su.blocks * to_number(rtrim(p.value)) as Space,

    tablespace,

    segtype,

    sql_text

    from v$sort_usage su, v$parameter p, v$session se, v$sql s

    where p.name = 'db_block_size'

    and su.session_addr = se.saddr

    and s.hash_value = su.sqlhash

    and s.address = su.sqladdr

    order by se.username, se.sid

    9、查看当前临时表空间使用大小与正在占用临时表空间的sql语句

    select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text

    from v$sort_usage sort, v$session sess, v$sql sql

    where sort.SESSION_ADDR = sess.SADDR

    and sql.ADDRESS = sess.SQL_ADDRESS

    order by blocks desc;

    10、临时表空间组介绍

    1)创建临时表空间组:

    create temporary tablespace tempts1 tempfile '/home/oracle/temp1_02.dbf' size 2M tablespace group group1;

    create temporary tablespace tempts2 tempfile '/home/oracle/temp2_02.dbf' size 2M tablespace group group2;

    2)查询临时表空间组:dba_tablespace_groups视图

    select * from dba_tablespace_groups;

    GROUP_NAME                     TABLESPACE_NAME

    ------------------------------ ------------------------------

    GROUP1                         TEMPTS1

    GROUP2                         TEMPTS2

    3)将表空间从一个临时表空间组移动到另外一个临时表空间组:

    alter tablespace tempts1 tablespace group GROUP2 ;

    select * from dba_tablespace_groups;

    GROUP_NAME                     TABLESPACE_NAME

    ------------------------------ ------------------------------

    GROUP2                         TEMPTS1

    GROUP2                         TEMPTS2

    4)把临时表空间组指定给用户

    alter user scott temporary tablespace GROUP2;

    5)在数据库级设置临时表空间

    alter database default temporary tablespace GROUP2;

    6)删除临时表空间组 (删除组成临时表空间组的所有临时表空间)

    drop tablespace tempts1 including contents and datafiles;

    select * from dba_tablespace_groups;

    GROUP_NAME                     TABLESPACE_NAME

    ------------------------------ ------------------------------

    GROUP2                         TEMPTS2

    drop tablespace tempts2 including contents and datafiles;

    select * from dba_tablespace_groups;

    GROUP_NAME                     TABLESPACE_NAME

    11、对临时表空间进行shrink(11g新增的功能)

    --将temp表空间收缩为20M

    alter tablespace temp shrink space keep 20M;

    --自动将表空间的临时文件缩小到最小可能的大小

    ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/lmtemp02.dbf’;

    临时表空间作用

    Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。

    重启数据库可以释放临时表空间,如果不能重启实例,而一直保持问题sql语句的执行,temp表空间会一直增长。直到耗尽硬盘空间。

    网上有人猜测在磁盘空间的分配上,oracle使用的是贪心算法,如果上次磁盘空间消耗达到1GB,那么临时表空间就是1GB。

    也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

    临时表空间的主要作用:

    索引create或rebuild;

    Order by 或 group by;

    Distinct 操作;

    Union 或 intersect 或 minus;

    Sort-merge joins;

    analyze.

    posted on 2012-06-28 14:54 japper 阅读(30586) 评论(2)  编辑  收藏 所属分类: Oracle

    展开全文
  • 一、默认表空间Oracle 安装时会自动创建几个默认表空间,可以在dba_tablespaces这张表查看到默认表空间 :说明:SYSTEM:管理任何其他表空间。...TEMP:临时表空间。可用于排序操作等。USERS:存储用户...

    一、默认表空间

    Oracle 安装时会自动创建几个默认表空间,可以在 dba_tablespaces 这张表查看到默认表空间 :

    0bad8be38b37587f6336a42c3e7911c2.png

    说明:

    SYSTEM:管理任何其他表空间。它包含数据字典,有关数据库管理信息的表和视图,编译的存储对象(如触发器,过程等)。

    SYSAUX:辅助表空间。

    UNDOTBS1:撤销表空间。存储的是撤销信息,可以用于恢复操作。

    TEMP:临时表空间。可用于排序操作等。

    USERS:存储用户的表和索引数据。

    使用 dba_users 这张表可以查询用户的默认表空间。

    36b0bc3fcb7555ac8b2cac8d11818549.png

    从结果中可以发现,一个用户只能分配一个表空间,一个表空间可以被多个用户使用

    如果想查询 SYS 和 SYSTEM 这两个用户的默认表空间可以使用 where 限定查询条件

    7ea69f32977b3b93c49374c4f3ede283.png

    可以看到 SYS 和 SYSTEM 这两个用户的默认表空间都是 SYSTEM 表空间。

    二、表空间的管理

    表空间的类型有如下三种:

    永久表空间:永久表空间包含持久性模式对象。永久表空间中的对象存储在数据文件中。

    撤销表空间:撤消表空间是在自动撤消管理模式下运行数据库时,管理撤消数据的一种永久性表空间。

    临时表空间:临时表空间仅包含会话期间的模式对象。临时表空间中的对象存储在临时文件中。

    三、创建表空间

    创建一个最简单的表空间:

    SQL> create tablespace tp1

    datafile 'tp1.dbf'

    size 1M;

    第一行的 tp1 是表空间的名字。

    第二行是表空间的数据文件是 tp1.dbf 。

    第三行是表空间大小为 1M 。

    默认情况下,创建的表空间不会自动扩展,如果我们想要在数据文件充满时进行自动扩展,可以创建一个自动扩展的表空间,当数据文件装满后,它会自动增加数据文件的尺寸。

    SQL> create smallfile tablespace tp2

    datafile 'tp2.dbf'

    size 10M autoextend on next 1M maxsize 20M

    extent management local autoallocate

    segment space management auto;

    代码详细解释:

    6eca0cf5f8a201a01bd52f3ff8eb8b80.png

    三、更改表空间

    1、重命名表空间

    例如我们想要把 tp2 这个表空间重命名为 syl_tp ,可以使用如下语句:

    SQL> alter tablespace tp2 rename to syl_tp;

    注意:重命名表空间不会重命名与之关联的任何数据文件。

    设置表空间的读写状态

    表空间在创建时默认是读写状态,我们可以将其设置为只读状态。

    SQL> alter tablespace tp1 read only;

    如果要改回读写状态,可以使用下面的语句:

    SQL> alter tablespace tp1 read write;

    设置表空间的可用状态

    可用状态指的是表空间的两种状态:

    联机状态 ONLINE:表空间可用,可以被使用。

    脱机状态 OFFLINE:表空间和其数据文件不可用。脱机状态还包括三种方式,即NORMAL (正常状态):将表空间中的所有数据文件中的所有块刷新到系统全局区域(SGA)之外。这是默认的方式。

    TEMPORARY (临时状态):Oracle 数据库会为表空间中的所有联机数据文件执行检查点,但不能确保可以写入所有文件。

    IMMEDIATE (立即状态):立即使表空间和数据文件脱机,不会确保表空间文件可用,并且不执行检查点。可能丢失未提交的更改。

    我们下面将表空间 tp1 以 normal 方式脱机:

    SQL> alter tablespace tp1 offline normal;

    注意:不能把一个临时表空间脱机。

    再将 tp1 设置成联机状态:

    SQL> alter tablespace tp1 online;

    调整表空间的大小

    在创建表空间时,如果使用了 autoextend ,则可以自动调整数据文件的大小。如果没有使用,就需要我们手动去调整。有两种调整方式可供选择:

    调整现有数据文件的大小

    下面我们将表空间 tp1 的尺寸更改为 2M :

    SQL> alter database datafile 'tp1.dbf' resize 2m;

    向表空间添加数据文件

    SQL> alter tablespace tp1 add datafile 'tp1_02.dbf' size 1m;

    使用 v$tablespace 和 v$datafile 这两个视图可以查看到表空间的数据文件和大小:

    SQL> select t.name tname,d.name dname,d.bytes from v$tablespace t join v$datafile d using(ts#) where t.name like 'TP1';

    ts#:tablespace number。

    b9912e50455b6dcbf21dea7304f9fb9e.png

    四、删除表空间

    例如我们要删除 tp1 这个表空间及其数据文件:

    SQL> drop tablespace tp1 including contents and datafiles;

    如果表空间包含的表与另一个表空间的表存在外键关系,就会删除失败,这个时候我们可以使用 cascade constraints 将表空间中的完整性也删除:

    SQL> drop tablespace syl_tp including contents cascade constraints;

    使用下面的命令查看表空间会发现表空间已经被删除:

    SQL> select tablespace_name from dba_data_files;

    想了解更多删除表空间内容可参考 删除表空间

    展开全文
  • --查看表空间使用率的sql语句: select * from (Select a.tablespace_name, to_char(a.bytes / 1024 / 1024, '99,999,999.99') "total_bytes(M)", to_char(b.bytes / 1024 / 1024, '99,999,999.99') "free_bytes...
    --查看表空间使用率的sql语句:
    select *
      from (Select a.tablespace_name,
                   to_char(a.bytes / 1024 / 1024, '99,999,999.99') "total_bytes(M)",
                   to_char(b.bytes / 1024 / 1024, '99,999,999.99') "free_bytes(M)",
                   to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
                           '99,999,999.99') "use_bytes(M)",
                   to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' use
              from (select tablespace_name, sum(bytes) bytes
                      from dba_data_files
                     group by tablespace_name) a,
                   (select tablespace_name, sum(bytes) bytes
                      from dba_free_space
                     group by tablespace_name) b
             where a.tablespace_name = b.tablespace_name
            union all
            select c.tablespace_name,
                   to_char(c.bytes / 1024 / 1024, '99,999,999.99') total_bytes,
                   to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999,999.99') free_bytes,
                   to_char(d.bytes_used / 1024 / 1024, '99,999,999.99') use_bytes,
                   to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
              from (select tablespace_name, sum(bytes) bytes
                      from dba_temp_files
                     group by tablespace_name) c,
                   (select tablespace_name, sum(bytes_cached) bytes_used
                      from v$temp_extent_pool
                     group by tablespace_name) d
             where c.tablespace_name = d.tablespace_name)
     order by tablespace_name;
    
    
    
    --查看表空间数据文件
    select d.file_name,
           d.tablespace_name,
           d.autoextensible,
           to_char(d.BYTES / 1024 / 1024, '99,999,999.99') bytes
      from dba_data_files d
     where d.TABLESPACE_NAME = 'MOBILE_PAFETBS';
    --调整数据文件大小
     alter database DATAFILE '/oradata/orcl/MOBILE_PAFETBS02.dbf' resize 1024m;
    
    --查看普通数据文件是否扩展
    select d.file_name,d.tablespace_name,d.autoextensible,d.BYTES from dba_data_files d   
    -- 执行添加临时表空间的数据文件命令:(解决ORA-01652 无法通过128 (在表空间 TEMP中)扩展temp段)
    ALTER TABLESPACE TBS_QS_AGENT/*表空间名*/ ADD DATAFILE '/oradata/orcl/TBS_QS_AGENT04.dbf'/*新的数据文件*/ SIZE 2G;   
    
    
    --查看临时表空间
    select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
    --查看临时表数据文件(大小、是否自动扩展)
    select tablespace_name,file_name,bytes/1024/1024 "file_size(M)",autoextensible from dba_temp_files;
    
    --将临时数据文件设为自动扩展:
    alter database tempfile '/oradata/orcl/temp01.dbf' autoextend on next 5m maxsize unlimited;
    --取消自动扩展 alter database datafile 文件路径 autoextend off
    alter database tempfile '/oradata/orcl/temp01.dbf' autoextend off;
    
    --增大临时文件大小
    alter database tempfile '/oradata/orcl/temp01.dbf' resize 6144m;
    
    展开全文
  • 三种表空间类型:永久表空间、UNDO表空间、临时表空间系统表空间:system与sysaux,创建数据库时自动创建,包含数据字典非系统表空间:存放数据表索引,将数据分散存放不同的表空间,便于空间管理与提升性能创建永久...
  • 详情:解决Oracle临时表空间过大有两种方法,方法一增加临时表空间的大小,方法二重建临时表空间,解决临时表空间过大的问题。方案一:增加临时表空间的大小--1.临时表空间的使用情况SELECT D.tablespace_name,SPACE...
  • Oracle 临时表空间

    2014-07-07 16:48:23
    Oracle临时表空间今天做expdp数据库实验时,突遇临时表空间不足,报ORA-01652错误,这下加深了对临时表空间的认识。希望本文能够对碰到过类似问题的朋友能够有所帮助。如果临时表空间没有设置为自动扩展,则临时...
  • 临时表空间作用Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间,如果不能重启实例,而一直保持问题sql语句的执行,...
  • 每个表空间由一个或多个系统文件组成,叫做数据文件datafiled.一个表空间可以有1个或多个段组成e.当数据库运行时表空间可以是online状态f.除了SYSTEM表空间或者带有活动回滚段的表空间,其他表空间可以设置为offline...
  • 这时如果排序的数据量很大,那么,此时内存的排序区(PGA中)就可能装不下,因此,Oracle服务器就要把一些中间的排序结果写到磁盘上,即临时表空间中。当用户的sql语句中经常有大规模的排序而内存的排序区不够时,使用...
  • 管理数据文件,要求如下: 1)调整临时表空间数据文件, 2)—-文件数(临时)设置为1, 3)—-文件状态(临时)为“正常”,...1.调整临时表空间数据文件位置1)查询临时表空间数据文件位置SYS(D647001)>select FI
  • ORACLE临时表空间

    2016-12-05 14:07:00
    ORACLE临时表空间总结 2014-10-05 11:35 by 潇湘隐者, 临时表空间概念 临 时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候,并且当PGA中 sort_...
  • /*创建临时表空间 **/create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local;/*创建数据表...
  • oracle临时表及临时表空间

    千次阅读 2016-01-06 19:55:23
    查看临时表空间中段的情况,可以查看v$temp_extent_map 查看临时表空间文件:v$tempfile; 查看sql使用临时块的情况:v$tempseg_usage 查看临时块的状态v$tempstat
  • Oracle临时表空间说明

    千次阅读 2016-09-28 05:42:36
    一、临时表空间作用... 2 二、临时表空间释放... 2 法一、重启库... 2 法二、Metalink给出的一个方法... 3 法三、我常用的一个方法... 3 法四、使用诊断事件的一种方法... 3 法五、 重建TEMP 表空间... 3 三...
  • 创建普通表空间指定初始大小,自动扩展,最大大小:(oracle允许的单个smallfile数据文件最大大小为 4194302*blocksize,单个bigfile数据文件最大可达到(32-128)TB)(默认创建表空间为标准的8k数据块,smallfile表空间)...
  • Oracle临时表空间总结

    2018-04-25 10:11:57
    Oracle临时表空间总结 临时表空间概念 临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候,并且当PGA中sort_area_size大小不够时,将会把数据放入临时表...
  • oracle 临时表空间作用相关使用

    千次阅读 2019-05-05 14:30:57
    临时表空间作用: 1、临时数据产生后Oracle数据库会先将这些存放到内存的PGA)内的sor_area排序区(SORT_AREA_SIZE参数)的地方,专门用来存放这些因为排序操作而...2、Oracle临时表空间主要用来做查询和存放一些...
  • oracle临时表空间

    2017-08-01 16:20:34
    临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当Oracle里需要用到SORT的时候,并且当PGA中sort_area_size大小不够时,将会把数据放入临时表空间里进行排序。像数据库中一些操作: ...
  • ora-01652无法通过128(在表空间temp中)扩展temp段今天提交请求后,提示ORA-01652: 无法通过128 (在表空间TEMP 中) 扩展temp 段。...扩展临时表空间来解决今天提交请求后,提示ORA-01652: 无法通过128...
  • oracle临时表空间总结

    2015-01-14 11:09:36
    ORACLE临时表空间总结 2014-10-05 11:35 by 潇湘隐者, 1427 阅读, 0 评论, 收藏, 编辑 临时表空间概念   临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里...
  • ORACLE临时表空间总结

    2019-09-14 17:00:56
    临时表空间用来管理数据库排序操作以及用于存储临时表、中间排序结果等临时对象,当ORACLE里需要用到SORT的时候,并且当PGA中sort_area_size大小不够时,将会把数据放入临时表空间里进行排序。像数据库中一些操作: ...
  • oracle 临时表空间

    2013-03-21 20:31:33
    oracle 临时表空间的增删改查 1、查看临时表空间 (dba_temp_files视图)(v_$tempfile视图) select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files; select ...
  • oracle 临时表空间处理

    2020-06-08 08:51:36
    临时表空间主要用途是在数据库进行排序运算[如创建索引、orderbygroupby、distinct、union/intersect/minus/、sort-mergejoin、analyze命令]、 管理索引[如创建索引、IMP进行数据导入]、访问视图等操作时提供...
  • 近日在维护oracle数据库时发现临时表空间数据文件temp.dbf从最初的几个G猛增到32G,达到上了磁盘文件的上限。搜索了一下,现将查询到的临时表空间的相关资料以及处理方法简要归纳如下:1、临时表空间的作用:临时...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 22,054
精华内容 8,821
关键字:

oracle临时表空间数据文件及扩展