第0章 虚拟环境搭建
alter user identified by tiger account unlock
connect sys/pasword as sysdba
col name for a20
select * from v$controlfile;
shutdown immediate
host copy D:\app\Administrator\oradata\orcl\*.* E:\Backup
host copy D:\app\Administrator\product\11.2.0\dbhome_1\database\*.* D:\Backup\dabse;
第1章 oracle体系结构
alter system set share_pool_size=32M;
select id ,name,block_size,advice_status from v$db_cache_advice;
--动态修改db_buffers_cache(数据库高速缓冲区=db_block_size * db_buffers_size)大小
alter system set db_cache_size = 48M;
--监控db_buffers_cache(数据库高速缓冲区)
select id,name,block_size,advice_status from v$db_cache_advice;
alter system set db_cache_advice = off;
show parameter
show sga
col value for a25
select name,type,value from v$parameter where name='sga_max_size';
select name,type,value from v$parameter where name='db_block_size';
select name,type,value from v$parameter where name='db_buffers_size';
select name,type,value from v$parameter where name='db_cache_size';
第二章 数据库管理工具
sqlplus/nolog
第三章 Oracle实例的管理
create spfile from pfile;
create pfile='mypfile' from spfile;
startup [pfile=''] open | mount | nomount;
alter database open |read only|read write;
startup restrict;
alter system enable restricted session ;
select user#,username, sid,serial# from v$session;
select user#,sid,serial#,username from v$session where username='SCOTT';
alter system kill session '73.922';
/***诊断文件**/
col name for a23
col value for a33
set line 100
select name , value from v$parameter where name like '%_dest';
--1.报警文件(alter语句,表空间创建等):background__dump_dest
alter tablespace users read only;
alter tablespace users read write;
--2.后台进程追踪文件记录(进程错误信息)background__dump_dest
--3.用户进程追踪文件(用户进程遇到用户会话错误时创建):user_dump_dest
--会话级开启用户(进程)追踪
alter session set sql_trace = true;
alter session set sql_trace = false;
--实例级别开启(尽量避免)
初始化参数文件中sql_trace = trace
第四章 数据字典和控制文件
/**数据字典***/
------ ------ -------
user_* all_* dba_*
*_tables *_index *_objects
------ ------- ------
col owner for a8
col object_name for a12
col object_type for a10
select owner,object_name,object_id,created,status,object_type from all_objects where owner='SCOTT';
--查询可使用的表
select table_name,owner from all_tables where owner not like '%sys';
select * from user_catalog(cat);
select * from dictionary where table_name like '%table%';
select * from v$fixed_table;
select table_name,column_name,comments from dict_columns where table_name='DBA_TABLES' AND COLUMN_NAME='INITIAL_EXTENT';
=====宝儿第一天上班============================
select name,created,log_mode,open_mode from v$database;
col host_name for a10;
select host_name,instance_name,version from v$instance;
select * from v$version;
col name for a25
select * from v$controlfile;
select group#,members,bytes,status,archived from v$log;
col member for a15
select * from v$logfile;
col tablespace_name for a12
select tablespace_name,block_size,status,contents,logging from dba_tablespaces;
col file_name for a40
select file_id,file_name,tablespace_name,status,bytes from dba_data_files;
select username,created from dba_users;
=================================
/**控制文件**/
set pagesize 30
select type,record_size,records_total,records_used from v$controlfile_record_section;
--查看控制文件的位置
select value from v$parameter where name='control_files';
col name for a15
select * from v$controlfile;
alter system set control_files =
'E:\Disk1\CONTROL01.CTL',
'E:\Disk2\CONTROL02.CTL' SCOPE=SPFILE;
--备份
alter database backup controlfile to 'E:\Backup\control.bak';
--将创建控制文件的命令备份到一个追踪文件中
alter database backup controlfile to trace;
=======================================
/*移动控制文件*/
0.show parameter pfile
1.
select value from v$parameter where name='control_files';
col name for a15
select * from v$controlfile;
NAME
----------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CO
NTROL01.CTL
C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CO
NTROL02.CTL
C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CO
NTROL03.CTL
2.
alter system set control_files =
'C:\Disk3\CONTROL01.CTL',
'C:\Disk6\CONTROL02.CTL',
'C:\Disk9\CONTROL03.CTL' SCOPE=SPFILE;
3.
shutdown immediate;
4.
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CONTROL01.CTL C:\Disk3\CONTROL01.CTL;
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CONTROL02.CTL C:\Disk6\CONTROL02.CTL;
host copy C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CONTROL01.CTL C:\Disk9\CONTROL03.CTL;
5.
startup
6.检验是否正确
select name from v$controlfile;
/*备份控制文件*/
alter database backup controlfile to 'C:\backup\control.bak';
alter database backup controlfile to trace;
col name for a23
col value for a33
select name , value from v$parameter where name like '%_dest';
=======================================
第5章 重做日志文件
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKOUT;
select group#,sequence#,members,bytes,status,archived from v$log;
col member for a30
select * from v$logfile;
alter database add logfile
('C:/Disk3/REDO04A','C:/Disk6/REDO04B')
size 15M;--组号自动递增
alter database drop logfile group 4;
--增加重做日志成员
alter database add logfile member
'C:\Disk3\redo01b.log' to group 1,
'C:\Disk3\redo02b.log' to group 2,
'C:\Disk3\redo03b.log' to group 3;
--删除重做日记成员
alter database drop logfile member
'C:\Disk3\redo01b.log' ,
'C:\Disk3\REDO02B.LOG' ,
'C:\Disk3\REDO03B.LOG' ;
--重新初始化联机重做日志文件
alter database clear logfile group 组号;
alter database clear unarchived logfile group 组号;
--以OMF(Oracle Managered Files)的方式创建一组重做日志
alter system set DB_CREATE_ONLINE_LOG_DEST_1 = 'C:\Disk3';
alter system set DB_CREATE_ONLINE_LOG_DEST_2 = 'C:\Disk6';
alter system set DB_CREATE_ONLINE_LOG_DEST_3 = 'C:\Disk9';
alter database add logfile;
SELECT group#,sequence#,members,bytes,status,archived from V$log;
col member for a50;
set line 120
select * from v$logfile;
--删除以OMF(Oracle Managered Files)的方式创建一组重做日志
alter database drop logfile group 4;
===========================
select group#,sequence#,members,bytes,status,archived from v$log;
col member for a30
select * from v$logfile;
===========================
