第一篇:表空间
管理表空间和数据文件
小结:作用
Undo表空间和临时表空间的建立方法
了解各个状态(online,read write,readonly)的作用,及如何改变表空间的状态的方法。了解移动数据文件的原因,及使用alter tablespace和alter datatable命令移动数据文件的方法
介绍
表空间是数据库的逻辑组成部分,从物理上将,数据库数据存放在数据文件中;从逻辑上讲,数据库则是存放在表空间中,表空间是 由一个或多个数据文件组成。
表
表空间——》数据文件 数据库的逻辑结构
Oracle中逻辑结构包括表空间、段、区、块 一个数据库逻辑上由一个或多个表空间。表空间是由物理文件构成。可以达到以下作用:
(1)控制数据库占用的磁盘空间
(2)dba可以将不同数据类型部署到不同的位置,这样有利于提高I/O性能,同时利于备份和恢复等管理操作。建立表空间
建立表空间是使用create tablespace命令完成,需要注意的是,一般情况下,建立表空间是特权用户或是dba来执行的,如果其他用户来创建表空间,则用户必须要具有create tablespace的系统权限。建立数据表空间
在建立数据库后,为了便于管理,最好建立自己的表空间 Create tablespace date01(表空间的名)datefile(数据文件)‘d:testdata01.dbf’size 20m uniform size 128k(区128k)
说明;执行完上述命令后,会建立名称为date01的表空间,并为该表空间建立名称为date01.bdf的数据文件,区的大小为128k; 使用数据表空间
Create table mypart(deptno number(4), dname varchar2(14), loc varchar2(13))tablespace date01;改变表空间的状态
在建立表空间时,表空间处于联机的状态,此时该表空间是可以访问的,并且该表空间是可以读写的,即可以查询该表空间的数据,而且还可以在表空间执行各种语句。但是在进行系统维护或是数据库维护时,可能需要改变表空间的状态。一般情况下,由特权用户或是dba来操作。
(1)使表空间脱机
Alter tablespace 表空间名 offline;(2)使表空间联机
Alter tablesqace 表空间名online;(3)只读表空间
当建立表空间时,表空间可以读写,如果不希望在该表空间上执行update、delete、insert操作,那么可以将表空间修改为只读 Alter tablespace 表空间名
read only 改变表空间的状态
(1)显示表空间包括的所有表
Select * from all_tables where tablespace_name=’表空间名’(2)知道表名,查看该表属于那个表空间
(3)Select tablespace_name,table_name from user_tables where table_name=’emp’;删除表空间
Drop tablespace ‘表空间’ including contents and datafiles;说明:including contents 表示删除表空间时,删除该表空间的所有数据对象,而datafiles表示将数据文件也删除。扩展表空间
1、建立表空间 sp01
2、在该表空间上建立一个普通的表 mydment 其表结构和dept一样
3、想该表空间加入数据insert into mydment select * from dept;
4、当一定时候就会出现无法扩展的问题,怎么办?
5、就扩展该表空间,为其增加更多的存储空间。有三种方法:(1)增加数据文件
Alter tablespace sp01 add datefile ‘d:testsp01.dbf’ size 20m(2)增加数据文件的大小 Alter tablespace sp01 ‘d:testsp01.dbf’ resize 20m;(数据文件不超过500m)(3)设置文件的自动增长。
Alter tablespace 表空间名 ‘d:testsp01.dbf’ autoextend on next 10m maxsize 500m;故障处理
有时,如果你的数据文件所在的磁盘损坏时,该数据文件将不再能使用,为了能够重新使用,需要将这些文件的副本移动到其他的磁盘,然后恢复。
下面以移动数据文件sp_001.dbf为例说明: 1)确定数据文件所在的表空间 select tablespace_name
from
dba_data_files
where file_neme=’d:sp001.dbf’;2)使用表空间脱机
确保数据文件的一致性,将表空间转变为offline的状态 Alter tablespace sp01 offline;3)使用命令移动数据文件到指定的目标位置 Host move d:sp01.dbf c:sp01.dbf 4)移动数据文件
在物理上移动数据后,还必须执行alter tablespace 命令对数据库进行逻辑修改:
Alter tablespace sp01 rename datafile ‘d:sp01.dbf’ to ‘c:sp01.dbf’;5)使得表空间联机
在移动了数据文件后,为了使用户可以访问表空间,必须将其转变为online状态:
Alter tablespace sp01 online;
第二篇:第四章 管理表空间
第四章 管理表空间
4.1、基本表空间
一、创建表空间
1.创建一个表空间 myspace
CREATE
TABLESPACE myspace(表名)
DATAFILE 'D:apporacle11goradataorclmyspace.dbf'
SIZE 20M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
2.查看所创建的表空间的属性 : dba_tablespaces
DESC dba_tablespaces
二、修改表空间 ALTER TABLESPASCE
1.修改表空间的状态
四种状态: 1)、ONLINE 在线
2)、OFFLINE 离线
2)、READ ONLY 只读
4)、READ WRITE 读写 ALTER TABLESPACE myspace READ ONLY
注意:有只读状态修改为 在线状态时 直接改为读写状态就可以咯
2.重命名表空间名
ALTER TABLESPACE myspace RENAME TO myspace2
3.增加表空间的数据文件
1)、查看是否有空闲表空间 dba_free_space
SELECT tablespace_name,bytes,blocks FROM dba_free_space WHERE tablespace_name='MYSPACE';2)增加表空间
ALTER tablespace myspace
ADD DATAFILE 'd:apporacle11goradataorclmyspace.dbf' SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 40M, 'd:apporacle11goradataorclmyspace2.dbf' SIZE 2M AUTOEXTEND ON NEXT 2M MAXSIZE 10M;3)删除表空间的数据文件
ALTER TABLESPACE myspace
DROP DATAFILE 'd:apporacle11goradataorclmyspace2.dbf';
三、修改表空间的数据文件 ALTER DATABASE
1、修改数据文件大小
1)查看比哦啊空间的数据文件信息
dba_dat_files
SELECT tablespace_name,file_name,bytes
FROM dba_dat_files WHERE tablespace_name=' MYSPACE';
2)修改数据文件大小
ALTER DATABASE
DATAFILE 'd:apporacle11goradataorclmyspace2.dbf' RESIZE 20M;
2、修改数据文件的自动增量 : 默认为OFF
ALTER DATABASE DATAFILE 'd:apporacle11goradataorclmyspace2.dbf' AUTOEXTEND ON NEXT 5M MAXSIZE 40M;
3、修改数据文件的状态
1)、ONLINE 表示数据文件可以使用
2)、OFFLINE 表示数据文件不可以使用,用于数据库运行在归档模式下的情况。
3)、OFFLINE DROP 表示数据文件不可用,但它用于数据库运行在非归档模式下的情况。
注:
归档重做日志文件用于对写满的日志文件进行复制并保存,具体功能由归档进程ARCn实现,该进程负责将写满的重做日志文件复制到归档日志目标中。
归档日志模式和非归档日志模式的区别
非归档模式只能做冷备份,并且恢复时只能做完全备份.最近一次完全备份到系统出错期间的数据不能恢复.归档模式可以做热备份,并且可以做增量备份,可以做部分恢复.用ARCHIVE LOG LIST 可以查看当前模式状态是归档模式还是非归档模式.ALETER DATABASE
DATAFILE 'd:apporacle11goradataorclmyspace2.dbf'
OFFLINE DROP;注:需要介质恢复
RECOVER
DATAFILE 'd:apporacle11goradataorclmyspace2.dbf';
4、删除表空间
DROP TABLESPACE myspace INCLUDING(包含)CONTENTS(内容)AND DATAFILES;4.2、临时表空间
一、创建临时表空间
1、创建临时表空间
CREATE TEMPRARY TABLESPACE mytemp
TEMPFILE 'E:orafilemytemp.dbf'
SIZE 10M AUTEXTEND ON NEXT 2M MAXSIZE 20M;
2、查看临时表空间的临时文件信息 : dba_temp_file
SELECT tablespace_name,file_name,bytes
FROM dba_temp_file WHERE tabespace_name='MYTEMP';
二、创建与管理临时表空间组
1、创建:不需要特别指定
CREATE TEMPRARY TABLESPACE mytemp2
TEMPFILE 'E:orafilemytemp2.dbf'
SIZE 10M AUTEXTEND ON NEXT 2M MAXSIZE 20M
TABLESPACE GROUP group1;
2、查看:dba_tablespace_groups
3、移动临时抱空间到另一组
ALTER TABLESPACE mytemp2 TABLESPACE GROUP group2;
4、删除临时表空间组:临时表空间被删除或者移走,临时表空间组也就不在咯
DROP TABLESPACE mytemp2;
第三篇:oracle-sql语句-创建表空间、数据库
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;获取创建表空间的sql select
dbms_lob.substr(dbms_metadata.get_ddl('TABLESPACE',a.tablespace_name))from dba_tablespaces a;
SQL> select sid,serial# from v$session where username='TEST';alter system kill session '150,9019';查看被锁的表 select object_name,machine,s.sid,s.serial#,s.event from gv$locked_object l,dba_objects o,gv$session s where l.object_id=o.object_id and l.session_id=s.sid;alter system kill session ‘sid,serial#’;
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;
Select b.tablespace_name as表空间名,b.bytes as字节数,(b.bytes-sum(nvl(a.bytes,0)))as已使用,sum(nvl(a.bytes,0))as剩余空间,sum(nvl(a.bytes,0))/(b.bytes)*100 as 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id;
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 segment_name,bytes FROM dba_segments Where bytes>10000000 AND tablespace_name='tablespace_name';10.查看所有表空间的碎片程度(值在30以下表示碎片很多)
select tablespace_name,sum(bytes),sum(free),sum(free)*100/sum(bytes)from(select
b.file_id file_ID,b.tablespace_name tablespace_name,b.bytes Bytes,(b.bytes-sum(nvl(a.bytes,0)))used,sum(nvl(a.bytes,0))free,sum(nvl(a.bytes,0))/(b.bytes)*100
Percent
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id)group by tablespace_name order by sum(free)*100/sum(bytes);11.查看自上次数据库启动以来所有数据文件的读写次数
select
substr(DF.NAME,1,5)Drive,DF.NAME file_name,(fs.phyblkrd+fs.phyblkwrt)
from v$filestat fs,v$datafile df
where df.file#=fs.file#;
创建临时表空间: //创建临时表空间
create temporary tablespace mytest tempfile 'E:/Program/oracle/product/10.1.0/oradata/orcl/mytest.dbf' size 32m autoextend on next 32m maxsize 1024m extent management local;
//创建数据表空间
create tablespace mytest_data logging datafile 'E:/Program/oracle/product/10.1.0/oradata/orcl/mytest_data.dbf' size 32m autoextend on next 32m maxsize 1024m extent management local;//创建用户并指定表空间
create user testaccount identified by testpwd default tablespace mytest_data temporary tablespace mytest;//给用户授予权限
grant connect,resource to testaccount;(db2:指定所有权限)12,创建一个用户:
CREATE USER username IDENTIFIED BY password OR IDENTIFIED EXETERNALLYOR IDENTIFIED GLOBALLY AS ‘CN=user’[DEFAULT TABLESPACE tablespace][TEMPORARY TABLESPACE temptablespace][QUOTA [integer K[M] ] [UNLIMITED] ] ON tablespace[,QUOTA [integer K[M] ] [UNLIMITED] ] ON tablespace[PROFILES profile_name][PASSWORD EXPIRE][ACCOUNT LOCK or ACCOUNT UNLOCK];
//查看数据库中的所有用户: select * from all_users;//or select * from dba_users 13查看oracle最大连接数: Select session_max,session_current,sessions_highwater,users_max from v$license;Select username,count(username)from v$session group by username;加密存储过程 WRAP
INAME=D:ORACLESOURCEDATE.SQL ONAME=D:ORACLESOURCEDATA.PLD
第四篇:Oracle建表空间各种语句
在创建用户之前,先要创建表空间:
其格式为:格式: create tablespace 表间名 datafile '数据文件名' size 表空间大小;如:
SQL> create tablespace news_tablespace datafile 'F:oracleproduct10.1.0oradatanewsnews_data.dbf' size 500M;其中'news_tablespace'是你自定义的表空间名称,可以任意取名;'F:oracleproduct10.1.0oradatanewsnews_data.dbf'是数据文件的存放位置,'news_data.dbf'文件名也是任意取;'size 500M'是指定该数据文件的大小,也就是表空间的大小。
现在建好了名为'news_tablespace'的表空间,下面就可以创建用户了:
其格式为:格式: create user 用户名 identified by 密码 default tablespace 表空间表;如:
SQL> create user news identified by news default tablespace news_tablespace;默认表空间'default tablespace'使用上面创建的表空间。
接着授权给新建的用户:
SQL> grant connect,resource to news;--表示把 connect,resource权限授予news用户
SQL> grant dba to news;--表示把 dba权限授予给news用户
授权成功。
ok!数据库用户创建完成,现在你就可以使用该用户创建数据表了!1.建表空间
create tablespace hoteldata datafile 'D:javaOracleproduct10.1.0oradatazznorclhoteldata.dbf'size 200m autoextend on next 10m maxsize unlimited;2.建用户 create user hotel identified by hotel default tablespace hoteldata account unlock;//identified by 后面的是密码,前面的是用户名 3.用户授权
grant resource,connect,RECOVERY_CATALOG_OWNER to hotel;grant create table to hotel;alter user hotel quota unlimited ON OSDB;alter user hotel default tablespace hoteldata;4.删除表空间
DROP TABLESPACE hoteldata INCLUDING CONTENTS AND DATAFILES;5.删除用户
DROP USER hotel CASCADE 6.删除表的注意事项
在删除一个表中的全部数据时,须使用TRUNCATE TABLE 表名;因为用DROP TABLE,DELETE * FROM 表名时,TABLESPACE表空间该表的占用空间并未释放,反复几次DROP,DELETE操作后,该TABLESPACE上百兆的空间就被耗光了。oracle sqlplus脚本建库总结(原创)******************************************************************/--查询表空间参数
select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;--查询数据文件信息
--autoextensible数据库已满后是否自动扩展
select tablespace_name,bytes,autoextensible,file_name from dba_data_files;/******************************************************************/--创建表空间
--一般信息
--DATAFILE:数据文件目录--存储
--AUTOEXTEND:数据文件满后自动扩展--ON NEXT:增量
--MAXSIZE UNLIMITED:最大容量无限制--SIZE:文件大小--存储
--启用事件记录:LOGGING为生成从做日志并可恢复,NOLOGGING为快速更新不生成日志且不可恢复
--MANAGEMENT LOCAL:本地管理--缺省:自动分配
--UNIFORM SIZE:统一分配
--MANAGEMENT DICTIONARY:在字典中管理--DEFAULT STORAGE:覆盖默认区值--INITIAL:初始大小--NEXT :下一个大小--MINEXTENTS:最小数量
--MAXEXTENTS UNLIMITED :最大数量不限制--PCTINCREASE:增量,单位“%”--MINIMUM EXTENT:最小区大小 CREATE TABLESPACE “TEST” NOLOGGING DATAFILE 'G:ORACLEORADATAMYORACLETEST.ora' SIZE 5M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 12K;--最好写成相对路径,免得出错 CREATE TABLESPACE “TEST” NOLOGGING DATAFILE '../DATABASE/TEST.ora' SIZE 5M REUSE--建议用'../oradata/TEST.ora' AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 12K;CREATE TABLESPACE “TEST” LOGGING DATAFILE 'G:ORACLEORADATAMYORACLETEST.ora' SIZE 5M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE(INITIAL 1K NEXT 2K MINEXTENTS 5 MAXEXTENTS 67 PCTINCREASE 4)MINIMUM EXTENT 3K;/******************************************************************/--增加表空间, 注意这里test.ora1不能与原表空间文件名称相同--添加一个新的大小为5M数据库文件test.ora1 alter tablespace mytesttablespace add datafile 'c:testtest.ora1' size 5M;/******************************************************************/--修改表空间数据库文件属性,myoracle为sid--将test.ora1数据库文件改为3M,其中resize可以是ONLINE, OFFLINE, RESIZE, AUTOEXTEND 或 END/DROP alter database myoracle datafile 'c:testtest.ora1' resize 3M;alter database myoracle datafile '$ORACLE_HOME/oradata/undo102.dbf' autoextend on next 10m maxsize 500M;/******************************************************************/ /*删除表空间
语法:drop tablespace tablespace_name including contents and datafiles;删除表空间时要系统不会删除表空间数据库文件,要彻底删除要手动删除 */ drop tablespace mytesttablespace including contents and datafiles;/******************************************************************/--创建用户--命令:
--CREATE USER 名称 IDENTIFIED BY 口令 DEFAULT TABLESPACE “默认表空间名” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK--一般属性
--DEFAULT TABLESPACE :默认表空间名
--TEMPORARY TABLESPACE :临时表空间名,默认TEMP--ACCOUNT :用户状态,默认UNLOCK 未锁定;LOCK 锁定 CREATE USER “TEST” PROFILE “DEFAULT” IDENTIFIED BY “test” DEFAULT TABLESPACE “TEST” TEMPORARY TABLESPACE “TEMP” ACCOUNT UNLOCK;--可以写成
CREATE USER “TEST” PROFILE “DEFAULT” IDENTIFIED BY “test” DEFAULT TABLESPACE “TEST”;/******************************************************************/--用户授权
--grant “connect,resource,dba” to “someuser” with admin option;--WITH ADMIN OPTION :管理选项--授予usertest DBA权限
grant dba to “usertest” with admin option;--取消授权
--REVOKE “RESOURCE” FROM “SCOTT”;/******************************************************************/--建表
--在usertest方案下建表,注意表名不能用关键字,否则报错ORA-00903: 表名无效
--语法:
--create table [schema.]
--data type:表示字段的数据类型
--default
CREATE TABLE “TEST”.“TEST”(“ID” NUMBER(10)DEFAULT 0 PRIMARY KEY, “NAME” VARCHAR2(20)NOT NULL, “INFO” VARCHAR2(1000))TABLESPACE “TEST”;/******************************************************************/--插入数据--语法:
--INSERT INTO tablename(column1,column2,…)VALUES(expression1,expression2,…);
INSERT INTO “TEST”.“TEST”(“ID” ,“NAME” ,“INFO”)VALUES(1 ,'testname' ,'');--也可以写成
INSERT INTO “TEST”.“TEST” VALUES(1 ,'testname' ,'');/******************************************************************/
第五篇:Oracle表空间和数据文件的关系
数据库的物理结构是由数据库的操作系统文件所决定,每一个Oracle数据库是由三种类型的文件组成: 数据文件和控制文件。数据库的文件为数据库提供真正的物理存储。
每一个Oracle数据库有一个或多个物理的数据文件(data file)。一个数据库的数据文件包含全部数据库数据。逻辑数据库结构(如表,索引等)的数据物理地存储在数据库地数据文件中。数据文件通常为*.dbf格式,例如: userCIMS.dbf.数据文件有一下特征:①、一个数据文件仅与一个数据库联系;②、一旦建立, 数据文件只增不减;③、一个表空间(数据库存储地逻辑单位)由一个或多个数据文件组成。
其次, 我们再来叙述一下Oracle的逻辑结构: Oracle的逻辑结构包括表空间(tablespace), 段(segment), 数据块(data block)以及模式对象(schema object)。
Oracle数据库在逻辑上是由多个表空间组成的, 表空间在物理上包含一个或者多个数据文件。而数据文件大小是块大小的整数倍;表空间中存储的对象叫段, 比如数据段, 索引段和回退段。段由区组成, 区是磁盘分配的最小单位。段的增大是通过增加区的个数来实现的。每个区的大小是数据块大小的整数倍,取得大小可以不同;数据块是数据库中的最小的I/O单位,同时也是内存数据缓冲区的单位,及数据文件存储空间单位。块的大小由参数DB_BLOCK_SIZE设置,其值影设置为操作系统块大小的整数倍。
⑴ 表空间(TABLESPACE)表空间是数据库中最大的逻辑单位,每一个表空间由一个或多个数据文件组成,一个数据文件只能与一个表空间相联系。每一个数据库都有一个SYSTEM表空间,该表空间是在数据库创建或数据库安装时自动创建的,用于存储系统的数据字典表,程序系统单元,过程函数,包和触发器等,也可用于存储用户数据表,索引对象。表空间具有在线(online)和离线(offline)属性,可以将除SYSTEM以外的任何表空间置为离线。⑵ 段(SEGMENT)数据库的段可以分为4类:数据段,索引段,回退段和临时段。⑶ 区
区是磁盘空间分配的最小单位。磁盘按区划分,每次至少分配一个区。区存储在段中,它由连续的数据块组成。⑷ 数据块
数据块是数据库中最小的数据组织单位与管理单位,是数据库文件磁盘存储空间单位,也是数据库I/O的最小单位,数据块大小由DB_BLOCK_SIZE参数决定,不同的ORACLE版本BD_BLOCK_SIZE的默认值是不同的。查询DB_BLOCK_SIZE可以使用sql:
select name, value from v$parameter where name = 'db_block_size';⑸ 模式对象
模式对象是一种应用,包括:表、聚簇、试图、索引序列生成器、同义词、哈希、程序单元、数据库链的等。
最后说一下ORACLE的用户、表空间和数据文件的关系:
一个用户可以使用一个或多个表空间,一个表空间也可以提供多个用户使用。用户和表空间没有隶属关系,表空间是一个用来管理数据存储的逻辑概念,表空间只是和数据文件发生关系,数据文件是物理的。一个表空间可以包含多个数据文件,而一个数据文件只能隶属于一个表空间。
总结一下:解释数据库、表空间、数据文件、表、数据的最好办法就是想象一个装满东西的柜子。数据库其实就是柜子,柜中的抽屉是表空间,抽屉中的文件夹是数据文件,数据夹中的纸是表,写在纸上的信息就是数据。