失眠网 > 查看表空间大小和使用率增加表空间大小的四种方法

查看表空间大小和使用率增加表空间大小的四种方法

时间：2024-06-08 04:35:37

查看表空间大小和使用率**

查看当前用户使用的表空间情况，使用命令select * from user_users即可，其中username标识用户名，default_tablespace表示默认的表空间。如果我们想查看表空间的使用情况，比如表空间利用率等指标，首先我们查询的用户必须有dba权限，使用如下的命令查询即可

select a.tablespace_name as tablespace_name,total,free,total-free as used,(total-free) as usepercent

from (select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) a,

(select tablespace_name,sum(bytes)/1024/1024 free from dba_data_files group by tablespace_name) b where

a.tablespace_name = b.tablespace_name;查看oracle下面所有的表空间，使用命令select * from Dba_Tablespaces即可，呈现的信息包括表空间名称以及表空间的大小等。查看表空间物理文件的名称、位置及大小信息，表空间文件通常以dbf的后缀方式存储。

select tablespace_name,file_id,file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

5.有些查询表空间的语句需要执行用户需要高的权限，如何查看当前用户的角色呢？使用命令select * from user_role_privs即可。

增加表空间大小的四种方法

Meathod1：给表空间增加数据文件

ALTER TABLESPACE app_data ADD DATAFILE

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF’ SIZE 50M;

Meathod2：新增数据文件，并且允许数据文件自动增长

ALTER TABLESPACE app_data ADD DATAFILE

‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF’ SIZE 50M

AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

Meathod3：允许已存在的数据文件自动增长

ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF’

AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

Meathod4：手工改变已存在数据文件的大小

ALTER DATABASE DATAFILE ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF’

RESIZE 100M;

英文版

View table space size and usage

View the table space used by the current user, and use the command select * from user_ Users, where user name identifies the user name and default_ A tablespace represents the default tablespace.If we want to view the usage of table space, such as the utilization of table space and other indicators, first of all, the user we query must have DBA permission. Use the following command to query

select a.tablespace_ name as tablespace_ name,total,free,total-free as used,(total-free) as usepercent

from (select tablespace_ name,sum(bytes)/1024/1024 total from dba_ data_ files group by tablespace_ name) a,

(select tablespace_ name,sum(bytes)/1024/1024 free from dba_ data_ files group by tablespace_ name) b where

a.tablespace_ name = b.tablespace_ name;View all the table spaces under Oracle and use the command select * from DBA_ The information presented includes the name of the table space and the size of the table space.View the name, location and size information of the physical file in the table space. The table space file is usually stored in the form of DBF suffix.

select tablespace_ name,file_ id,file_ name,round(bytes/(1024*1024),0) total_ space from dba_ data_ files order by tablespace_ name;Some query table space statements need to be executed, and the user needs high permissions. How to view the role of the current user? Use the command select * from user_ role_ PRIVS.