Fundamentals IChapter1Oracle Architectural Componentsshow sga；alter system set shared_pool_size=64M;alter system set DB_CACHE_SIZE=96M;alter system set large_pool_size=24Mdesc v$db_cache_adviceselect*from v$db_cache_advice;(displayed statistics)Chapter2Getting Started with the Oracle Server1、list the common database administration tools available to a DBAOracle Universal InstallerOracle Database Configuration AssistantPassword File UtilitySQL*PlusOracle Enterprise Manager2、SQL statementscolumn owner format a5;column table_name format a20;column tablespace_name format a15;select owner,table_name,tablespace_name from dba_tables where rownum<10;SQL>!rm$HOME/admin/pfile/orapw$ORACLE_SIDSQL>!orapwd file=$HOME/admin/pflie/orapw$ORACLE_SID password=oracle entries=5SQL>!chmod660$HOME/admin/pfile/orapw$ORACLE_SIDChapter3Managing an Oracle Instance1、create spfile[='spflie-name']from pfile[='pfile-name']for examplecreate spflie='$ORACLE_HOME/dbs/spfilebbk.ora'from pfile='$ORACLE_HOME/dbs/initbbk.ora'; DefaultSQL>create spfile from pfile;SQL>create pflie from spflie;modifying parameters in spfileSQL>alter system set undo_tablespace=undo2;SQL>alter system set undo_tablespace=undo2scope=both;deleting or resetting valuesSQL>alter system reset undo_suppress_errors scope=both sid='*';alter system set parameter_name=parameter_value[comment'text'][scope=memory|spfile|both][sid='sid'|'*'] startup database filestartup pfile=$ORACLE_HOME/dbs/initbbk.orastartup spflie=$ORACLE_HOME/dbs/spfilebbk.orastartup[force][restrict][pfile=filename][open][recover][database][mount][nomount]alter database bbk mount;alter database bbk open read only;alter database{mount|open}alter database open[read write|read only]startup restrictalter system enable restricted session;shutdown[abort|immediate|transactional|normal]alter session set SQL_TRACE=trueChapter4Creating a Databaseorapwd file=$ORACLE_HOME/dbs/orapwU15password=admin entries=5set remote_login_passwordfile=exclusive in initialization parameter fileadd user to the password fileassign appropriate privileges to each userSQL>grant sysdba to heli;Chapter5Using Data Dictionary and Dynamic Performance Viewsselect owner,object_name,object_type from dba_objects;select owner,object_name,object_type from all_objects;select object_name,object_type from user_objects;general overview:dictionary,dict_columnsshcema objects:dba_tables,dba_indexs,dba_tab_columns,dba_constraintsspace allocation:dba_segments,dba_extentsdatabase structure:dba_tablespaces,dba_data_filesdynamic performance examplesv$controlfile:Lists the names of the control filesv$database:Contains database information from the control filev$datafile:Contains data file information from the control filev$instance:Displays the state of the current instancev$parameter:Lists parameter and values currently in effect for the sessioonv$session:Lists session information for each current sessionv$sga:Contains summary information on the system global areav$spparameter:Lists the contents of the SPFILEv$tablespace:Displays tablespace information from the control filev$thread:Contains thread information from the control filev$version:Version numbers of core library components in the Oracle serverselect*from v$fixed_table;7、list of the data dictionary viewsselect table_name from dictionary;8、identify the database name,instance name and size of the database blocksselect name from v$database;select instance from v$thread;select value from v$parameter where name='db_block_size';9、list the name and size the data files,online redo log files,and the name of the control files select name from v$datafile;select member from v$logfile;select name from v$controlfile;10、identify the data file that makes up the system tablespaceselect file_name from dba_data_files where tablespace_name='SYSTEM';11、free space and used spaceselect sum(bytes)/1024"free space in KB"from dba_free_space;select sum(bytes)/1024"used space in KB"from dba_segments;12、list the name and creation date of the database usersselect username,created from dba_users;Chapter6Maintaining the Control Filemultiplexing the control file when using spfilealter system setcontrol_files='$HOME/oracdata/u01/control01.ctl','$HOME/oradata/u02/control02.ctl'scope=spfile;shutdown immediatecp$HOME/oradata/u01/control01.ctl$HOME/oradata/u02/control02.ctlstartup;multiplexing the contril file when using pfileshutdown immediatecp$HOME/oradata/u01/control01.ctl$HOME/oradata/u02/control02.ctlcontrol_files=$HMOE/oradata/u01/control01.ctl,$HOME/oradata/u02/control02.ctlFor exampleSQL>alter system set control_files='/u010g/oradata/bbk/control01.ctl','/u010g/oradata/bbk/control02.ctl','/u010g/oradata/bbk/control03.ctl','/u010g/oradata/bbk/control04.ctl' scope=spfile;SQL>shutdown immediateSQL>!cp/u010g/oradata/bbk/control03.ctl/u010g/oradata/bbk/control04.ctlSQL>!chmod660/u010g/oradata/bbk/control04.ctlSQL>startup;SQL>select*from v$controlfile;4、inital sizing of the data file section in control fileSQL>select records_total from v$controlfile_record_section where type='DATAFILE';Chapter7Maintaining Online Redo Log Files1、forcing a log switchalter system switch logfileSQL>select group#,thread#,sequence#,status from v$log;SQL>select group#,members,archived,status from v$log;SQL>select tablespace_name,contents,extent_management from dba_tablespaces;checkpoint can be forced by:setting fast_start_mttr_target parameterFAST_START_MTTR_TARGET=600alter system checkpoint commandalter system checkpoint;2、add log groupalter database add logfile group3('$HOME/oradata/u01/log03a.rdo','$HOME/oradata/u02/log3b.rdo')size2M;3、adding online redo log file membersalter database add logfile member'$HOME/oradata/u04/log1c.rdo'TO GROUP1,'$HOME/oradata/u04/log2c.rdo'TO GROUP2,'$HOME/oradata/u04/log3c.rdo'TO GROUP3;4、dropping online redo log file groupsalter database drop logfile group3;5、dropping online redo log file membersalter database drop logfile member'$HOME/oradata/u04/log3c.rdo';6、alter database rename file command(1)SQL>shutdown immediate(2)copy the online redo log files to the new location(3)startup the database and mount,but do not open itSQL>connect/as sysdbaSQL>startup mountSQL>alter database rename file'$HOME/oradata/u01/log2a.rdo'TO'$HOME/oradata/u02/log1c.rdo';SQL>alter database open;7、clearing online redo log filealter database clear logfile group2;alter database clear unarchived logfile group2;test3SQL>col group#format99SQL>col member format a40SQL>alter database add logfile member'/u010g/oradata/bbk/redo01b.log'to group1,SQL>'/u010g/oradata/bbk/redo02b.log'to group2,SQL>'/u010g/oradata/bbk/redo03b.log'to group3;SQL>select*from v$logfile;test4alter database add logfile group4('/u010g/oradata/bbk/redo01b.log','/u010g/oradata/bbk/redo01.log')size51M;column group#format99column member format a40select*from v$logfile;select group#,members from v$log;test5SQL>alter system switch logfile;SQL>alter system switch logfile;SQL>alter system switch logfile;SQL>alter system switch logfile;SQL>select group#,members from v$log;SQL>alter database drop logfile group4;SQL>!rm/u010g/oradata/bbk/redo01b.rdoSQL>!rm/u010g/oradata/bbk/redo02b.rdotest6SQL>alter database add logfilegroup3('/u010g/oradata/bbk/redo03a.rdo','/u010g/oradata/bbk/redo03b.rdo')size51M,group4('/u010g/oradata/bbk/redo04a.rdo','/u010g/oradata/bbk/redo04b.rdo')size51M;SQL>select group#,status from v$log;SQL>alter system switch logfile;SQL>alter system switch logfile;SQL>alter system switch logfile;SQL>alter system switch logfile;SQL>alter database drop logfile group1,group2;SQL>select group#,bytes from v$log;Chapter8Managing Tablespaces and Data FilesSQL>select a.tablespace_name,a.bytes bytes_used,rgest,round(((a.bytes- b.bytes)/a.bytes)*100,2)percent_used from(select tablespace_name,sum(bytes)bytes from dba_data_files group by tablespace_name)a,(select tablespace_name,sum(bytes)bytes, max(bytes)largest from dba_free_space group by tablespace_name)b where a.tablespace_name=b.tablespace_name order by((a.bytes-b.bytes)/a.bytes)DESC;configuring ASSMcreate tablespace data02datafile'/u01/oradata/data02.dbf'size5M extent management local uniform szie64K segment space mangement auto;create database dba01logfilegroup1('/$HOME/oradata/bbk/redo01.log')size100M,group2('/$HOME/oradata/bbk/redo02.log')size100M,group3('/$HOME/oradata/bbk/redo03.log')size100M,maxlogfiles5maxlogmembers5maxloghistory1maxdatafiles100maxinstances1datafiles'/$HOME/oradata/bbk/system01.dbf'size325Mundo tablespace undotbsdatafile'/$HOME/oradata/bbk/undotbs01.dbf'size200Mdefault temporary tablespace temptempfile'/$HOME/oradata/bbk/tem01.dbf'size50Mcharacter set US7ASCIIcreating a default temporary tablespacealter database default temporary tablespace default_temp2;To find the default temporary tablespace for the database query database_properties;select*from database_properties;alter tablespace userdata read only;Tablespaces that cannot be taken offline:system,tablespace tablespace with active undo segments,default temporary tablespace.alter tablespace userdata offline;alter tablespace userdata online;changing storage settingsalter tablespace userdata minimum extent2m;alter tablespace userdata default storage(initial2M next2M maxextents999);create tablespace userdata02datafile'/u010g/oradata/bbk/userdata02.dbf'size5Mautoextend on next2M maxsize200M;alter database datafile'/u010g/oradata/bbk/userdata02.dbf'autoextend on next2M;alter database datafile'/u010g/oradata/bbk/userdata02.dbf'resize5M;alter tablespace userdata02add datafile'/u010g/oradata/bbk/userdata03.dbf'szie5m;create tablespace user_data datafile'/u010g/oradata/bbk/user_data.dbf'size200M autoxtend onnext10M maxsize500M;select tablespace_name,file_name,autoextensible from dba_data_files;methods for moving data filesalter tablespace:tablespace must be offline,target data file must existalter tablespace userdata rename datafile'/u010g/oradata/bbk/userdata.dbf'to'/u010g/oradata/bbk/userdata01.dbf';alter database:database must be mounted,target data file must existalter database rename file'/u010g/oradata/bbk/system01.dbf'to'/u110g/oradata/bbk/system01.dbf';droping tablespaceYou cannot drop a tablespace if it:Is the system tablespacehas active segmentsincluding contents drops the segmentsincluding contents and datafiles deletes data filescascade constraints drops all referential integrity constraintsdrop tablespace userdata including contents and datafiles;Obtaining Tablespace Informationtablespace information:dba_tablespacev$tablespacedata file information:dba_data_filesv$datafiletemp file information:dba_temp_filesv$tempfileChapter9Storage Structure and Relationshipscreate tablespace data02datafile'/u01/oradata/data02.dbf'size5M extent management local uniform size64k segment space management auto;obtaining storage information:dba_extents:dba_segments:dba_tablespaces:dba_data_files:dba_free_spaceSQL>select segment_name,tablespace_name,extents,blocks from dba_segments;SQL>select extent_id,file_id,block_id,blocks from dba_extents;SQL>select tablespace_name,count(*),max(blocks),sum(blocks)from dba_free_space group by tablespace_name;test1create table emp(empno number(4),ename varchar2(30),job varchar2(9),mgr number(4),hiredate date,sal number(7,2),comm number(7,2),deptno number(2))tablespace data01storage(initial100knext100kpctincrease0minextents8maxextents10);create table fragment1(a number)tablespace data01storage(initial10K);create table dept(deptno number,dname varchar2(15),loc varchar2(20))tablespace data01storage(initial50k next50k);create table frament2(a number)tablespace data01storage(initial8k);create table big_emp(emp number(4),ename varchar2(30))tablespace data01storage(initial1Mnext1M maxextents10);create index i_e_empno on emp(ename)tablespace indx01storage(initial50K next50K);drop table fragment1;drop table fragment2;test2select distinct segment_type from dba_segments;test3column segment_name format a20column segment_type format a15select segment_name,segment_type,max_extents,extents from dba_segments where extents+5>max_extents and segment_type<>'cache'; test4select distinct f.file_name from dba_extents e,dba_data_file f where e.segment_name='EMP'and e.file_id=f.file_id;test6select tablespace_name,count(*)as fragments,sum(bytes)as total,max(bytes)as largest from dba_free_space group by tablespace_name; test7select s.segment_name,s.segment_type,s.tablespace_name,s.next_extent from dba_segments s wherenot exists(select1from dba_free_space f where s.tablespace_name=f.tablespace_name having max(f.types)>s.next_extent);Chapter10Managing Undo Data:dba_rollback_segs:v$rollname:v$rollstatv$undostatv$sessionv$transactionSQL>select segment_name,owner,tablespace_name,status from dba_rollback_segs;SQL>select ,s.extents,s.rssize,s.hwmsize,s.xacts,s.status from v$rollname n,v$rollstat s where n=n;SQL>select ername,t.xidusn,t.ubafil,t.ubablk,ed_ublk from v$session s,v$transaction t where s.saddr=t.ses_addr;SQL>select max(undoblks/((end_time-begin_time)*24*3600))from v$undostat;SQL>select sum(undoblks)/sum((end_time-begin_time)*24*3600)from v$undostat;SQL>select to_char(begin_time,'hh24:mi:ss')begin_time,to_char(end_time,'hh24:mi:ss')end_time,undoblks from v$undostat where rownum<4;test1list the undo segments in tablespace undotbsSQL>select segment_name from dba_rollback_segs where tablespace_name='UNDOTBS';test2create undo tablespace undo02datafile'/u010g/oradata/bbk/undo02.dbf'size15M;select segment_name from dba_rollback_segs where tablespace_name='UNDO02';test4and5SQL>alter system set undo_tablespace='undo02'scope=both;SQL>drop tablespace undotbs1including contents and datafiles;test6SQL>select segment_name from dba_rollback_segs where tablespace_name='UNDOTBS';SQL>select n,,b.status from v$rollname a,v$rollstat b where in(select segment_name from dba_segments where tablespace_name='UNDOTBS')and n=n;test9SQL>alter system set undo_retention=0scope=memory;SQL>drop tablespace undotbs including contents and datafiles;(tablespace dropped)Chapter11Managing Tablesselect rowid,id,name from t;create tablespace assm datafile'u01/oradata/denver/assm_1.dbf'size100M extent management local uniform size128K segment space management auto;create tablespace mssm datafile'u01/oradata/denver/mssm_1.dbf'size100M extent management local uniform size128K segment space management manual;create user as1identified by as1default tablespace assm;create user ms1identified by ms1default tablespace mssm;grant connect,resource to as1;grant connext,resource to ms1;sqlplus as1/as1SQL>show userSQL>desc user_segment;SQL>col segment_name format a30;SQL>select segment_name,segment_type from user_segments;SQL>create table t(id int,name char(10))storage(initial128k next128k pctincrease0minextents1maxextents5)tablespace assm; SQL>select segment_name,segment_type from user_segments;temporary tablesessionSQL>create global temporary table tmp_session on commit preserve rows as select*from t where1=0;transactionSQL>create global temporary table tmp_transaction on commit delete rows as select*from t where1=0;SQL>insert into t values(0,'name');SQL>insert into t values(1,'name');SQL>insert into tmp_session select*from t;SQL>insert into tmp_transaction select*from t;SQL>select count(*)from tmp_session;SQL>select count(*)from tmp_transaction;SQL>commit;SQL>select count(*)from tmp_session;SQL>select count(*)from tmp_transaction;Setting PCTFREE&PCTUSEDcompute PCTFREE(average row size-initial row size)*100/average row sziecompute PCTUSED100-PCTFREE-(average row size*100)/available data spacelookup create table all informaionSQL>select dbms_metadata.get_addl('TABLE','tablename','schema')from dual;SQL>alter table t move tablespace mssm;SQL>alter table t drop column comments cascade constraints checkpoint1000;SQL>alter table t set unused column name cascade constraints;SQL>alter table t drop unused column;SQL>select segment_name,file_id,block_id,blocks from dba_extents where segment_name='ORDERS'and segment_type='TABLE'; SQL>select count(*)from dba_extents where segment_name='ORDERS'and segment_type='TABLE';SQL>alter table orders allocate extent;SQL>select count(*)from dba_extents where segment_name='ORDERS'and segment_type='TABLE';SQL>truncate table orders reuse storage;SQL>select count(*)from dba_extents where segment_name='ORDERS'and segment_type='TABLE';Chapter12Managing IndexesSQL>create table t(id int,sex char(1),name char(10));SQL>desc user_objects;SQL>col object_name format a20SQL>select object_name,object_type from user_objects;SQL>beginfor i in1..1000loopinsert into t values(i,'M','zhangsan');end loop;commit;end;/SQL>create index t_idx1on t(id);SQL>select object_name,object_type from user_objects;SQL>create index t_idex2on t(sex,name);SQL>select object_name,object_type from user_objects;SQL>create bitmap index my_bit_idx on t(sex);SQL>alter index orders_region_id_idx rebuild tablespace index02;SQL>alter index orders_id_idx rebuild online;SQL>select object_name,object_type from user_objects;SQL>alter index t_idx1rebulid;SQL>alter index t_idx1rebulid online;SQL>alter index orders_id_idx coalesce;analyze indexSQL>analyze table t1compute statistics for table;SQL>analyze table t2compute statistics for all columns;SQL>analyze table t3compute statistics for all indexed columns;SQL>analyze table t4compute statistics;analyze TABLE a COMPUTE statistics FOR TABLE;对表做统计分析,仅仅影响all_tables中的相关数据analyze TABLE b COMPUTE statistics FOR ALL indexed columns;对索引列做统计分析,仅仅影响all_tab_columns中索引列相关数据。
