--数据库实例是：
system global area ---SGA
program global area---PGA
--而数据库是：
SQL> ho ls $ORACLE_BASE/oradata/lijh
control01.ctl ii.dbf redo02.log system01.dbf users01.dbf
control02.ctl perfstat.dbf redo03.log temp01.dbf users02.dbf
control03.ctl redo01.log sysaux01.dbf undotbs01.dbf1、数据库启动与关闭步骤刚好相反
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1220772 bytes
Variable Size 205524828 bytes
Database Buffers 385875968 bytes
Redo Buffers 11358208 bytes
Database mounted.
Database opened.
--数据库启动三部曲：
startup nomount ---> instance ---读初始化参数文件($ORACLE_HOME/dbs/spfilesid.ora),分配内存
alter database mount; ---->挂载数据库 读controlfile .ctl
alter database open; ---->打开数据库 read write
1.SQL> startup nomount
ORACLE instance started.
Total System Global Area 603979776 bytes
Fixed Size 1220772 bytes
Variable Size 209719132 bytes
Database Buffers 381681664 bytes
Redo Buffers 11358208 bytes
2.SQL> alter database mount;
Database altered.
3.SQL> alter database open;
Database altered.
2、创建和管理初始化参数文件
pfile 8i 和以前更早版本，只有init.ora初始化参数文件（文本文件) 可以vi 不适合7*24
spfile 9i才有的，rman 可以备份 能够自我调节 一般存在服务器上 不能vi
路径：
SQL> ho ls $ORACLE_HOME/dbs
hc_clone.dat hc_tech.dat lkLIJH orapwlijh spfileclone.ora
hc_lijh.dat init.ora lkPOD orapwpod spfilelijh.ora
hc_ocm.dat initdw.ora lkTECH orapwtech spfilepod.ora
hc_pod.dat lkCLONE orapwclone snapcf_lijh.f spfiletech.ora
--修改spfile中的参数：
alter system set db_cache_size=100m scope=memory ;--->临时的内存里生效，下次重启数据库恢复原值
alter system set db_cache_size=100m scope=spfile;--->下次重启数据库才生效
alter system set db_cache_size=100m scope=both(默认）---->永久生效
SQL> alter system set db_cache_size=100m scope=memory;
SQL> show parameter db_cache
SQL> startup force
SQL> show parameter db_cache
--pfile与spfile相互创建：
create pfile='...' from spfile;
create spfile from pfile;
--若将spfile文件中的参数修改错误数据库启不来，解决：
用以前备份的pfile文件启动再重新创建spfile，然后重启
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initpod.ora';
SQL> alter database mount;
SQL> alter database open;
SQL> create spfile from pfile;
SQL> startup force
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10g/db
s/spfilepod.ora
--关闭数据库：
A=abort --->不准许新的连接，不等待当前session结束，不等待当前事务结束，不做强制检查点
I=immediate --->不准许新的连接，不等待当前session结束，不等待当前事务结束，强制检查点并关闭文件
T=transaction ---->不准许新的连接，不等待当前session结束，等待当前事务结束，强制检查点并关闭文件
N=normal --->不准许新的连接，等待当前session结束，等待当前事务结束，强制检查点并关闭文件
干净又快速的关闭数据库 方法： shutdown immediate
3、诊断文件
altert_sid.log
bdump
udump
cdump
<alert.log>
记录命令
记录主要事件的结果
用于日常操作信息
用于诊断数据库错误
dba来管理
<后台跟踪文件>
记录后台进程检测到的错误
诊断和解决错误
位置： background_dump_dest
SQL> show parameter back%dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /u01/app/oracle/admin/pod/bdump
$ORACLE_BASE/admin/sid/bdump/alert_sid.log -->路径
<用户跟踪进程>
由用户进程产生
包含sql信息
用户错误
SQL> show parameter user%dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /u01/app/oracle/admin/pod/udump
$ORACLE_BASE/admin/pod/udump/ -->路径
SQL> show parameter max_dump%
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string UNLIMITED ----默认的
SQL> alter system set max_dump_file_size=800; ---->800个 operating system block
SQL> alter system set max_dump_file_size='800m';
SQL> show parameter max_dump%
SQL> alter system set max_dump_file_size=unlimited;
4、控制文件
一个二进制文件
定义当前数据库物理状态
丢失需要恢复
维护数据库一致性
控制文件内容：
数据库名字和标识
表空间名字
数据文件和联机日志文件的物理位置和大小
检查点信息
归档信息
等....
控制文件默认有三个镜像，丢失任何一个丢要恢复，为避免介质故障，将它们放到不同的磁盘
镜像控制文件步骤：
SQL> alter system set control_files='/u01/app/oracle/oradata/pod/control01.ctl',
2 '/u01/app/oracle/oradata/pod/control02.ctl',
3 '/u01/app/oracle/oradata/pod/control03.ctl',
4 '/u01/app/oracle/oradata/control04.ctl' scope=spfile;
SQL> shutdown immediate
cp control*.ctl ...... 拷贝到不同的磁盘
SQL> startup
SQL> show parameter control_fil
<获得控制文件>
v$controlfile
v$parameter
show parameter control_files
SQL> show parameter control_fil
SQL> select name from v$controlfile;
SQL> alter database backup controlfile to trace ; --->udump
SQL> alter database backup controlfile to trace as '$ORACLE_BASE/aaa.txt'; -->备份出来的文件里面是脚本，有两段，运行set#1 可noresetlog重建控制文件
SQL> alter database backup controlfile to '/u01/app/oracle/bak/con.bak';---二进制备份文件
SQL> ho more /u01/app/oracle/bak/con.bak
5、表空间和数据文件
--表空间：
查看表空间： select tablespace_name from dba_tablespaces;
不可或缺的几个表空间：
system -> 系统、字典表空间，不能被损坏
undo -> 回滚表空间，DML 把数据快照放到此，数据提交即消失
sysaux -> 10g新增 高并发系统繁忙时造成system争用,将工具放到sysaux,减轻system 的压力,sysaux损坏不影响系统
temp -> 临时表空间，临时数据相关的内容
users -> 10g新增 用户数据从system拨离出
创建和删除表空间：
create tablespace d3 datafile 'xx.dbf' size 20m autoextend on next 1m maxsize 2g;
drop tablespace d3;
create temporary tablespace temp1 tempfile 'xx.dbf' size 100m;
drop tablespace temp1;
create undo tablespace undo1 datafile 'xx.dbf' size 445m;
10g ->新增加功能(区)extent management local (段)segment space managment auto
10gr2 ->默认为extent management local segment space managment auto
create tablespace d4 datafile 'xx.dbf' size 10m autoextend on next 1m maxsize unlimited
extent management local segment space management manual ;
查看：select tablespace_name,extent_management,segment_space_management from dba_tablespaces;
tablespace 状态--> online,offline | read only,read write(online时)
alter tablespace users offline; -->system,undo 没有offline
alter tablespace users online;
alter tablespace users read only; -->DQL DDL ---NOT DML
alter tablespace users read write;
查看状态：select file#,status,enabled from v$datafile;
alter tablespace d4 add datafile '/d402.dbf' size 30m; ->添加dbf
alter tablespace d4 drop datafile 11; ->可以删除为空的dbf,不能删除仅有的dbf,不能删除第一个dbf
alter tablespace d4 rename to d5; ->重命名
alter tablespace temp tablespace group tmp; -> 加入临时表空间组
alter tablespace undo retention guarantee; -> 活动事务在undo存放时间必须到undo_retention指定时间才踢出
改默认表空间：
alter database default tablespace users;
alter database default temporary tablespace tmp;
查看默认(临时)表空间:
select property_name,property_value from database_properties where property_name in
('DEFAULT_TEMP_TABLESPACE','DEFAULT_PERMANENT_TABLESPACE');
select username,default_tablespace,temporary_tablespace from dba_users;
表空间的配额限制：
alter user uu1 quota 200m on users;
alter user uu1 quota 100m on system;
========================================================================================
--datafile： v$datafile dba_data_files
查看dbf：select file_id,file_name,tablespace_name ,bytes from dba_data_files;
对dbf的操作：
alter database datafile 'xx.dbf' autoextend on next 1m maxsize 1g;
alter database datafile 'xx.dbf' autoextend off; -> 自动增长关闭
alter database datafile 'xx.dbf' resize 20m; -> 修改dbf大小
alter database datafile 4 offline; -->不要轻易offline,除非是offline drop
alter database datafile 4 offline drop;
alter database rename file '' to ''; -> 重命名(步骤应是：表空间offline ,拷贝dbf ,alter ,表空间online)
--tempfile： v$tempfile dba_temp_files
temp ->临时段数据，排序数据
PGA ->排序默认在内存PGA中，如内存不足，then temp tablespace ----disk i/o
查看： select file_name,tablespace_name from dba_temp_files;
创建临时表空间：
create temporary tablespace temp1 tempfile 'xx.dbf' size 100m;
create temporary tablespace temp2 tempfile 'xx.dbf' size 100m tablespace group tmp;
drop tablespace temp;
alter tablespace temp1 tablespace group tmp; --> 10g ->temp group
alter database default temporary tablespace temp2;
--UNDO:
参数：undo_retention ----事务在undo保留时间上限默认900秒
参数：undo_tablespace ----当前的回滚表空间默认undotbs1
create undo tablespace undo1 datafile 'xx.dbf' size 200m;
alter system set undo_tablespace=undo1;
alter system set undo_retention=10800;
alter tablespace undo1 retention guarantee; -> 活动事务在undo存放时间必须到undo_retention指定时间才踢出
select tablespace_name,retention from dba_tablespaces where tablespace_name='UNDO1';
select name from v$rollname; ----undo segment 自动默认分配10个
alter rollback segment "_SYSSMU19{1}quot; offline;
alter rollback segment "_SYSSMU19{1}quot; online;
delete from tt1; ->DML
查看当前回滚段表空间里是否有活动的事物:
select s.username,t.ubafil,t.ubablk from v$session s,v$transaction t where s.saddr=t.ses_addr; ->查出记录
rollback;
select s.username,t.ubafil,t.ubablk from v$session s,v$transaction t where s.saddr=t.ses_addr; ->无记录SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
LIU ONLINE --online时的显示
已选择6行。
SQL> select file#,ts#,status,enabled from v$datafile;
FILE# TS# STATUS ENABLED
---------- ---------- ------- ----------
1 0 SYSTEM READ WRITE
2 1 ONLINE READ WRITE
3 2 ONLINE READ WRITE
4 4 ONLINE READ WRITE
5 6 ONLINE READ WRITE --online时的显示
6 6 ONLINE READ WRITE --online时的显示
已选择6行。
SQL> alter tablespace liu offline;
表空间已更改。
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
LIU OFFLINE --offline时的显示
已选择6行。
SQL> select file#,ts#,status,enabled from v$datafile;
FILE# TS# STATUS ENABLED
---------- ---------- ------- ----------
1 0 SYSTEM READ WRITE
2 1 ONLINE READ WRITE
3 2 ONLINE READ WRITE
4 4 ONLINE READ WRITE
5 6 OFFLINE DISABLED --offline时的显示
6 6 OFFLINE DISABLED --offline时的显示
已选择6行。
SQL> alter tablespace liu read only; --online时才能将表空间read only｜read write
alter tablespace liu read only
*
第 1 行出现错误:
ORA-01539: 表空间 'LIU' 未联机
SQL> alter tablespace liu online;
表空间已更改。
SQL> alter tablespace liu read only;
表空间已更改。
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
LIU READ ONLY --read only时的显示
已选择6行。
SQL> select file#,ts#,status,enabled from v$datafile;
FILE# TS# STATUS ENABLED
---------- ---------- ------- ----------
1 0 SYSTEM READ WRITE
2 1 ONLINE READ WRITE
3 2 ONLINE READ WRITE
4 4 ONLINE READ WRITE
5 6 ONLINE READ ONLY --read only时的显示
6 6 ONLINE READ ONLY --read only时的显示
已选择6行。
SQL> alter tablespace liu read write;
表空间已更改。
SQL> alter database datafile 6 offline;
数据库已更改。
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
LIU ONLINE --数据文件offline，但表空间online
已选择6行。
SQL> select file#,ts#,status,enabled from v$datafile;
FILE# TS# STATUS ENABLED
---------- ---------- ------- ----------
1 0 SYSTEM READ WRITE
2 1 ONLINE READ WRITE
3 2 ONLINE READ WRITE
4 4 ONLINE READ WRITE
5 6 ONLINE READ WRITE
6 6 RECOVER READ WRITE --数据文件offline,状态变成recover,这里可以做恢复
已选择6行。
SQL> alter database datafile 6 online; --无法online,必须先做recover再online
alter database datafile 6 online
*
第 1 行出现错误:
ORA-01113: 文件 6 需要介质恢复
ORA-01110: 数据文件 6: 'F:\APP\ADMINISTRATOR\ORADATA\OCM\LIU02.DBF'
SQL> recover datafile 6; --recover数据文件
完成介质恢复。
SQL> select file#,ts#,status,enabled from v$datafile;
FILE# TS# STATUS ENABLED
---------- ---------- ------- ----------
1 0 SYSTEM READ WRITE
2 1 ONLINE READ WRITE
3 2 ONLINE READ WRITE
4 4 ONLINE READ WRITE
5 6 ONLINE READ WRITE
6 6 OFFLINE READ WRITE --recover完，数据文件状态变成offline
已选择6行。
SQL> alter database datafile 6 online; --将数据文件online
数据库已更改。
SQL> select file#,ts#,status,enabled from v$datafile;
FILE# TS# STATUS ENABLED
---------- ---------- ------- ----------
1 0 SYSTEM READ WRITE
2 1 ONLINE READ WRITE
3 2 ONLINE READ WRITE
4 4 ONLINE READ WRITE
5 6 ONLINE READ WRITE
6 6 ONLINE READ WRITE
已选择6行。
6、重做日志文件
联机重做日志文件：
记录所有对数据的改变
提供恢复机制
能够分组
至少需要2个日志组
联机重做日志文件的结构：
group 1 group 2 group 3
member member member ----->disk 1
member member member ----->disk 2
SQL> col member format a40;
SQL> select GROUP# ,MEMBER from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------
3 /u01/app/oracle/oradata/pod/redo03.log
2 /u01/app/oracle/oradata/pod/redo02.log
1 /u01/app/oracle/oradata/pod/redo01.log
循环使用的
当一个联机日志文件被写满了，lgwr将移到下一个联机日志文件组
被称为日志切换
同时发生一个检查点
信息写到控制文件
强行日志切换：
alter system switch logfile;
SQL> select GROUP# ,STATUS from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
SQL> alter system switch logfile;
SQL> select GROUP# ,STATUS from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 CURRENT
STATUS:
current ---->当前正在写的
ACTIVE---->激活状态 （ckpt正在读它... 归档模式，arcn正在读它....)
INACTIVe---->非激活状态
增加日志文件组成员：
SQL> alter database add logfile member
2 '/u01/app/oracle/oradata/disk1/redo0102.log' to group 1,
3 '/u01/app/oracle/oradata/disk1/redo0202.log' to group 2,
4 '/u01/app/oracle/oradata/disk1/redo0302.log' to group 3;
增加日志组：
SQL> alter database add logfile group 4
2 ('/u01/app/oracle/oradata/pod/redo0401.log',
3 '/u01/app/oracle/oradata/disk1/redo0402.log') size 50m;
删除联机重做日志文件组：
SQL> alter database drop logfile group 4; ->当前正在写的日志组，不可以删除
可以查询的视图：
v$log
v$logfile
v$log_history
7、归档日志
写满了的联机重做日志文件可以被归档
归档方式的优势：
恢复： 有联机日志文件和归档文件可以恢复所有提交的事务
备份：可以联机状态下备份
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
%s----->序列号
%r----->resetlogs
SQL> show parameter db_rec --闪回区路径和大小，默认2g
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
--将数据库改为归档模式：
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled <--
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Current log sequence 32
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled <--
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 30
Next log sequence to archive 32
Current log sequence 32
SQL> alter database open;
# ps-ef |grep ora_arc
oracle 26772 1 0 10:26 ? 00:00:00 ora_arc0_pod ----归档进程
oracle 26774 1 0 10:26 ? 00:00:00 ora_arc1_pod
oracle 26777 1 0 10:26 ? 00:00:00 ora_arc2_pod
--默认不指定归档路径，那么将归档文件放在闪回区域
生产环境，我们一定要将归档路径指定到独立的一块盘
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog/pod/';
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=/u01/app/oracle/archivelog/pod/
log_archive_dest_10 string
[oracle@emrep pod]$ ll
total 632
-rw-r----- 1 oracle oinstall 463360 Aug 10 10:32 1_35_726313888.dbf
-rw-r----- 1 oracle oinstall 141824 Aug 10 10:32 1_36_726313888.dbf
-rw-r----- 1 oracle oinstall 27136 Aug 10 10:32 1_37_726313888.dbf
[oracle@emrep pod]$ pwd
/u01/app/oracle/archivelog/pod
--默认分配log_archive_max_processes 指定的个数
SQL> show parameter log_arch%max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 2
SQL> alter system set log_archive_max_processes=4;
SQL> show parameter log_arch%max
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes integer 4
# ps -ef|grep ora_arc
oracle 26772 1 0 10:26 ? 00:00:00 ora_arc0_pod
oracle 26774 1 0 10:26 ? 00:00:00 ora_arc1_pod
oracle 27716 1 0 10:37 ? 00:00:00 ora_arc2_pod
oracle 27718 1 0 10:37 ? 00:00:00 ora_arc3_pod
--默认分配log_archive_max_processes 指定的个数，但是在业务高峰期，切换非常频繁情况下，lgwr进程会自动启动多个归档进程，最多30个
8、日志挖掘
oracle 10g 使用logmnr在线分析挖掘日志，使用当前在线的数据字典
--1.查找当前日志
SQL> select * from v$log where status='CURRENT';
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
2 1 39 52428800 2 NO CURRENT
1009280 10-AUG-10
--2.查看日志成员
SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP#=2;
MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/pod/redo02.log
/u01/app/oracle/oradata/disk1/redo0202.lo
--3.执行一些操作：
SQL> CREATE table fff as select * from dba_users;
--4.添加日志并分析
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/pod/redo02.log',dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
--5.查看分析结果
SQL> desc v$logmnr_contents
SQL> select sql_redo,TIMESTAMP ,username from v$logmnr_contents where sql_redo like '%CREATE table fff as select * from dba_users%';
SQL_REDO
--------------------------------------------------------------------------------
TIMESTAMP USERNAME
------------------- ------------------------------
CREATE table fff as select * from dba_users;
2010-08-10 11:03:02
--6.结束日志挖掘
SQL> exec dbms_logmnr.end_logmnr
SQL> select sql_redo,TIMESTAMP ,SESSION#, TABLE_NAME from v$logmnr_contents ; ->再访问v$logmnr_contents报错
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from
v$logmnr_contents
9、db link
link 是一个数据库对象，创建此对象的用户需要是dba或者拥有create database link权限
--权限：select role,privileg from role_sys_privs where privileg='CREATE DATABASE LINK';
--创建link:
grant create database link to tom; -> 授权
conn tom/tom -> 连接
create database link ycwl connect to scott identified by tiger using 'cat'; --> 前提tnsping cat能通
insert into e1@ycwl values(100); -> 使用link访问cat库.DQL,DML. --不要作DDL操作
--查询link:
select owner,db_link,host,username from dba_db_links;
--全局link :
必须是dba才能创建public database link，所有用户都能使用全局link连接
create public database link lg connect to scott identified by tiger using 'cat';
conn scott/tiger
select * from e1@lg;
--删除link:
drop database link link_name;
drop public database link link_name;
10、审计
参数：audit_file_dest 决定路径 默认 /u01/app/oracle/admin/pod/adump
参数：audit_trail 决定追踪目标 默认 NONE
--audit_trail 参数值：
NONE ： 默认值 不做审计
DB： 将审计记录在相关表中，如aud$,审计的结果只有连接信息
DB,EXTENDED： 这样审计结果里面除了连接信息还有当时执行的具体语句
OS：将审计在操作系统文件中，audit_file_dest参数指定
--激活审计：
alter system set audit_trail=db,extended scope=spfile;
startup force
--开始审计：
audit all on scott.emp by access;
audit select,insert,delete on scott.emp by access whenever successful;
by access : 每个被审计的操作都会生成一条audit trail
by session : 一个会话里面同类型的操作只生成一条记录，默认为by session
(同一个会话，insert ,update,delete,select 为同一类型操作，只会记录一条)
whenever successful ： 操作成功才审计
whenever not successful： 反之，操作不成功才审计
默认，不管操作成功与否都记录
--查看：
select username,obj_name,sql_text from dba_audit_trail;
--撤销审计：
noaudit all on scott.emp;
--查看哪些对象开启了审计：
desc dba_obj_audit_opts
--清理审计信息：
delete from aud$; ->dba_audit_trail自动清楚记录
11、外部表
extenal table： 外部表，将表放在指定路径而非表空间
创建路径：
create directory lijh as '/u01/app/oracle/extenalbak/';
grant all on directory lijh to tom;
创建外部表：
create table test11
organization external
(
type oracle_datapump ->文件类型 dmp
default directory lijh ->路径
location ('test11.dmp') ->文件名称,不用再写路径了
)
as select * from scott.dept; ->结构, 或者在第一行指定表结构
txt文件内容导入外部表：
create table xx (a number,b varchar2(20),c varchar2(20))
organization external
(
type oracle_loader ->文件类型 loader
default directory dir ->路径
access parameters
(records delimited by newline ->以换行作为一条记录结束
fields terminated by ',' ->以 逗号 作为分隔，空格作为分隔的话要写 whitespace
missing field values are null ->没有的值设为空
(a,b,c))
location('xx.txt') ->文件名称
);
--将alert日志建成外部表
create table alertlog(text varchar2(999))
organization external
(type oracle_loader
default directory bdumpdir
access parameters
(records delimited by newline
fields
reject rows with all null fields)
location('alert_ocm.log')
);
--查看alert日志
select * from (select lag(text,1) over(order by rownum) s1,text from alertlog) where text like '%&keyword%';
例如输入 to log,可看每组日志的first time
12、内置的数据库对象
在数据库中创建的其他对象:
数据字典
性能表
PL/SQL包
数据库事件触发器
<数据字典>
每个Oracle数据库的中枢，描述数据库和它的对象，由Oracle服务器自己维护
包含只读的表和视图，存储在SYSTEM表空间中，拥有者是SYS用户
数据字典包括两个部分:
基础表：存储数据库的描述，create database命令创建（sql.bsq)
数据字典视图：用于简化基础表的信息，通过public同义词访问，由脚本catalog.sql创建
创建数据字典视图:
@?/rdbms/admin/catalog.sql ----> 创建常用的数据字典视图和同义词
@?/rdbms/admin/catproc.sql ---->生成要运行脚本所需要的PL/SQL
数据字典的内容，提供下列信息:
逻辑和物理的数据库结构，对象的定义和空间分配，一致性限制，用户，角色，权限，审计
数据字典的主要用途:
Oracle服务器用它查找下列信息：用户，对象，存储结构
Oracle服务器修改数据字典当DDL语句执行的时候.
用户和管理员们利用它了解数据库的信息.
三类静态视图：dba_(包含所有的对象), all_(当前用户能够访问的对象), user_(当前用户拥有的对象)
一般的概况: dictionary, dict_columns
对象: dba_tables, dba_indexes, dba_tab_columns, dba_constraints --->user_ ,all_
空间分配: dba_segments, dba_extents
数据库结构: dba_tablespaces, dba_data_files
<动态性能表>
虚表，记录当前数据库的活动，不停的更新，用于监控和调整数据库
所有者是SYS用户，以v$开头，在v$fixed_table有全部的动态性能表
v$controlfile
v$database
v$datafile
v$instance
v$parameter
v$session
v$sga
v$spparameter
v$tablespace
v$thread
v$version
关于版本
v$version
v$option
13、分区表
分区表：
将大表分成几个较小的部分，分别存储在不同表空间，其中每个小部分都可以看成是单独的表。三种：range hash list
--一个table 只能有一个tablespace,一个tablespace可有多个dbf,在不同的磁盘hash存放，只要有一盘挂掉，table不能访问
而分区表挂了某一块盘，其它盘仍然可以访问
range
按范围建分区表
create table trans(id int,trans_dt date)
partition by range(trans_dt)
(partiton p1 values less than (to_date('2009-07-01','yyyy-mm-dd')) tablespace users,
partiton p2 values less than (to_date('2010-01-01','yyyy-mm-dd')) tablespace users,
partiton p3 values less than (to_date('2010-07-01','yyyy-mm-dd')) tablespace d2,
partiton p4 values less than (to_date('2011-01-01','yyyy-mm-dd')) tablespace tools
);
增加一个区，设定无穷大值：
alter table trans add partition pmax values less than(maxvalue) tablespace system;
访问分区表的某个区：
select * from r2 partition(p3);
获得分区表信息：
desc dba_part_tables ->subpartitioning_type
desc dba_tab_partitions ->high_value
hash
以hash建分区表，目的纯粹是为减轻磁盘负担：
create table r2 (a int,b varchar2(80))
partition by hash(a)
(partition p1 tablespace users,
partition p2 tablespace system
);
增加一个分区：
alter table r2 add partition p3 tablespace ii;
list
按某个值建分区表：
create table r3 (city varchar2(80),id int)
partition by list(city)
(partition p1 values('SZ','GZ'),
partition p2 values('BJ','SH')
);
增加一个分区，设置默认在哪个区：
alter table r3 add partition p3 values(default);
分裂分区表：
alter table t_part_range split partition p2
at (to_date('2009/10','yyyy/mm')) into (partition p2,partition p4);
14、版本升级
版本升级(分软件和数据库)： 10.2.0.1 -> 10.2.0.4
1. 要求：
system表空间 >10m
shared_pool_size >150m ->暂时先把sga_target调为0,内存即不再自动调配了，再set 参数
java_pool_size >150m
~~~~~这个脚本检查 system free~~~~~
set line 110
col f.tablespace_name format a15
col d.tot_grootte_mb format a10
col ts-per format a15
select upper(f.tablespace_name) "ts-name",
d.tot_grootte_mb "ts-bytes(m)",
d.tot_grootte_mb - f.total_bytes "ts-used (m)",
f.total_bytes "ts-free(m)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,
2),
'990.99') "ts-per"
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 5 desc;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-------或用这个脚本检查 system free-----
select a.tablespace_name, a.total, a.total-f.free use, f.free, round(1-f.free/a.total,2) "USE%"
from
(select tablespace_name,sum(bytes/1048576) total from dba_data_files group by tablespace_name) a,
(select tablespace_name,round(sum(bytes/1048576)) free from dba_free_space group by tablespace_name) f
where a.tablespace_name=f.tablespace_name(+)
order by "USE%" desc;
--------------------------------------
select file_name,bytes/1024/1024,tablespace_name from dba_data_files;
alter database datafile '/oracle/oradata/red/system01.dbf' resize 800m;
show parameter db_cache
show parameter shared_pool_size
show parameter java_pool_size
alter system set sga_target=0; ---->内存不再自动调配了
alter system set db_cache_size=100m;
alter system set java_pool_size=150m;
alter system set shared_pool_size=150m;
2.停库，全备份
3.升级软件
./runinstall -> oracle
root.sh -> root
4.更新字典，(每个库都要做)
startup upgrade
@?/rdbms/admin/catupgrd.sql
5.重编译失效对象：
startup force
@?/rdbms/admin/utlrp.sql
select comp_name,version,status from sys.dba_registry; -->升级后的检测
select * from utl_recomp_errors; ->看有无失效对象
6.重启库：
startup force
若升级失败，回退：
startup downgrade
@catdwgrd.sql（10.2.0.1运行的是这个，而10.1降级用的是d92000.sql，即dold_release.sql）
startup force
15、查看和修改字符集
修改字符集
查看字符集：
select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; ->默认为 WE8ISO8859P1
修改方法：
1. 停库，全备份
2. 独占模式
startup mount;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter database open;
3.改字符集
alter database character set ZHS16GBK; ->ERROR:superset of old character set,改为低版的会报错
alter database character set internal_use ZHS16GBK; ->强制改为低版
4.取消独占模式
startup force mount;
alter system disable restricted session;
16、物化视图
<物化视图>
顾名思义，物化视图不是虚表，而是保存在磁盘上的表。在大型数据库中使用sum,count,avg,min,max等。
权限： create materialized view，select ,create table 等
要查询重写引用别的模式中的物化视图管理，query_rewrite_enable=TRUE
创建：
CREATE materalized view mv1 as
select * from a,b,c
where a...
group by
order by ;
创建方法： build immediate ,build deferred
build immediate --->创建物化视图的时候就生成数据，默认
build deferred ---->创建不生成数据，以后根据需要生成数据
刷新方式：
complete:刷新对整个mv完全刷新
fast:快速，增量刷新 --> 要有mv log
force: 选择性刷新，如果可以用fast方式，就采用fast,否则采用完全刷新 （complete） 默认
nerver: 从不刷新
on demand ：根据需要手工刷新 job ,mview
on commit: 基表数据变化，并且commit,触发刷新物化视图
手动刷新：
execute dbms_mview.refresh('mymv');
__________________________________________
eg1:
建mv log:
create materialized view log on scott.emp; -->log 使mv能fast刷新
drop materialized view log on scott.emp;
create materialized view log on scott.emp tablespace users with rowid; --> rowid有增加记录到日志
创建物化视图:
create materialized view mv10
build deferred
refresh force
on demand
start with to_date('2010-08-13 10:30:00','yyyy-mm-dd hh24:mi:ss')
next sysdate + 1/24/12 -->5分钟刷新一次
as
select * from scott.emp;
修改已经建立好的物化视图的刷新时间：
1.查看job:
select job,last_date,what from user_jobs;
JOB LAST_DATE WHAT
---------- --------- ---------------------------------------------
41 13-AUG-10 dbms_refresh.refresh('"SYS"."MV10"');
2.改刷新时间：
begin
dbms_job.interval(job=>41,interval=>'sysdate +1/48');
end;
/
删除物化视图：
drop materialized view mv10;
drop materialized view log on scott.emp;
___________________________________________
eg2:
create materialized view mv11
build immediate
refresh on commit
enable query rewrite
as
select * from scott.emp;
SQL> select count(*) from mv11;
SQL> delete from scott.emp
2 where rownum<2;
SQL> commit;
SQL> select count(*) from mv11;
___________________________________________
eg 3:
普通用户创建物化视图并刷新mv的权限：
grant create materialized view to scott;
grant execute on dbms_mview to scott; -->可手动刷新的权限
conn soctt/tiger
create materialized view mv12
as
select * from emp;
execute dbms_mview.refresh('mv12','c'); -->手动刷新
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
eg4
访问另一台机的表创建物化视图
SQL> create user solo identified by solo account unlock;
SQL> grant connect,resource to solo;
SQL> grant create materialized view to solo;
SQL> grant execute on dbms_mview to solo;
SQL> grant create database link to solo;
SQL> conn solo/solo
SQL> create database link lpod connect to scott identified by tiger using 'sun';
SQL> select count(*) from t_131@lpod;
SQL> create materialized view memp refresh force as select * from t_131@lpod;
SQL> select count(*) from memp;
SQL> execute dbms_mview.refresh('memp');
17、健康脚本
conn / as sysdba
spool htalthcheck.log
whenever sqlerror continue;
set lines 1000
select count(*) from dba_data_files;
select count(*) from v$tablespace;
select sum(bytes)/1024/1024 as "sum(bytes)" from dba_data_files;
select group#,bytes/1024/1024 from v$log;
archive log list;
select * from v$version;
select * from v$option where value='TRUE';
select pool,round(pool_bytes/1048576),round(100*pool_bytes/total_sga,2) pervent
from (select sum(bytes) total_sga from v$sgastat),
(select nvl(pool,name) pool,sum(bytes) pool_bytes from v$sgastat group by nvl(pool,name))
order by 3 desc;
select pool,name,bytes from v$sgastat;
select name,status from v$controlfile;
select group#,status,member from v$logfile;
select tablespace_name,contents,extent_management from dba_tablespaces
where contents='TEMPORARY';
select username,default_tablespace,temporary_tablespace from dba_users where temporary_tablespace='SYSTEM';
select username,default_tablespace from dba_users
where username not in ('SYS','SYSTEM')
and default_tablespace='SYSTEM';
select upper(f.tablespace_name) "ts-name",
d.tot_grootte_mb "ts-bytes(m)",
d.tot_grootte_mb - f.total_bytes "ts-used (m)",
f.total_bytes "ts-free(m)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2),'990.99') "ts-per"
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 5 desc;
spool off
2944
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
