`

(转)oracle 临时表空间的增删改查

 
阅读更多
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 <db_name> 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.
分享到:
评论

相关推荐

    oracle 增删改查

    根据提供的文件信息,本文将详细解释Oracle数据库中的增删改查操作,并重点解析与表空间相关的创建、修改及删除等管理操作。 ### Oracle 表空间的创建 在Oracle数据库中,表空间是逻辑存储单元,它由一个或多个...

    Oracle创建表空间、创建用户以及授权、查看权限.doc

    这里授予了用户多种系统权限,包括创建会话、创建任何表、视图、索引、过程等,以及对任何表进行增删改查操作。 ##### 3.2 授予角色权限 角色是一组预定义的权限集合,可以方便地授予或撤销给用户。例如: ```sql...

    c#窗体应用程序实现对数据库的增删查改

    在C#中,我们可以使用`MySql.Data.MySqlClient`命名空间下的类来与MySQL数据库进行交互。首先需要创建一个`MySqlConnection`对象,指定服务器地址、数据库名、用户名和密码等连接参数。 3. **ADO.NET组件** - **...

    Oracle大型数据库的基础学习知识—2

    Oracle支持SQL的增删改查操作,以及更复杂的子查询、连接、聚合函数、视图、存储过程等特性。 5. **表空间管理**:表空间是Oracle数据库中存储数据的主要方式,用于逻辑划分数据库空间。理解如何创建、扩展和收缩表...

    oracle数据库教程

    DML主要包含select, insert, update, delete等操作,用于对数据库中数据的增删改查。DDL则用于定义或修改数据库结构,如创建(create)、修改(alter)、删除(drop)表或索引等。DCL用于管理对数据的访问权限,如...

    oracle基本语句总结.text

    1)增删改查:2) 添加列:3)创建表空间4)创建临时表空间5)创建用户6)赋权7)建表/删表8)加约束/主键9)检查10)加默认的不行11)外键 12)复制表13)复制表结构 14)在已有的表结构中插入数据.....

    网上书店系统oracle数据库创建

    这需要通过SQL语句进行用户信息的增删改查操作,同时结合后端代码处理用户认证和权限控制。 2. **商品展示**:展示书籍信息,支持按分类、作者等条件筛选。这涉及查询书籍表,并可能结合其他信息(如作者表、分类表...

    oracle-SQL笔记

    总结来说,Oracle SQL的学习涵盖了数据控制(权限管理)、数据定义(对象创建与修改)和数据操纵(数据增删改查)等核心概念。对于初学者,理解和熟练掌握这些基本语句是成为合格的数据库管理员或开发者的第一步。...

    ORACLE数据库设计调整方案.pdf

    1. 规范化:规范化理论是数据库设计的基础,通过消除冗余数据,降低存储需求,减少I/O操作,提高增删改查的速度。规范化设计应遵循的原则包括:相同数据段存储在一起,标准化设计,例外处理区域,最小化表空间冲突,...

    oracle数据库知识.pptx

    SQL(Structured Query Language)是用于操作和查询数据库的标准语言,支持数据的增、删、改、查操作,并且可以嵌入到其他编程语言中。Oracle数据库则是基于SQL的,它采用了客户/服务器(CLIENT/SERVER)架构,允许...

    oracle命令

    表空间管理是Oracle数据库管理的核心部分之一,涉及到数据文件的增删改查以及空间的分配。以下是一些相关的管理命令: - **查询包含特定字符串的表名**:`SELECT * FROM tabs WHERE TABLE_NAME LIKE '%WEB%';` - ...

    Oracle 笔记.docx

    - 实现数据表的增删改查(CRUD)操作。 - 学习事务控制、索引、视图等高级特性。 - 掌握存储过程与触发器的使用。 - 实现权限管理和备份恢复功能。 - 进行数据库设计与优化。 #### 三、Oracle核心概念与操作 - ...

    数据库系统应用开发实用教程电子课件第章管理表PPT课件.ppt

    《数据库系统应用开发...学习这些目标旨在使开发者能够熟练地进行数据库操作,包括用户管理、权限分配、表的创建和维护,以及数据的增删改查和事务处理。通过实践和理解这些知识点,可以有效提高数据库应用开发能力。

    ORACLE常用精华语句

    - **普通用户权限**:非SYSDBA或SYSOPER的用户权限,一般用于业务操作,如数据的增删改查。 3. **用户登录** - 登录示例: ``` conn sys/tiger as sysdba; conn system/tiger as sysoper; conn / as sysdba; ...

    Oracle简单常用sql

    Oracle SQL是用于管理和操作Oracle数据库的强大工具,涵盖了数据的增、删、改、查以及数据库对象的管理。以下是一些常用的Oracle SQL语句及其详细解释: 1. **查看用户下的所有表**: - `SELECT table_name FROM ...

    oracle面试1000例

    - SQL语言:掌握SQL的增删改查操作,了解子查询、连接查询、集合操作。 - PL/SQL:Oracle的编程语言,用于编写存储过程、函数、触发器。 2. **Oracle架构**: - 实例与数据库的区别:实例是内存结构,数据库是...

    oracle基础

    Oracle支持SQL的增、删、改、查等基本操作。 - **SELECT语句**:用于查询数据,例如`SELECT * FROM table_name;` - **INSERT语句**:插入新记录,例如`INSERT INTO table_name (column1, column2) VALUES (value1,...

    oracle错误一览表

    ### Oracle错误一览表详解 #### 1. ORA-00001: 违反唯一约束条件 - **描述**: 当尝试插入一个已经存在于唯一索引中的值时触发此错误。 - **解决方法**: 检查并确认数据是否符合唯一性要求,或者调整表结构来移除或...

    oracle错误码大全

    - 优化临时表的使用,减少不必要的锁操作。 #### ORA-00058: DB_BLOCK_SIZE必须为才可安装此数据库(非) **描述**: 数据库块大小不符合安装要求。 **解决方法**: - 设置正确的`DB_BLOCK_SIZE`值。 - 确认硬件环境...

    orcle 的应用安布暑数据库入门与提升

    通过掌握这些基本概念,初学者可以开始Oracle数据库的安装、建表和权限设置,进一步学习SQL语言,包括数据的增删改查操作,以及更复杂的查询技巧。这些知识将为后续的数据库管理和应用程序开发打下坚实的基础。

Global site tag (gtag.js) - Google Analytics