K刀论坛

首页 / 编程技术 / 教程详解

oracle数据库：配置环境1

时间:2014-04-17 13:26 来源: 我爱IT技术网作者:微风

0、代码

Home » Databases » Oracle » Expert Oracle Database Architecture

Expert Oracle Database Architecture

Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions

2nd Edition

By Thomas Kyte

1、如何正确地建立SCOTT/tiger演示模式

关于执行脚本：书上说是cd [ORACLE_HOME]/sqlplus/demo，连接后运行demobld.sql，怎么在10g/11g下都没有这目录？

百度之后找到11g下的解决方案

(1)cd &ORACLE_HOME/rdbms/admin

(2)以任意用户身份连接后运行utlsampl.sql

2、需要建立和运行的环境；

可以将login.sql文件拷贝到oracle用户的当前目录下（home/oracle）或者设置SQLPATH环境变量，指向login.sql所在的目录。
如果仅将login.sql文件拷贝到oracle用户的当前目录下（home/oracle），仅在home/oracle目录sqlplus时login.sql生效?
SQL*Plus在启动的时候会自动查找运行两个脚本文件：glogin.sql和login.sql

glogin.sql是sqlplus全局登录的的profile，是ORACLE自带的登录脚本文件，他的路径是固定的，即$ORACLE_HOME/sqlplus/admin。当用户启动sqlplus的时候，会从这个固定的路径加载glogin.sql，不过一般来讲，我们不用关心glogin.sql文件

login.sql是用户登录的profile，sqlplus加载glogin.sql之后会查找并试图加载login.sql，sqlplus先从当前路径查找login.sql文件，如果找到了就加载这个文件并停止继续查找；如果当前路径不存在这个脚本，则继续从环境变量$SQLPATH指定的路径查找，如果找到了login.sql则加载之，之后不再继续查找；如果$SQLPATH中也不存在login.sql，则停止查找
在.bash_profile.orcl中添加$SQLPATH

login.sql

define _editor=vi

set serveroutput on size 1000000

set trimspool on

set long 5000

set linesize 100

set pagesize 9999

column plan_plus_exp format a80

column global_name new_value gname

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

3、如何配置SQL*Plus工具AUTOTRACE;

AUTOTRACE是SQL*Plus中的一个工具，可以显示所执行查询的解释计划explain plan以及所用的资源

[oracle@vcenteroracle admin]$ more plustrce.sql

--

-- NAME

-- plustrce.sql

--

-- DESCRIPTION

-- Creates a role with access to Dynamic Performance Tables

-- for the SQL*Plus SET AUTOTRACE ... STATISTICS command.

-- After this script has been run, each user requiring access to

-- the AUTOTRACE feature should be granted the PLUSTRACE role by

-- the DBA.

--

-- USAGE

-- sqlplus "sys/knl_test7 as sysdba" @plustrce

--

-- Catalog.sql must have been run before this file is run.

-- This file must be run while connected to a DBA schema.

set echo on

drop role plustrace;

create role plustrace;

grant select on v_$sesstat to plustrace;

grant select on v_$statname to plustrace;

grant select on v_$mystat to plustrace;

grant plustrace to dba with admin option;

set echo off

[oracle@vcenteroracle admin]$ pwd

/home/oracle/app/oracle/product/11.2.0/db_1/sqlplus/admin

[oracle@vcenteroracle admin]$ more utlxplan.sql

rem

rem $Header: utlxplan.sql 08-may-2004.12:53:19 bdagevil Exp $ xplainpl.sql

rem

Rem NAME

REM UTLXPLAN.SQL

Rem FUNCTION

Rem NOTES

Rem MODIFIED

Rem bdagevil 05/08/04 - add other_xml column

Rem bdagevil 06/18/03 - rename hint alias to object_alias

Rem ddas 06/03/03 - increase size of hint alias column

Rem bdagevil 02/13/03 - add plan_id and depth column

Rem ddas 01/17/03 - add query_block and hint_alias columns

Rem ddas 11/04/02 - revert timestamp column to DATE (PL/SQL problem)

Rem ddas 10/28/02 - change type of timestamp column to TIMESTAMP

Rem ddas 10/03/02 - add estimated_time column

Rem mzait 04/16/02 - add row vector to the plan table

Rem mzait 10/26/01 - add keys and filter predicates to the plan table

Rem ddas 05/05/00 - increase length of options column

Rem ddas 04/17/00 - add CPU, I/O cost, temp_space columns

Rem mzait 02/19/98 - add distribution method column

Rem ddas 05/17/96 - change search_columns to number

Rem achaudhr 07/23/95 - PTI: Add columns partition_{start, stop, id}

Rem glumpkin 08/25/94 - new optimizer fields

Rem jcohen 11/05/93 - merge changes from branch 1.1.710.1 - 9/24

Rem jcohen 09/24/93 - #163783 add optimizer column

Rem glumpkin 10/25/92 - Renamed from XPLAINPL.SQL

Rem jcohen 05/22/92 - #79645 - set node width to 128 (M_XDBI in gendef)

Rem rlim 04/29/91 - change char to varchar2

Rem Peeler 10/19/88 - Creation

Rem

Rem This is the format for the table that is used by the EXPLAIN PLAN

Rem statement. The explain statement requires the presence of this

Rem table in order to store the descriptions of the row sources.

create table PLAN_TABLE (

statement_id varchar2(30),

plan_id number,

timestamp date,

remarks varchar2(4000),

operation varchar2(30),

options varchar2(255),

object_node varchar2(128),

object_owner varchar2(30),

object_name varchar2(30),

object_alias varchar2(65),

object_instance numeric,

object_type varchar2(30),

optimizer varchar2(255),

search_columns number,

id numeric,

parent_id numeric,

depth numeric,

position numeric,

cost numeric,

cardinality numeric,

bytes numeric,

other_tag varchar2(255),

partition_start varchar2(255),

partition_stop varchar2(255),

partition_id numeric,

other long,

distribution varchar2(30),

cpu_cost numeric,

io_cost numeric,

temp_space numeric,

access_predicates varchar2(4000),

filter_predicates varchar2(4000),

projection varchar2(4000),

time numeric,

qblock_name varchar2(30),

other_xml clob

);

[oracle@vcenteroracle admin]$ pwd

/home/oracle/app/oracle/product/11.2.0/db_1/rdbms/admin

运行CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE;时报错ORA-00955时

SQL> SELECT * FROM SYS.ALL_SYNONYMS WHERE SYNONYM_NAME='PLAN_TABLE';

通过设置AUTOTRACE 系统变量可以控制AUTOTRACE报告
SET AUTOTRACE OFF:不生成AUTOTRACE报告。这是默认设置。
SET AUTOTRACE ON EXPLAIN:AUTOTRACE报告只显示优化器执行路径。
SET AUTOTRACE ON STATISTICS:AUTOTRACE报告只显示SQL语句的执行统计信息
SET AUTOTRACE ON:AUTOTRACE报告既包括优化器执行路径，又包括SQL语句的执行统计信息。
SET AUTOTRACE TRACEONLY: 这与SET AUTOTRACE ON类似，但是不显示用户的查询输出（如果有的话）

本文来源我爱IT技术网 http://www.52ij.com/jishu/5125.html 转载请保留链接。

评论列表（网友评论仅供网友表达个人看法，并不表明本站同意其观点或证实其描述）

oracle数据库：配置环境1

最新文章

相关文章