用电脑怎么查oracle,Oracle 表空间查询与操作方法 -电脑资料

一，

1.查询oracle表空间的使用情况

select b.file_id　　文件ID,

b.tablespace_name　　表空间,

b.file_name　　　　　物理文件名,

b.bytes　　　　　　　总字节数,

(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,

sum(nvl(a.bytes,0))　　　　　　　　剩余,

sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

group by b.tablespace_name,b.file_name,b.file_id,b.bytes

order by b.tablespace_name

2.查询oracle系统用户的默认表空间和临时表空间

select default_tablespace,temporary_tablespace from dba_users

3.查询单张表的使用情况

select segment_name,bytes from dba_segments where segment_name = 'RE_STDEVT_FACT_DAY' and wner = USER

RE_STDEVT_FACT_DAY是您要查询的表名称

4.查询所有用户表使用大小的前三十名

select * from (select segment_name,bytes from dba_segments where wner = USER order by bytes desc ) where rownum <= 30

5.查询当前用户默认表空间的使用情况

select tablespacename,sum(totalContent),sum(usecontent),sum(sparecontent),avg(sparepercent)

from

(

SELECT b.file_id as id,b.tablespace_name as tablespacename,b.bytes as totalContent,(b.bytes-sum(nvl(a.bytes,0))) as usecontent,sum(nvl(a.bytes,0)) as sparecontent,sum(nvl(a.bytes,0))/(b.bytes)*100 as sparepercent

FROM dba_free_space a,dba_data_files b

WHERE a.file_id=b.file_id and b.tablespace_name = (select default_tablespace from dba_users where username = user)

group by b.tablespace_name,b.file_name,b.file_id,b.bytes

)

GROUP BY tablespacename

6.查询用户表空间的表

select * from user_tables

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

一、建立表空间

CREATE TABLESPACE test

DATAFILE 'c:/oracle/oradata/db/test01.dbf' SIZE 50M

UNIFORM. SIZE 1M; #指定区尺寸为128k,如不指定，区尺寸默认为64k

或

CREATE TABLESPACE test

DATAFILE 'c:/oracle/oradata/db/test01.dbf' SIZE 50M

MINIMUM EXTENT 50K EXTENT MANAGEMENT LOCAL

DEFAULT STORAGE (INITIAL 50K NEXT 50K MAXEXTENTS 100 PCTINCREASE 0);

可从dba_tablespaces中查看刚创建的表空间的信息

二、建立UNDO表空间

CREATE UNDO TABLESPACE test_undo

DATAFILE 'c:/oracle/oradata/db/test_undo.dbf' SIZE 50M

UNDO表空间的EXTENT是由本地管理的，而且在创建时的SQL语句中只能使用DATAFILE和EXTENT MANAGEMENT子句。

ORACLE规定在任何时刻只能将一个还原表空间赋予数据库，即在一个实例中可以有多个还原表空间存在，但只能有一个为活动的。可以使用ALTER SYSTEM命令进行还原表空间的切换。

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = test_undo;

三、建立临时表空间

CREATE TEMPORARY TABLESPACE test_temp

TEMPFILE '/oracle/oradata/db/test_temp.dbf' SIZE 50M

查看系统当前默认的临时表空间

select * from dba_properties where property_name like 'DEFAULT%'

改变系统默认临时表空间

alter database default temporary tablespace test_temp;

四、改变表空间状态

1.使表空间脱机

ALTER TABLESPACE test OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项

ALTER TABLESPACE game test FOR RECOVER;

2.使表空间联机

ALTER TABLESPACE test ONLINE;

3.使数据文件脱机

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机

ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读

ALTER TABLESPACE test READ ONLY;

6.使表空间可读写

ALTER TABLESPACE test READ WRITE;

五、删除表空间

DROP TABLESPACE test INCL ING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

DROP TABLESPACE　表空间名 [INCL ING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]

1. INCL ING CONTENTS　子句用来删除段

2. AND DATAFILES　子句用来删除数据文件

3. CASCADE CONSTRAINTS　子句用来删除所有的引用完整性约束

六、扩展表空间

首先查看表空间的名字和所属文件

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

1.增加数据文件

ALTER TABLESPACE test

ADD DATAFILE '/oracle/oradata/db/test02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸

ALTER DATABASE DATAFILE 'c:/oracle/oradata/db/test01.dbf'

RESIZE 100M;

3.设定数据文件自动扩展

ALTER DATABASE DATAFILE 'c:/oracle/oradata/db/test01.dbf'

AUTOEXTEND ON NEXT 100M

MAXSIZE 200M;

设定后可从dba_tablespace中查看表空间信息，从v$datafile中查看对应的数据文件信息

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

create tablespace scgl

datafile 'E:\ORACLE\PROD T\10.1.0\ORADATA\ORCL\scgl2.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

create tablespace test_data

logging

datafile 'E:\ORACLE\PROD T\10.1.0\ORADATA\ORCL\user_data.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

create user scgl identified by qwer1234

default tablespace scgl

temporary tablespace scgl_temp;

tempfile 'E:\ORACLE\PROD T\10.1.0\ORADATA\ORCL\user_temp.dbf'

create temporary tablespace scgl_temp

tempfile 'E:\ORACLE\PROD T\10.1.0\ORADATA\ORCL\scgl_temp.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

grant connect,resource, dba to scgl;

oracle创建表空间 SYS用户在CMD下以DBA身份登陆：

在CMD中打sqlplus /nolog

然后再

conn / as sysdba

//创建临时表空间

create temporary tablespace user_temp

tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

//创建数据表空间

create tablespace test_data

logging

datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

//创建用户并指定表空间

create user username identified by password

default tablespace user_data

temporary tablespace user_temp;

查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 1

查询表空间的free space

select tablespace_name,

count(*) as extends,

round(sum(bytes) / 1024 / 1024, 2) as MB,

sum(blocks) as blocks

from dba_free_space

group by tablespace_name;

--查询表空间的总容量

select tablespace_name, sum(bytes) / 1024 / 1024 as MB

from dba_data_files

group by tablespace_name;

查询表空间使用率

select total.tablespace_name,

round(total.MB, 2) as Total_MB,

round(total.MB - free.MB, 2) as Used_MB,

round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct

from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

from dba_free_space

group by tablespace_name) free,

(select tablespace_name, sum(bytes) / 1024 / 1024 as MB

from dba_data_files

group by tablespace_name) total

where free.tablespace_name = total.tablespace_name;

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

1.建立表空间:create tablespace test datafile '/u01/test.dbf' size 10M uniform. size 128k

#指定区尺寸为128k ,块大小为默认8K

#大文件表空间 create bigfile tablespace big_tbs datafile '/u01/big_tbs.dbf ' size 100G

2.建非标准表show parameter db alter system set db_2k_cache_size=10M create tablespace test datafile '/u01/test.dbf' size 10M blocksize 2K uniform. size 128k

#常见错误

SQL> alter system set db_2k_cache_size=2M; alter system set db_2k_cache_size=2M ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-00384: Insufficient memory to grow cache

#解决

SQL> alter system set sga_max_size=400M scope=spfile; SQL> shutdown immediate; SQL> startup SQL> alter system set db_2k_cache_size=10M; System altered.

3.查看区大小与块大小#区大小 conn y / 123 create table t(i number) tablespace test; Insert into t values(10) select bytes/1024 from user_segments where segment_name=upper('t');

#块大小 Show parameter block(默认64K)

#非标准表空间的blocksize SQL> select * from v$dbfile; SQL> select name,block_size,status from v$datafile; SQL> select block_size from v$datafile where file#=14;

4.删除表空间drop tablespace test including contents and datafiles

5.查表空间:#查数据文件 select * from v$dbfile; #所有表空间 select * from v$tablespace;

#表空间的数据文件 select file_name,tablespace_name from dba_data_files;

6.建立undo表空间create undo tablespace undotbs01 datafile '/u01/undotbs01.dbf' size 5M;

#切换到新建的undo表空间 alter system set undo_tablespace=undotbs01;

7.建立临时表空间create temporary tablespace temp_data tempfile '/u01/temp.db' size 5M; create bigfile temporary tablespace bigtem tempfile '/u01/bigtemp.db' size 5M;

8.改变表空间状态

(0.)查看状态

#表空间状态 select tablespace_name,block_size,status from dba_tablespaces;

#数据文件状态 select name,block_size,status from v$datafile;

(1.)表空间脱机alter tablespace test offline

#如果意外删除了数据文件 alter tablespace test offline for recover

(2.)表空间联机alter tablespace test online

(3.)数据文件脱机select * from v$dbfile; alter database datafile 3 offline

(4.)数据文件联机recover datafile 3; alter database datafile 3 online;

(5.)使表空间只读alter tablespace test read only

(6.)使表空间可读写alter tablespace test read write;

9.扩展表空间#首先查看表空间的名字和所属文件及空间 select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; #三种扩展方法

1.alter tablespace test add datafile '/u01/test02.dbf' size 10M(自动加一个datafile)

2.alter database datafile '/u01/test.dbf' resize 20M;

3.alter database datafile '/u01/test.dbf' autoextend on next 10M maxsize 1G;

#设定后查看表空间信息

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;

10.移动表空间的数据文件

#先确定数据文件据在表空间

SQL>select tablespace_name,file_name from dba_data_files where file_name='/u01/test.dbf';

#open状态

SQL>alter tablespace test offline; SQL>host move /u01/test.dbf /u01/oracle/test.dbf; SQL>alter tablespace test rename datafile '/u01/test.dbf' to '/u01/oracle/test.dbf'; SQL>alter tablespace test offline;

#mount状态 SQL>shutdown immediate; SQL>startup mount SQL>host move /u01/test.dbf /u01/oracle/test.dbf; SQL>alter database rename file '/u01/test.dbf' to '/u01/oracle/test.dbf';

11.表空间和数据文件常用的数据字典与动态性能视图v$dbfile v$datafile dba_segments user_segments dba_data_files v$tablespace dba_tablespaces user_tablespaces

--查询表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME) D

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 1

--查询表空间的free space

select tablespace_name,

count(*) as extends,

round(sum(bytes) / 1024 / 1024, 2) as MB,

sum(blocks) as blocks

from dba_free_space

group by tablespace_name;

--查询表空间的总容量

select tablespace_name, sum(bytes) / 1024 / 1024 as MB

from dba_data_files

group by tablespace_name;

--查询表空间使用率

select total.tablespace_name,

round(total.MB, 2) as Total_MB,

round(total.MB - free.MB, 2) as Used_MB,

round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct

from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB

from dba_free_space

group by tablespace_name) free,

(select tablespace_name, sum(bytes) / 1024 / 1024 as MB

from dba_data_files

group by tablespace_name) total

where free.tablespace_name = total.tablespace_name;

//给用户授予权限

grant connect,resource to username;

//以后以该用户登录，创建的任何数据库对象都属于user_temp 和user_data表空间，

这就不用在每创建一个对象给其指定表空间了

撤权：

revoke 权限... from 用户名;

删除用户命令

drop user user_name cascade;

建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM. SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

删除表空间

DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES;

一、建立表空间

CREATE TABLESPACE data01

DATAFILE '/oracle/oradata/db/DATA01.dbf' SIZE 500M

UNIFORM. SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k

二、建立UNDO表空间

CREATE UNDO TABLESPACE UNDOTBS02

DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M

#注意：在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间:

ALTER SYSTEM SET undo_tablespace=UNDOTBS02;

三、建立临时表空间

CREATE TEMPORARY TABLESPACE temp_data

TEMPFILE '/oracle/oradata/db/TEMP_DATA.dbf' SIZE 50M

四、改变表空间状态

1.使表空间脱机

ALTER TABLESPACE game OFFLINE;

如果是意外删除了数据文件，则必须带有RECOVER选项

ALTER TABLESPACE game OFFLINE FOR RECOVER;

2.使表空间联机

ALTER TABLESPACE game ONLINE;

3.使数据文件脱机

ALTER DATABASE DATAFILE 3 OFFLINE;

4.使数据文件联机

ALTER DATABASE DATAFILE 3 ONLINE;

5.使表空间只读

ALTER TABLESPACE game READ ONLY;

6.使表空间可读写

ALTER TABLESPACE game READ WRITE;

五、删除表空间

DROP TABLESPACE data01 INCL ING CONTENTS AND DATAFILES;

六、扩展表空间

首先查看表空间的名字和所属文件

select tablespace_name, file_id, file_name,

round(bytes/(1024*1024),0) total_space

from dba_data_files

order by tablespace_name;

1.增加数据文件

ALTER TABLESPACE game

ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 1000M;

2.手动增加数据文件尺寸

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf'

RESIZE 4000M;

3.设定数据文件自动扩展

ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf

AUTOEXTEND ON NEXT 100M

MAXSIZE 10000M;

设定后查看表空间信息

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

您可能感兴趣的文章:

oracle查看表空间已分配和未分配空间的语句分享

oracle表空间表分区详解及oracle表分区查询使用方法

Oracle中如何把表和索引放在不同的表空间里

六分钟学会创建Oracle表空间的实现步骤

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

oracle 创建表空间步骤代码

oracle增加表空间大小两种实现方法

oracle 创建表空间详细介绍

Oracle新建用户、角色，授权，建表空间的sql语句

Oracle7.X 回滚表空间数据文件误删除处理方法

Oracle7.X 回滚表空间数据文件误删除处理方法

Oracle7.X 回滚表空间数据文件误删除处理方法

shell脚本操作oracle删除表空间、创建表空间、删除用户

QQ空间 搜狐微博 人人网 开心网 百度搜藏更多

Tags：oracle 查询表空间

复制链接收藏本文打印本文关闭本文返回首页

上一篇：oracle保留两位小数解决方案

下一篇：Oracle排名函数(Rank)实例详解

相关文章

2009-11-11Linux ORCLE数据库增量备份脚本

2007-03-03Oracle SQL性能优化系列学习二

2012-10-10在Oracle的函数中,返回表类型的语句

2012-11-11Oracle客户端的安装与远程连接配置方法分享

2007-03-03Oracle对两个数据表交集的查询

2009-06-06Oracle SID存在解決方法

2013-05-05oracle指定排序的方法详解

2012-10-10oracle ORA-01114、ORA-27067错误解决方法

2013-05-05oracle定时备份压缩的实现步骤

2009-07-07oracle 服务启动，关闭脚本(windows系统下)

文章评论

最 近 更 新

ORACLE常见错误代码的分析与解决(二)

oracle常用sql语句

Oracle数据库的备份及恢复策略研究

Oracle针对数据库某一行进行操作的时候，

Oracle的默认用户密码

Oracle性能究极优化 下

有关Oracle数据库的备份情况

Oracle Number型数值存储与转换的实现详解

oracle em 按钮乱码解决办法及em网页变成

Oracle随机函数之dbms_random使用详解

热 点 排 行

ORACLE 10g 安装教程[图文]

oracle常用sql语句

oracle sqlplus 常用命令大全

oracle 触发器 学习笔记

ORACLE常用数值函数、转换函数、

Oracle数据库下载及安装图文操作

oracle中的视图详解

oracle 查询表名以及表的列名

ORACLE数据库查看执行计划的方法

oracle 存储过程和函数例子