select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;
查询数据块的大小DB_BLOCK_SIZE
select name,value from v$parameter where name like '%size%'
或者
SHOW PARAMETER DB_BLOCK_SIZE;
修改表空间文件的大小
select file_id,bytes,file_name from dba_data_files;
先查看表空间的文件
alter database datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\USERS01.DBF' resize 100m;
修改完成后重新查看文件的大小
select tablespace_name,bytes,autoextensible,file_name from dba_data_files;
用以M为单位的
select tablespace_name,bytes/1048576||'M',autoextensible,file_name from dba_data_files
像自动扩展的属性
select bytes/1048576||'M',autoextensible,maxbytes/1048576||'M',file_name from dba_data_files;
创建扩展属性的表空间文件
create tablespace sales datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\sales01.DBF' size 50m autoextend on next 5m maxsize 100m,
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\sales02.DBF' size 50m autoextend off,
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\sales03.DBF' size 50m autoextend on next 10m maxsize unlimited;
添加数据库表空间的数据库文件
alter tablespace sales add datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\sales04.DBf' size 50m autoextend on next 5m
maxsize 100m;
修改数据文件的自动扩展的属性
alter database datafile 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\USERS01.DBF' autoextend off;
select tablespace_name,bytes,autoextensible,file_name from dba_data_files;
查看修改后的结果
select tablespace_name,bytes,autoextensible,file_name from dba_data_files;
查看数据表空间的存储参数
select * from dba_tablespaces
查看数据表空间的存储参数精确
select t1.tablespace_name,t1.block_size,t1.initial_extent,t1.next_extent,t1.max_extents,t1.min_extents from dba_tablespaces t1
查看表的存储空间大小和存储方法
select iniitial_exten,next_extent,min_extents,max_extents,pct_increase
from user_tables where table='EMP'
SQL> select initial_extenT,next_extent,min_extents,max_extents,pct_increase
2 from user_tables where table_NAME='EMP';
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ----------- ------------
65536 1 2147483645
select segment_name,table_name,extent_id,bytes,blocks from user_extents
where segment_name='EMP';
SQL> select segment_name table_name,extent_id,bytes,blocks from user_extents
2 where segment_name='EMP';
TABLE_NAME
--------------------------------------------------------------------------------
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
EMP
0 65536 8
查看数据库日志文件
select * from v$log
group#日志文件组号
thread#文件线程号（一般为1，双机容错为2)
sequence# 日志序列号 日志切换后自动加1
bytes 日志文件大小 50M 是默认的值
members 改组日志成员个数
ARCHIVED 时候 已经完成归档
STATUS 改组状态 是否在可用状态
FIRST_CHANGE#系统更改号（SCN) 也叫检查点号
FIRST_TIME 系统改变时间
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
1 1 23 52428800 1 NO INACTIVE 1267252 2008-8-23 2:25:17
2 1 24 52428800 1 NO CURRENT 1307360 2008-8-23 14:07:20
3 1 22 52428800 1 NO INACTIVE 1230984 2008-8-22 17:00:15
查看日志文件
select * from v$logfile
日志强制切换
alter system switch logfile;
切换后结果如下
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
1 1 23 52428800 1 NO INACTIVE 1267252 2008-8-23 2:25:17
2 1 24 52428800 1 NO ACTIVE 1307360 2008-8-23 14:07:20
3 1 25 52428800 1 NO CURRENT 1337784 2008-8-23 23:30:28
查看历史日志信息
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from v$log_history;
确认是否归档模式，产生归档日志
select dbid,name,created,log_mode from v$database;
DBID NAME CREATED LOG_MODE
2154395220 YU10 2008-7-30 11:28:20 NOARCHIVELOG
也可以通过服务器端直接看
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Current log sequence 25
更改archive log 将非归档模式改成归档模式
C:\Documents and Settings\user>sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 23 23:55:00 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 25
Current log sequence 27
SQL> show parameter log_archive_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3204448256 bytes
Fixed Size 1304912 bytes
Variable Size 651957936 bytes
Database Buffers 2550136832 bytes
Redo Buffers 1048576 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
然后做一个归档日志强制切换
日志强制切换
alter system switch logfile;
然后再做一个查询
select recid,stamp,thread#,sequence#,name from v$archived_log;
结果归档日志结果如下
RECID STAMP THREAD# SEQUENCE# NAME
1 663552055 1 25 F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\YU10\ARCHIVELOG\2008_08_24\O1_MF_1_25_4C0F1PWO_.ARC
stamp 是时间序号 也叫时间戳
sequence 为日志序列号 要比CURRENT 的序号少1
NAME 中的为所存在的物理地址
F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\YU10\ARCHIVELOG\2008_08_24\O1_MF_1_25_4C0F1PWO_.ARC
select group#,sequence#,status,first_change# from v$log;
日志文件中的检查点好存储在数据字典V$LOG中的FIRST_CHANGE#列中
检查日志文件中的SCN号和日志中的一样，说明数据库是正常的。
select file#,checkpoint_change#,name from v$datafile;
select name,dbid,checkpoint_change# from v$database;
数据库头的同步号与日志文件，数据文件中检查点的号完全相同 ，三类数据检查点的文件号必须完全相同的时候，数据库才是正常的
alter database add logfile group 4 ('F:\oracle\product\10.2.0\oradata\yu10\REDO04a.log',
'F:\oracle\product\10.2.0\oradata\yu10\REDO04b.log') size 100m;
或者
alter database add logfile ('F:\oracle\product\10.2.0\oradata\yu10\REDO04a.log',
'F:\oracle\product\10.2.0\oradata\yu10\REDO04b.log') size 100m;
alter database drop logfile group 4;
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
删除日志组的时候必须
1.不能删除数据库仅有的两个日志组
2.不能删除活动的日志组
如果
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
ORA-01624: 日志 4 是紧急恢复实例 yu10 (线程 1) 所必需的
ORA-00312: 联机日志 4 线程 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\REDO04A.LOG'
ORA-00312: 联机日志 4 线程 1: 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\REDO04B.LOG
这个是在ARCHIVELOG 模式下 日志组的标识状态为ACTIVE的时候，数据库归档没有完成，
alter system switch logfile;
进行日志切换，等一下在删除。
添加和删除日志组的成员
alter database add logfile member 'F:\oracle\product\10.2.0\oradata\yu10\REDO03a.log' to group 3
alter database drop logfile member 'F:\oracle\product\10.2.0\oradata\yu10\REDO03a.log';
不能删除当前在使用的那个
删除成员不能删除日志组中唯一的一个成员
确保ARCHIVELOG 模式下的日志组已经归档
具有INVALID标识的成员不能删除，这个表示是 新增的成员，还没有使用，也不能删除。
移动日志文件成员
先做物理文件的移动
COPY F:\oracle\product\10.2.0\oradata\yu10\REDO01.LOG d:\REDO01.LOG
然后做以下命令
alter database rename file 'F:\oracle\product\10.2.0\oradata\yu10\REDO01.LOG' to 'c:\REDO01.LOG'
alter database rename file 'c:\REDO01.LOG' to 'F:\oracle\product\10.2.0\oradata\yu10\REDO01.LOG'
清除数据库的日志文件
应用以下示例
物理 删除一个日志文件 ，强迫重新启动数据库
delete F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\REDO01.LOG
然后重新启动
SQL> startup force
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 113246912 bytes
Database Buffers 167772160 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员
ORA-00312: 联机日志 1 线程 1:
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\REDO01.LOG'
使用不完全数据库恢复文件恢复
SQL> recover database until cancel;
完成介质恢复。
SQL> alter database open resetlogs;
数据库已更改。
数据库控制文件的
select * from v$controlfile;
查看数据库的信息
select * from v$database;
包括数据库创建的时间数据库的名称，数据CHECKPOINT#
控制文件之间是镜像的关系，建议放在不同的物理磁盘。
所有修改的数据库结构的命令都会引起控制文件的改变，数据库管理命令都记录在跟踪文件中。F:\oracle\product\10.2.0\admin\yu10\bdump\alert_sid.log
这个文件的位置是 在参数文件中定义的
由参数 background_dump_dest
user_dump_dest两个参数确定
数据库的逻辑结构
数据块 区 段 表空间 数据库
select tablespace_name,contents,initial_extent,next_extent,min_extents,max_extents
from dba_tablespaces;
查表空间
select tablespace_name,contents,initial_extent,next_extent,min_extents,max_extents
2 from dba_tablespaces;
TABLESPACE_NAME CONTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
------------------------------ --------- -------------- ----------- ----------- -----------
SYSTEM PERMANENT 65536 1 2147483645
UNDOTBS1 UNDO 65536 1 2147483645
SYSAUX PERMANENT 65536 1 2147483645
TEMP TEMPORARY 1048576 1048576 1
USERS PERMANENT 65536 1 2147483645
EXAMPLE PERMANENT 65536 1 2147483645
SALES PERMANENT 65536 1 2147483645
查看表空间文件
SQL> select file_id,bytes,maxbytes,tablespace_name,file_name from dba_data_files;
FILE_ID BYTES MAXBYTES TABLESPACE_NAME FILE_NAME
---------- ---------- ---------- ------------------------------ --------------------------------------------------------------------------------
4 104857600 0 USERS F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\USERS01.DBF
3 304087040 3435972198 SYSAUX F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SYSAUX01.DBF
2 36700160 3435972198 UNDOTBS1 F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\UNDOTBS01.DBF
1 513802240 3435972198 SYSTEM F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SYSTEM01.DBF
5 104857600 3435972198 EXAMPLE F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\EXAMPLE01.DBF
6 52428800 104857600 SALES F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SALES01.DBF
7 52428800 0 SALES F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SALES02.DBF
8 52428800 3435972198 SALES F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SALES03.DBF
9 52428800 104857600 SALES F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SALES04.DBF
9 rows selected
剩余表空间
SQL> select tablespace_name,sum(bytes),sum(blocks)
2 from dba_free_space
3 group by tablespace_name;
TABLESPACE_NAME SUM(BYTES) SUM(BLOCKS)
------------------------------ ---------- -----------
UNDOTBS1 24641536 3008
SYSAUX 8716288 1064
USERS 93192192 11376
SYSTEM 3407872 416
EXAMPLE 23396352 2856
SALES 209453056 25568
6 rows selected
可以查询数据字典dba_data_files,dba_free_space 联合一起查询，通过计算空闲空间的百分比
select b.file_id 文件id号,
b.tablespace_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.file_id, b.tablespace_name, b.bytes
order by b.file_id
表空间的属性 ONLINE、 OFFLINE、 READ ONLY 、READ WRITE
ALTER TABLESPACE USERS OFFLINE;
CONN SCOTT/TIGER;
CREATE TABLE TEST001 (ID NUMBER(7));
会出现如下错误
CREATE TABLE TEST001 (ID NUMBER(7))
*
第 1 行出现错误:
ORA-01542: 表空间 'USERS' 脱机, 无法在其中分配空间
conn sys/oracle as sysdba;
ALTER TABLESPACE USERS ONLINE;
ALTER TABLESPACE USERS READ ONLY;
conn scott/tiger;
SQL> CREATE TABLE TEST001 (ID NUMBER(7));
CREATE TABLE TEST001 (ID NUMBER(7))
*
第 1 行出现错误:
ORA-01647: 表空间 'USERS' 是只读, 无法在其中分配空间
conn sys/oracle as sysdba;
ALTER TABLESPACE USERS ONLINE;
ALTER TABLESPACE USERS READ WRITE;
可以通过下面的方式查看的 只读 或者 可写
conn sys/oracle as sysdba;
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
SALES ONLINE
已选择7行。
通过下面的方式看 ONLINE 或者 OFFLINE
SQL> select file#,checkpoint_change#,status,name from v$datafile
2 /
FILE# CHECKPOINT_CHANGE# STATUS NAME
---------- ------------------ ------- --------------------------------------------------------------------------------
1 1469186 SYSTEM F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SYSTEM01.DBF
2 1469186 ONLINE F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\UNDOTBS01.DBF
3 1469186 ONLINE F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SYSAUX01.DBF
4 1496443 ONLINE F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\USERS01.DBF
5 1469186 ONLINE F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\EXAMPLE01.DBF
6 1469186 ONLINE F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SALES01.DBF
7 1469186 ONLINE F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SALES02.DBF
8 1469186 ONLINE F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SALES03.DBF
9 1469186 ONLINE F:\ORACLE\PRODUCT\10.2.0\ORADATA\YU10\SALES04.DBF
9 rows selected
1 数据段
Select username,defult_tablespace from dba_users;
每个表对应一个数据段，可以通过user_extents数据字典来查询
Select segment_name,tablespace_name,bytes,blocks from user_extents;
SQL> Select segment_name,tablespace_name,bytes,blocks from user_extents;
SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS
-------------------------------------------------------------------------------- ------------------------------ ---------- ----------
DEPT USERS 65536 8
EMP USERS 65536 8
BONUS USERS 65536 8
SALGRADE USERS 65536 8
PK_DEPT USERS 65536 8
PK_EMP USERS 65536 8
6 rows selected
Segment_name 为数据段名称
2.索引段 用于存储索引数据
Select index_name,table_owner,table_name,tablespace_name from all_indexes;
SQL> Select index_name,table_owner,table_name,tablespace_name from all_indexes;
INDEX_NAME TABLE_OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
PK_EMP SCOTT EMP USERS
PK_DEPT SCOTT DEPT USERS
用下面的程序查询索引的名称
select user_indexes.table_name,user_indexes.index_name,uniqueness,column_name
From user_ind_columns,user_indexes
Where user_ind_columns.index_name=user_indexes.index_name
And
user_ind_columns.table_name=user_indexes.table_name
Order by 1,2,3,4
TABLE_NAME INDEX_NAME UNIQUENESS COLUMN_NAME
------------------------------ ------------------------------ ---------- --------------------------------------------------------------------------------
DEPT PK_DEPT UNIQUE DEPTNO
EMP PK_EMP UNIQUE EMPNO
3.回滚段
oracle 10g 数据库自动管理回滚段的，他取消了回滚段的概念（但是还保留了可以使用回退段，为了数据库版本的兼容性），素有事务需要所啊多少回退段，每个回退段的大小，回退段的空间利用率，竞争，读一致性等可以由ORACLE 10G 自动管理，
用下面的程序检查数据库是用的回退段还是UNDO 表空间
SQL> show parameter undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
undo_management string AUTO
也可以在数据字典中查询
SQL> select value from v$parameter where name='undo_management';
VALUE
-----------------------------------------------------------------------------
AUTO
如果要继续使用数据回滚段 可以将ORACLE 10G 中的undo_management 设置成为手动manual方式重新启动数据库，这样就启动了oracle10g对回滚段的支持
查询回滚段的信息
select segment_name,owner,tablespace_name from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME
------------------------------ ------ ------------------------------
SYSTEM SYS SYSTEM
_SYSSMU1$ PUBLIC UNDOTBS1
_SYSSMU2$ PUBLIC UNDOTBS1
_SYSSMU3$ PUBLIC UNDOTBS1
_SYSSMU4$ PUBLIC UNDOTBS1
_SYSSMU5$ PUBLIC UNDOTBS1
_SYSSMU6$ PUBLIC UNDOTBS1
_SYSSMU7$ PUBLIC UNDOTBS1
_SYSSMU8$ PUBLIC UNDOTBS1
_SYSSMU9$ PUBLIC UNDOTBS1
_SYSSMU10$ PUBLIC UNDOTBS1
4 临时段 用户在使用ORDER BY 语句时候进行排序，汇总时，在用户的临时表空间TEMP中会自动创建一个临时段,用完临时段消除。
select username,temporary_tablespace from dba_users;
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
SB TEMP
SCOTT TEMP
OUTLN TEMP
MDSYS TEMP
ORDSYS TEMP
EXFSYS TEMP
DMSYS TEMP
WMSYS TEMP
区
select tablespace_name,contents,initial_extent,next_extent,min_extents,max_extents from dba_tablespaces;
select tablespace_name,contents,initial_extent,next_extent,min_extents,max_extents from dba_tablespaces;
TABLESPACE_NAME CONTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS
------------------------------ --------- -------------- ----------- ----------- -----------
SYSTEM PERMANENT 65536 1 2147483645
UNDOTBS1 UNDO 65536 1 2147483645
SYSAUX PERMANENT 65536 1 2147483645
TEMP TEMPORARY 1048576 1048576 1
USERS PERMANENT 65536 1 2147483645
EXAMPLE PERMANENT 65536 1 2147483645
SALES PERMANENT 65536 1 2147483645
通过user_tables 查询用户所创建的表的存储空间大小和存储方法
查询数据段中 区的存储方法
select initial_extent,next_extent,min_extents,max_extents,pct_increase from user_tables
where table_name=’EMP’;
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ----------- ------------
65536 1 2147483645
查询区段的分配状况
select segment_name table_name,extent_id,bytes,blocks from user_extents where segment_name='EMP'
TABLE_NAME EXTENT_ID BYTES BLOCKS
-------------------------------------------------------------------------------- ---------- ---------- ----------
EMP 0 65536 8
EXTENT_ID 是指分配次数 bytes为每次分配的空间的大小 blocks为每次分配的数据块数。
数据块
DB_BLOCK_SIZE 这个参数决定决定数据块的大小
数据块是数据库中最小的数据组织单位和管理单位，是数据文件磁盘存储空间单位。
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xuejiayue1105/archive/2008/08/23/2816883.aspx
