网友oracle学习笔记

自己虽有两年开发经验，但不注意总结，今天借花献佛

######### 创建数据库----look $ORACLE_HOME/rdbms/admin/buildall.sql #############

create database db01
maxlogfiles 10
maxdatafiles 1024
maxinstances 2
logfile
GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo') SIZE 15M,
GROUP 2 ('/u01/oradata/db01/log_02_db01.rdo') SIZE 15M,
GROUP 3 ('/u01/oradata/db01/log_03_db01.rdo') SIZE 15M,
datafile 'u01/oradata/db01/system_01_db01.dbf') SIZE 100M,
undo tablespace UNDO
datafile '/u01/oradata/db01/undo_01_db01.dbf' SIZE 40M
default temporary tablespace TEMP
tempfile '/u01/oradata/db01/temp_01_db01.dbf' SIZE 20M
extent management local uniform size 128k
character set AL32UTE8
national character set AL16UTF16
set time_zone='America/New_York';

############### 数据字典 ##########

set wrap off

select * from v$dba_users;

grant select on table_name to user/rule;

select * from user_tables;

select * from all_tables;

select * from dba_tables;

revoke dba from user_name;

shutdown immediate

startup nomount

select * from v$instance;

select * from v$sga;

select * from v$tablespace;

alter session set nls_language=american;

alter database mount;

select * from v$database;

alter database open;

desc dictionary

select * from dict;

desc v$fixed_table;

select * from v$fixed_table;

set oracle_sid=foxconn

select * from dba_objects;

set serveroutput on

execute dbms_output.put_line('sfasd');

############# 控制文件 ###########

select * from v$database;

select * from v$tablespace;

select * from v$logfile;

select * from v$log;

select * from v$backup;

/备份用户表空间/
alter tablespace users begin backup;

select * from v$archived_log;

select * from v$controlfile;

alter system set control_files='$ORACLE_HOME/oradata/u01/ctrl01.ctl',
'$ORACLE_HOME/oradata/u01/ctrl02.ctl' scope=spfile;

cp $ORACLE_HOME/oradata/u01/ctrl01.ctl $ORACLE_HOME/oradata/u01/ctrl02.ctl

startup pfile='../initSID.ora'

select * from v$parameter where name like 'control%' ;

show parameter control;

select * from v$controlfile_record_section;

select * from v$tempfile;

/备份控制文件/
alter database backup controlfile to '../filepath/control.bak';

/备份控制文件，并将二进制控制文件变为了asc 的文本文件/
alter database backup controlfile to trace;

############### redo log ##############

archive log list;

alter system archive log start;--启动自动存档

alter system switch logfile;--强行进行一次日志switch

alter system checkpoint;--强制进行一次checkpoint

alter tablspace users begin backup;

alter tablespace offline;

/checkpoint 同步频率参数FAST_START_MTTR_TARGET,同步频率越高，系统恢复所需时间越短/
show parameter fast;

show parameter log_checkpoint;

/加入一个日志组/
alter database add logfile group 3 ('/$ORACLE_HOME/oracle/ora_log_file6.rdo' size 10M);

/加入日志组的一个成员/
alter database add logfile member '/$ORACLE_HOME/oracle/ora_log_file6.rdo' to group 3;

/删除日志组:当前日志组不能删；活动的日志组不能删；非归档的日志组不能删/
alter database drop logfile group 3;

/删除日志组中的某个成员，但每个组的最后一个成员不能被删除/
alter databse drop logfile member '$ORACLE_HOME/oracle/ora_log_file6.rdo';

/清除在线日志/
alter database clear logfile '$ORACLE_HOME/oracle/ora_log_file6.rdo';

alter database clear logfile group 3;

/清除非归档日志/
alter database clear unarchived logfile group 3;

/重命名日志文件/
alter database rename file '$ORACLE_HOME/oracle/ora_log_file6.rdo' to '$ORACLE_HOME/oracle/ora_log_file6a.rdo';

show parameter db_create;

alter system set db_create_online_log_dest_1='path_name';

select * from v$log;

select * from v$logfile;

/数据库归档模式到非归档模式的互换,要启动到mount状态下才能改变;startup mount;然后再打开数据库./
alter database noarchivelog/archivelog;

achive log start;---启动自动归档

alter system archive all;－－手工归档所有日志文件

select * from v$archived_log;

show parameter log_archive;

分析日志文件logmnr

在init.ora中set utl_file_dir 参数
重新启动oracle
create 目录文件
desc dbms_logmnr_d;
dbms_logmnr_d.build;
加入日志文件 add/remove log file
dhms_logmnr.add_logfile
dbms_logmnr.removefile
start logmnr
dbms_logmnr.start_logmnr
分析出来的内容查询 v$logmnr_content --sqlredo/sqlundo

实践：

desc dbms_logmnr_d;

/对数据表做一些操作，为恢复操作做准备/
update 表 set qty=10 where stor_id=6380;

delete 表 where stor_id=7066;
/***********************************/
utl_file_dir的路径
execute dbms_logmnr_d.build('foxdict.ora','$ORACLE_HOME/oracle/admin/fox/cdump');

execute dbms_logmnr.add_logfile('$ORACLE_HOME/oracle/ora_log_file6.log',dbms_logmnr.newfile);

execute dbms_logmnr.start_logmnr(dictfilename=>'$ORACLE_HOME/oracle/admin/fox/cdump/foxdict.ora');

######### tablespace ##############

select * form v$tablespace;

select * from v$datafile;

/表空间和数据文件的对应关系/
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts#=t2.ts#;

alter tablespace users add datafile 'path' size 10M;

select * from dba_rollback_segs;

/限制用户在某表空间的使用限额/
alter user user_name quota 10m on tablespace_name;

create tablespace xxx [datafile 'path_name/datafile_name'] [size xxx] [extent management local/dictionary] [default storage(xxx)];

exmple: create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M AUTOEXTEND ON NEXT 5M MAXSIZE 200M;
create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management dictionary default storage(initial 100k next 100k pctincrease 10) offline;
/9i以后，oracle建议使用local管理，而不使用dictionary管理，因为local采用bitmap管理表空间，不会产生系统表空间的自愿争用;/
create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management local uniform size 1m;
create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management local autoallocate;
/在创建表空间时，设置表空间内的段空间管理模式，这里用的是自动管理/
create tablespace userdata datafile '$ORACLE_HOME/oradata/userdata01.dbf' size 100M extent management local uniform size 1m segment space management auto;

alter tablespace userdata mininum extent 10;

alter tablespace userdata default storage(initial 1m next 1m pctincrease 20);

/*undo tablespace(不能被用在字典管理模下) */
create undo tablespace undo1 datafile '$ORACLE_HOME/oradata/undo101.dbf' size 40M extent management local;

show parameter undo;

/temporary tablespace/
create temporary tablespace userdata tempfile '$ORACLE_HOME/oradata/undo101.dbf' size 10m extent management local;

/设置数据库缺省的临时表空间/
alter database default temporary tablespace tablespace_name;

/系统/临时/在线的undo表空间不能被offline/
alter tablespace tablespace_name offline/online;

alter tablespace tablespace_name read only;

/重命名用户表空间/
alter tablespace tablespace_name rename datafile '$ORACLE_HOME/oradata/undo101.dbf' to '$ORACLE_HOME/oradata/undo102.dbf';

/重命名系统表空间 ,但在重命名前必须将数据库shutdown,并重启到mount状态/
alter database rename file '$ORACLE_HOME/oradata/system01.dbf' to '$ORACLE_HOME/oradata/system02.dbf';

drop tablespace userdata including contents and datafiles;---drop tablespce

/resize tablespace,autoextend datafile space/
alter database datafile '$ORACLE_HOME/oradata/undo102.dbf' autoextend on next 10m maxsize 500M;

/resize datafile/
alter database datafile '$ORACLE_HOME/oradata/undo102.dbf' resize 50m;

/给表空间扩展空间/
alter tablespace userdata add datafile '$ORACLE_HOME/oradata/undo102.dbf' size 10m;

/将表空间设置成OMF状态/
alter system set db_create_file_dest='$ORACLE_HOME/oradata';

create tablespace userdata;---use OMF status to create tablespace;

drop tablespace userdata;---user OMF status to drop tablespace;

select * from dba_tablespace/v$tablespace/dba_data_files;

/将表的某分区移动到另一个表空间/
alter table table_name move partition partition_name tablespace tablespace_name;

ORACLE storage structure and relationships

/手工分配表空间段的分区(extend)大小/
alter table kong.test12 allocate extent(size 1m datafile '$ORACLE_HOME/oradata/undo102.dbf');

alter table kong.test12 deallocate unused; ---释放表中没有用到的分区

show parameter db;

alter system set db_8k_cache_size=10m; ---配置8k块的内存空间块参数

select * from dba_extents/dba_segments/data_tablespace;

select * from dba_free_space/dba_data_file/data_tablespace;

/数据对象所占用的字节数/
select sum(bytes) from dba_extents where onwer='kong' and segment_name ='table_name';

############ UNDO Data ################

show parameter undo;

alter tablespace users offline normal;

alter tablespace users offline immediate;

recover datafile '$ORACLE_HOME/oradata/undo102.dbf';

alter tablespace users online ;

select * from dba_rollback_segs;

alter system set undo_tablespace=undotbs1;

/忽略回滚段的错误提示/
alter system set undo_suppress_errors=true;

/在自动管理模式下,不会真正建立rbs1;在手工管理模式则可以建立,且是私有回滚段/
create rollback segment rbs1 tablespace undotbs;

desc dbms_flashback;

/在提交了修改的数据后,9i提供了旧数据的回闪操作,将修改前的数据只读给用户看,但这部分数据不会又恢复在表中,而是旧数据的一个映射/
execute dbms_flashback.enable_at_time('26-JAN-04:12:17:00 pm');

execute dbms_flashback.disable;

/回滚段的统计信息/
select end_time,begin_time,undoblks from v$undostat;

/undo表空间的大小计算公式: UndoSpace=[UR * (UPS * DBS)] + (DBS * 24)
UR :UNDO_RETENTION 保留的时间(秒)
UPS :每秒的回滚数据块
DBS:系统EXTENT和FILE SIZE(也就是db_block_size)/

select * from dba_rollback_segs/v$rollname/v$rollstat/v$undostat/v$session/v$transaction;

show parameter transactions;

show parameter rollback;

/在手工管理模式下,建立公共的回滚段/
create public rollback segment prbs1 tablespace undotbs;

alter rollback segment rbs1 online;----在手工管理模式

/*在手工管理模式中,initSID.ora中指定 undo_management=manual 、rollback_segment=('rbs1','rbs2',...)、
transactions=100 、transactions_per_rollback_segment=10
然后 shutdown immediate ,startup pfile=....???.ora */

########## Managing Tables ###########

/*char type maxlen=2000;varchar2 type maxlen=4000 bytes
rowid 是18位的64进制字符串 (10个bytes 80 bits)
rowid组成: object#(对象号)--32bits,6位
rfile#(相对文件号)--10bits,3位
block#(块号)--22bits,6位
row#(行号)--16bits,3位
64进制: A-Z,a-z,0-9,/,+ 共64个符号

dbms_rowid 包中的函数可以提供对rowid的解释*/

select rowid,dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid) from table_name;

create table test2
(
id int,
lname varchar2(20) not null,
fname varchar2(20) constraint ck_1 check(fname like 'k%'),
empdate date default sysdate)
) tablespace tablespace_name;

create global temporary table test2 on commit delete/preserve rows as select * from kong.authors;

create table user.table(...) tablespace tablespace_name storage(...) pctfree10 pctused 40;

alter table user.tablename pctfree 20 pctused 50 storage(...);---changing table storage

/手工分配分区,分配的数据文件必须是表所在表空间内的数据文件/
alter table user.table_name allocate extent(size 500k datafile '...');

/释放表中没有用到的空间/
alter table table_name deallocate unused;

alter table table_name deallocate unused keep 8k;

/将非分区表的表空间搬到新的表空间,在移动表空间后，原表中的索引对象将会不可用，必须重建/
alter table user.table_name move tablespace new_tablespace_name;

create index index_name on user.table_name(column_name) tablespace users;

alter index index_name rebuild;

drop table table_name [CASCADE CONSTRAINTS];

alter table user.table_name drop column col_name [CASCADE CONSTRAINTS CHECKPOINT 1000];---drop column

/给表中不用的列做标记/
alter table user.table_name set unused column comments CASCADE CONSTRAINTS;

/drop表中不用的做了标记列/
alter table user.table_name drop unused columns checkpoint 1000;

/当在drop col是出现异常，使用CONTINUE，防止重删前面的column/
ALTER TABLE USER.TABLE_NAME DROP COLUMNS CONTINUE CHECKPOINT 1000;

select * from dba_tables/dba_objects;

######## managing indexes ##########

/create index/
example:
/创建一般索引/
create index index_name on table_name(column_name) tablespace tablespace_name;
/创建位图索引/
create bitmap index index_name on table_name(column_name1,column_name2) tablespace tablespace_name;
/索引中不能用pctused/
create [bitmap] index index_name on table_name(column_name) tablespace tablespace_name pctfree 20 storage(inital 100k next 100k) ;
/大数据量的索引最好不要做日志/
create [bitmap] index index_name table_name(column_name1,column_name2) tablespace_name pctfree 20 storage(inital 100k next 100k) nologging;
/创建反转索引/
create index index_name on table_name(column_name) reverse;
/创建函数索引/
create index index_name on table_name(function_name(column_name)) tablespace tablespace_name;
/建表时创建约束条件/
create table user.table_name(column_name number(7) constraint constraint_name primary key deferrable using index storage(initial 100k next 100k) tablespace tablespace_name,column_name2 varchar2(25) constraint constraint_name not null,column_name3 number(7)) tablespace tablespace_name;

/给创建bitmap index分配的内存空间参数，以加速建索引/
show parameter create_bit;

/改变索引的存储参数/
alter index index_name pctfree 30 storage(initial 200k next 200k);

/给索引手工分配一个分区/
alter index index_name allocate extent (size 200k datafile '$ORACLE/oradata/..');

/释放索引中没用的空间/
alter index index_name deallocate unused;

/索引重建/
alter index index_name rebuild tablespace tablespace_name;

/普通索引和反转索引的互换/
alter index index_name rebuild tablespace tablespace_name reverse;

/重建索引时，不锁表/
alter index index_name rebuild online;

/给索引整理碎片/
alter index index_name COALESCE;

/分析索引,事实上是更新统计的过程/
analyze index index_name validate structure;

desc index_state;

drop index index_name;

alter index index_name monitoring usage;-----监视索引是否被用到

alter index index_name nomonitoring usage;----取消监视

/有关索引信息的视图/
select * from dba_indexes/dba_ind_columns/dbs_ind_expressions/v$object_usage;