ORALE 新手入门速成篇

因为项目需要，先前学过的oracle需要重新拾起来，根据自己的理解，同时参考了tom大神的神作，进行了梳理。

当你接手一个陌生的环境，你需要有一个初步的了解：

show user
show errors
select * from v$version;
select instance_name from v$instance;
select database_name from v$database;
select userenv('language') from dual;
select * from v$logfile;
select * from v$datafile;
select * from controlfile;
desc dual;

作为一个DBA，还需要了解就是oracle的start涉及的几个phase：

start nomout: 构建oracle实例

Starting an instance includes the following tasks:
? Searching $ORACLE_HOME/dbs for a file of a particular name in this sequence:
1. Search for spfile.ora.
2. If spfile.ora is not found, search for spfile.ora.
3. If spfile.ora is not found, search for init.ora.
This is the file that contains initialization parameters for the instance. Specifying the
PFILE parameter with STARTUP overrides the default behavior.
? Allocating the SGA
? Starting the background processes
? Opening the alert_.log file and the trace files

mount：alter database mount：

Mounting a database includes the following tasks:
? Associating a database with a previously started instance
? Locating and opening all of the control files specified in the parameter file
? Reading the control files to obtain the names and statuses of the data files and online redo
log files (However, no checks are performed to verify the existence of the data files and
online redo log files at this time.)

open：

Opening the database includes the following tasks:
? Opening the data files
? Opening the online redo log files

SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area  778387456 bytes
Fixed Size 1374808 bytes
Variable Size 494929320 bytes
Database Buffers          276824064 bytes
Redo Buffers                5259264 bytes
SQL> select * from v$controlfile;

未选定行
SQL> select 'abc' from dual;

'ABC'
------
abc
SQL> select value from v$spparameter where name='control_files';

VALUE
--------------------------------------------------------------------------------

H:\app\kinfinger\oradata\rmfdb\control01.ctl
H:\app\kinfinger\flash_recovery_area\rmfdb\control02.ctl

SQL> select * from v$datafile;
 select * from v$datafile
               *
第 1 行出现错误:
ORA-01507: ??????
SQL> select table_name from user_tables;
select table_name from user_tables
                       *
第 1 行出现错误:
ORA-01219: ??????: ???????/?????
ORA-01219:database not open:queries allowed on fixed tables/views only
“ORA-01219:数据库未打开：仅允许在固定表/视图中查询”的警告窗口！
读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。
SQL> alter database mount;

数据库已更改。

出于测试目的，这里以windows平台为测试环境。由于oracle本身对系统资源消耗巨大，这里设置为手动启动，自己根据需要启动所需要的地址空间。

lsnrctl status
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12541: TNS: 无监听程序
 TNS-12560: TNS: 协议适配器错误
  TNS-00511: 无监听程序
   32-bit Windows Error: 2: No such file or directory

C:\Windows\system32>net start OracleServiceRMFDB
OracleServiceRMFDB 服务正在启动 ......................
OracleServiceRMFDB 服务已经启动成功。

C:\Windows\system32>lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 15-5月 -2016 16:2
7:27

Copyright (c) 1991, 2010, Oracle. All rights reserved.

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=kinfinger-PC)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
版本                      TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Produ
ction
启动日期                  15-5月 -2016 16:20:17
正常运行时间              0 天 0 小时 7 分 12 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序参数文件          H:\app\kinfinger\product\11.2.0\dbhome_1\network\admin
\listener.ora
监听程序日志文件          h:\app\kinfinger\diag\tnslsnr\kinfinger-PC\listener\al
ert\log.xml
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=kinfinger-PC)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
服务摘要..
服务 "CLRExtProc" 包含 1 个实例。
  实例 "CLRExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "rmfdb" 包含 1 个实例。
  实例 "rmfdb", 状态 READY, 包含此服务的 1 个处理程序...
服务 "rmfdbXDB" 包含 1 个实例。
  实例 "rmfdb", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功

特别注意：这里CMD需要有管理员权限，否则，监听器启动不起来。且没有任何报错误提示信息。

在启动监听器的时候，oracle实例没有注册，只有在启动实例后，status显示实例注册成功。至于注册2个实例的区别，后续研究。

当你需要连接到server时，你需要配置SQLNET.ORA,,需要用到一个调试工具TNSPING

H:\oracleexp>TnsPING LOCALHOST

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 15-5月 -
2016 21:55:40

Copyright (c) 1997, 2010, Oracle. All rights reserved.

已使用的参数文件:
H:\app\kinfinger\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

已使用 EZCONNECT 适配器来解析别名
尝试连接 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST
=127.0.0.1)(PORT=1521)))
OK (40 毫秒)

环境配置以及启动搞定以后，作为DBA，首先需要熟悉的就是你的工具sqlplus

这里学习一下TOM 大神的表空间监控脚本：

set serveroutput on size 1000000;
create or replace procedure show_space(
p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL
)
-- -this procedure use authid current user so it can query DBA_*
-- -views using priviliges from a role and so it can be installed
-- -once per database,instead of once user that wants to use it authid current_user
AUTHID CURRENT_USER
as
   l_free_blks number;
   l_total_blocks number;
   l_total_bytes number;
   l_unused_blocks number;
   l_unused_bytes number;
   l_LastUsedExtFileID number;
   l_LastUsedExtBlockID number;
   l_LAST_USED_BLOCK number;
   l_segment_space_mgmt varchar2(255);
   l_unformatted_blocks number;
   l_unformatted_bytes number;
   l_fs1_blocks number; l_fs1_bytes number;
   l_fs2_blocks         number; l_fs2_bytes number;
   l_fs3_blocks number; l_fs3_bytes number;
   l_fs4_blocks number; l_fs4_bytes number;
   l_full_blocks number; l_full_bytes number;

   --inline procedure to print out numbers nicely formatted
   --with a smple label
   procedure p(p_label in varchar2,p_num in number)
   is
   begin
        dbms_output.put_line(rpad(p_label,40,'.')||to_char(p_num,'999,999,999,999'));
   end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be  create by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary
-- note:at runtime,the invoker must have access to these two views
-- this query dertermines if the object is an assm object or not
    begin
    execute immediate
        'select ts.segment_space_management
        from dba_segments seg,dba_tablespace ts
        where seg.segment_name = :p_segname
        and (:p_partition is null or seg.partition_name = :p_partition)
        and seg.owner = :p_owner
        and seg.tablespace_name=ts.tablespace_name'
        into l_segment_space_mgmt
        using p_segname,p_partition,p_partition,p_owner;
    exception
        when too_many_rows then
        dbms_output.put_line
     ('This must be a partitioned table,use p_partition =>');
     return;
end;
--if the object is in an an assm tablespace ,we must use this API
--call to get space infomation,else we use the free_blocks
--API for the user management segments
if l_segment_space_mgmt = 'AUTO' then
    dbms_space.space_usage
    (p_owner,p_segname,p_type,
    l_unformatted_blocks,l_unformatted_bytes,
    l_fs1_blocks,l_fs1_bytes,
    l_fs2_blocks,l_fs2_bytes,
    l_fs3_blocks,l_fs3_bytes,
    l_fs4_blocks,l_fs4_bytes,
    l_full_blocks,l_full_bytes,p_partition);
    p('Unformatted Blocks',l_unformatted_blocks);
    p('FS1 BLOCKS(0-25)  ',l_fs1_blocks);
    p('FS2 BLOCKS(25-50) ',l_fs2_blocks);
    p('FS3 BLOCKS(50-75) ',l_fs3_blocks);
    p('FS4 BLOCKS(75-100)',l_fs4_blocks);
else
    dbms_space.free_blocks(
    segment_owner         => p_owner,
    segment_name          => p_segname,
    segment_type          => p_type,
    freelist_group_id => 0,
    free_blks             => l_free_blks
    );
    p('Free Blocks ',l_free_blks);
end if;
--and then the unused space api call to get the rest of the information
dbms_space.unused_space
(
      segment_owner          => p_owner,
    segment_name           => p_segname,
    segment_type           => p_type,
    partition_name          => p_partition,
    total_blocks          => l_total_blocks,
    total_bytes          => l_total_bytes,
    unused_blocks          => l_unused_blocks,
    unused_bytes          => l_unused_bytes,
    LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileID,
    LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockID,
    LAST_USED_BLOCK       => l_LAST_USED_BLOCK
    );

    p('Total      Blocks',l_total_blocks);
    p('Total     Bytes',l_total_bytes);
    p('Total    MBytes',trunc(l_total_bytes/1024/1024));
    p('Unused   Blocks',l_unused_blocks);
    p('Unused    Bytes',l_unused_bytes);
    p('Last Used Ext FileID',l_LastUsedExtFileID);
    p('Last used EXt Block ID ',l_LastUsedExtBlockID);
    p('Last used Block',l_LAST_USED_BLOCK);
end;
/

以及用户测试表空间脚本：big_table.sql

truncate table hugetable;
drop table hugetable;
commit;
create table hugetable
  as
  select rownum id,a.*
  from all_objects a
  where 1=0
  /
  alter table hugetable nologging;
declare
  l_cnt number;
  l_rows number :=&1;
  begin
  insert /*+ append */
    into hugetable
    select rownum,a.*
    from all_objects a
    where rownum <= l_rows ;
    l_cnt := sql%rowcount;
    commit;
    while(l_cnt < l_rows)
    loop
      insert /*+ append */ into hugetable
        select rownum+l_cnt,
        owner,object_name,subobject_name,object_id,data_object_id,
        object_type,created,last_ddl_time,timestamp,status,
        temporary,generated,secondary,namespace,edition_name
        from hugetable
        where rownum <= l_rows - l_cnt;
        l_cnt := l_cnt+sql%rowcount;
        commit;
    end loop;
  end;
  /
  alter table hugetable add constraint
  hugetable_pk primary key(id);
  prompt 'end of procedure '
  exec dbms_stats.gather_table_stats(user,'hugetable',estimate_percent => 1);
select count(*) from hugetable ;

然后，就是你需要配置你得sqlplus,格式化：

set serveroutput on size unlimited
set trimpool on
set long 5000
set linesize 100
set pagesize 9999999
set plan_plus_exp format a80
set termout off
define gname=idle
column global_name new_Value gname
select lower(user)||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name
from (select global_name,instr(global_name,'.') dot from global_name);
set sqlprompt '&gname'
set termout on

pagesize: 多少N打印一次列标题

trimpool:对spool不进行trim，使其长度为linesize

linesize: 行宽

serveroutput:启用DBMS_OUTPUT输出

column plan_plus_exp format a80:设置explain的输出为80

long: clob,lob的默认大小为5000

sqlprompt:我是谁，在哪里， 一旦你conn,还是需要你show user知道你是谁.

trace的使用：

H:\app\kinfinger\product\11.2.0\dbhome_1\RDBMS\ADMIN
@utlxplan
grant all on plan_table to public
set autotrace on
set autotrace on explain
set autotrace on statistics
set autotrace only
set autotrace off
查看plan_table：
set autotrace traceonly
select * from hugetable;
执行计划
----------------------------------------------------------
Plan hash value: 4224224188

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9975 | 886K| 38 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| HUGETABLE | 9975 | 886K| 38 (0)| 00:00:01 |
 select * from hugetable where id=5;
执行计划
----------------------------------------------------------
Plan hash value: 232715781

--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1 | 91 | 2 (0)
| 00:00:01 |
| 1 | TABLE