ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Linux操作系统下Oracle11g R2 RAC 安装配置教程
Oracle数据库培训-备份恢复-性能优化-集群容灾
Oracle数据库DBA高级工程师培训视频

Oracle 查看表空间的大小及使用情况sql语句

内容发布:zhwpyy| 发布时间:2017-4-17 16:25:47

--1、查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
--2、查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
--3、查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;
--4、查看控制文件
SELECT NAME FROM v$controlfile;
--5、查看日志文件
SELECT MEMBER FROM v$logfile;
--6、查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
--7、查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
--8、查看数据库的版本 
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
--9、查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;


--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
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

Oracle RAC 调整表空间数据文件大小
先查找出表空间对应的数据文件路径:
select file#,name from v$DAtafile

其中,file#是文件序号,name是文件路径。
然后用下面的语句调整数据文件大小(注意把单引号里的内容替换为上面查出的文件路径):
alter database datafile '+DATA1/ora11g/datafile/system.262.760023469' resize 4G;

=========================================

另注意,如果要以加数据文件的形式扩表空间,则一定要在磁盘组名字前加上 + 号,否则
Oracle会把数据文件建在主机本地硬盘上。



RAC实例 表空间 维护
先配置一下监听,这样我们就可以从客户端进行连接了。 我这里写了三种连接。 第一种是正常方式,一般都采用这种方式,后面的rac1和rac2 是方便测试。 因为如果用第一种方式的话,客户端连哪个实例是随机的,不好进行控制,除非手动的关闭某个实例,让Oracle 漂过去,那样有点麻烦。 我就又多添加了2个监听,分别对应实例1和实例2.  配置这2个监听的时候,要注意Service_name 这个参数,也是orcl. 即全局名。 不是对应的实例名。

Oracle 实例监听:
RAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.10.1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.10.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
        (TYPE = session)
        (METHOD = basic)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

Rac1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.10.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Rac2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.85.10.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

ASM 实例监听配置,手动的修改tnsnames.ora 文件。

如何配置从远程客户端 来访问ASM 实例,参考Blog:
远程客户端 访问 ASM 实例 http://www.linuxidc.com/Linux/2011-09/42728.htm


在这里做一下说明, RAC 的数据文件和控制文件都是放在共享设备上的,所以添加数据文件或对数据文件做修改时,只需要在一个节点上进行就可以了。


一.   用别名管理数据文件

1.1   查看已经存在的数据文件:

C:/Users/Administrator.DavidDai>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 9月
Copyright (c) 1982, 2010, Oracle.  All rights reserved
SQL> conn sys/oracle@rac2 as sysdba;
已连接。
SQL> select file_name from dba_data_files;
FILE_NAME
------------------------------------------------------
+DATA/orcl/datafile/users.279.730181053
+DATA/orcl/datafile/sysaux.277.730181053
+DATA/orcl/datafile/undotbs1.278.730181053
+DATA/orcl/datafile/system.276.730181051
+DATA/orcl/datafile/undotbs2.284.730181347


ASM文件名字的格式是固定的:+group/dbname/file type/tag.file.incarnation

在创建db时系统自动创建的几个表空间(system,undotbs,sysaux,users)对应的都是真实的数据文件,即ASM 文件默认的命名格式。而且这个信息都写到了控制文件里。 如果我们使用别名的话,会方便很多。 对于这些创建数据库时自动创建的表空间,我们要他们使用别名,除了手工创建对应别名外,还需要重建控制文件,并且在重建时,datafile 里写别名的信息。 这样数据库也就使用别名了。


1.2  创建一个别名

注意: 别名的相关操作都要连接到ASM实例进行。 这也是我们前面配置ASM 监听的原因。

C:/Users/Administrator.DavidDai>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期四 9月 23 12:07:24 2010
Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn sys/oracle@ASM AS SYSDBA
已连接。
SQL> select name from v$asm_diskgroup;
NAME
-------------------------------------------
DATA
FLASH_RECOVERY_AREA

1.2.1 使用全路径来创建

SQL> ALTER DISKGROUP DATA ADD ALIAS '+DATA/orcl/datafile/undotbs1.dbf'
FOR '+DATA/orcl/datafile/undotbs1.278.730181053';

1.2.2 使用数据文件的编号来创建

这里的编号就最后几个数字,在下面这个文件里,编号就是:284.730181347
+DATA/orcl/datafile/undotbs2.284.730181347

看一下创建SQL:
SQL> ALTER DISKGROUP DATA ADD ALIAS '+DATA/orcl/datafile/undotbs2.dbf'
  2    FOR '+DATA.284.730181347';  -- 注意这里的格式
磁盘组已变更。


来查看别名的信息,这里要用asmcmd命令:

[oracle@rac1 admin]$ export ORACLE_SID=+ASM1
[oracle@rac1 admin]$ asmcmd
ASMCMD> pwd
+DATA/ORCL/DATAFILE
ASMCMD> ls -a
+DATA/ORCL/DATAFILE/dave.dbf => DAVE.288.730415847
none => SYSAUX.257.729962649
none => SYSAUX.261.729964001
none => SYSAUX.266.729966263
none => SYSAUX.270.729970051
none => SYSAUX.277.730181053
none => SYSTEM.256.729962647
none => SYSTEM.260.729963993
none => SYSTEM.265.729966263
none => SYSTEM.269.729970045
none => SYSTEM.276.730181051
none => UNDOTBS1.258.729962649
none => UNDOTBS1.262.729964005
none => UNDOTBS1.267.729966265
none => UNDOTBS1.271.729970057
+DATA/ORCL/DATAFILE/undotbs1.dbf => UNDOTBS1.278.730181053
+DATA/ORCL/DATAFILE/undotbs2.dbf => UNDOTBS2.284.730181347
none => USERS.259.729962651
none => USERS.263.729964005
none => USERS.268.729966267
none => USERS.272.729970063
none => USERS.279.730181053
dave.dbf
undotbs1.dbf
undotbs2.dbf

在这里,我们看到别名和数据文件的对应关系,我们创建的所有数据文件都有对应的ASM 文件。我们可以使用别名来与之对应,但是创建DB时创建的表空间没有对应的别名与之对应。 这个就是我之前说的,要修改这个默认设置就需要重建控制文件。

1.3 重命名别名
SQL> ALTER DISKGROUP DATA RENAME ALIAS '+DATA/orcl/datafile/undotbs1.dbf' TO '+DATA/orcl/datafile/undotbs3.dbf';

磁盘组已变更。

1.4 删除别名
SQL>  ALTER DISKGROUP DATA DROP ALIAS  '+DATA/orcl/datafile/undotbs3.dbf';
磁盘组已变更。

-- 这里要说明一下,很多资料说是用delete 关键字。 其实这里用的是drop 关键字

1.5 用别名来删除文件
ALTER DISKGROUP DATA DROP FILE '+DATA/ORCL/DATAFILE/undotbs3.dbf';


1.6 根据数据文件的文件号来删除文件
ALTER DISKGROUP DATA DROP FILE ' DATA.342.3';


1.7 用完整文件路劲删除文件
ALTER DISKGROUP DATA DROP FILE ' DATA /orcl/datafile/dave.342.3';

这部分内容可以参考Oracle 联机文档:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16102/asmfiles.htm#CHDDHIGG



二. 数据文件管理

2.1. 创建表空间

和单实例的相同,只不过文件路劲写成ASM的路劲:

SQL> CREATE TABLESPACE DaveHomeTown DATAFILE  '+DATA/orcl/datafile/AnhuiAnqing.dbf' SIZE 10M;
表空间已创建。

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/users.279.730181053
+DATA/orcl/datafile/sysaux.277.730181053
+DATA/orcl/datafile/undotbs1.278.730181053
+DATA/orcl/datafile/system.276.730181051
+DATA/orcl/datafile/undotbs2.284.730181347
+DATA/orcl/datafile/dave.dbf
+DATA/orcl/datafile/anhuianqing.dbf

已选择7行。

我是在节点1上创建的。 前面已经说过,因为它是共享的,所以在节点2上也能查询到。

如果遇到如下错误:

ORA-00569: Failed to acquire global enqueue.
Cause: A prior error occurred on one of the instances in the cluster. Typically errors are caused by shared pool resource contention.
Action: Check for and resolve prior errors on all instances in the cluster. If there is shared pool resource contention, increase the SHARED_POOL_SIZE, DML_ LOCKS, PROCESSES, TRANSACTIONS, CLUSTER_DATABASE_INSTANCES and PARALLEL_MAX_SERVERS initialization parameters.

就检查一下ASM的相关参数。
Oracle ASM 详解http://www.linuxidc.com/Linux/2011-09/42727.htm

2.2            修改数据文件大小

默认情况下,user 表空间是5M, 我们来把它改成10M。

SQL> ALTER DATABASE DATAFILE '+DATA/orcl/datafile/users.279.730181053' RESIZE 10M;
数据库已更改。


现在设置表空间的自动扩展属性:

SQL> ALTER DATABASE DATAFILE '+DATA/orcl/datafile/users.279.730181053'
  2  AUTOEXTEND ON
  3  NEXT 10M
  4  MAXSIZE UNLIMITED;

数据库已更改。

2.3 表空间添加数据文件

SQL> ALTER TABLESPACE DAVE ADD DATAFILE '+DATA/orcl/datafile/dave2.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 20M;

表空间已更改。


2.4 删除数据文件
SQL> ALTER TABLESPACE DAVE DROP DATAFILE '+DATA/orcl/datafile/dave2.dbf';
表空间已更改。


2.5. 删除表空间
SQL> drop tablespace dave including contents and datafiles;
表空间已删除。







上一篇:oracle增加表空间的四种方法
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表