复习ORACLE 数据库DBA操作日志(不停更新.........)

查询剩余的表空间
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

====================================================================
====================================================================
== 以下部分为8月23日复习记录
====================================================================
====================================================================
查看数据库日志文件
select * from v$log
group#日志文件组号

thread#文件线程号（一般为1，双机容错为2)

sequence# 日志序列号 日志切换后自动加1

bytes 日志文件大小 50M 是默认的值

members 改组日志成员个数

ARCHIVED 时候 已经完成归档

STATUS 改组状态 是否在可用状态

FIRST_CHANGE#系统更改号（SCN) 也叫检查点号

FIRST_TIME 系统改变时间

GROUP#THREAD#SEQUENCE#BYTESMEMBERSARCHIVEDSTATUSFIRST_CHANGE#FIRST_TIME
1123 524288001NOINACTIVE12672522008-8-23 2:25:17
2124 524288001NOCURRENT13073602008-8-23 14:07:20
3122 524288001NOINACTIVE12309842008-8-22 17:00:15

查看日志文件
select * from v$logfile

日志强制切换
alter system switch logfile;

切换后结果如下
GROUP#THREAD#SEQUENCE#BYTESMEMBERSARCHIVEDSTATUSFIRST_CHANGE#FIRST_TIME
1123524288001NOINACTIVE12672522008-8-23 2:25:17
2124524288001NOACTIVE13073602008-8-23 14:07:20
3125524288001NOCURRENT13377842008-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;

DBIDNAMECREATEDLOG_MODE
2154395220YU102008-7-30 11:28:20NOARCHIVELOG

也可以通过服务器端直接看

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;

结果归档日志结果如下
RECIDSTAMPTHREAD#SEQUENCE#NAME
1663552055125F:\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

====================================================================
====================================================================
== 以下部分为8月24日复习记录
====================================================================
====================================================================

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标识的成员不能删除，这个表示是 新增的成员，还没有使用，也不能删除。

===================================================================
====================================================================
== 以下部分为8月25日复习记录
====================================================================
====================================================================

移动日志文件成员
先做物理文件的移动
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

===================================================================
====================================================================
== 以下部分为8月26日复习记录
====================================================================
====================================================================

段

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 这个参数决定决定数据块的大小
数据块是数据库中最小的数据组织单位和管理单位，是数据文件磁盘存储空间单位。