ORACLE之常用FAQ

 

ORACLE之常用FAQ V1.0
第一部分、SQL&PL/SQL
##########[Q]
么样查询特殊字符,如通配符%_
**********[A]select * from table where name like 'A/_%' escape '/'

##########[Q]
如何插入引号到数据表中
**********[A]
可以用ASCII码处理,其它特殊字符如&也一,如
insert into t values('i'||chr(39)||'m'); -- chr(39)
代表字符'
或者用两个引号表示一个
or insert into t values('I''m'); --
两个''可以表示一个'

##########[Q]
样设置事一致性
**********[A]set transaction [isolation level] read committed;
认语一致性
set transaction [isolation level] serializable;
read only;
务级一致性

##########[Q]
么样利用游更新数据
**********[A]cursor c1 is
select * from tablename
where name is null for update [of column]
……
update tablename set column = ……
where current of c1;

##########[Q]
自定异常
**********[A] pragma_exception_init(exception_name,error_number);
如果立即抛出异常
raise_application_error(error_number,error_msg,true|false);
其中number-20000-20999错误信息最2048B
异常
SQLCODE
错误
SQLERRM
错误信息

##########[Q]
制与十六制的转换
**********[A]8i
以上版本:
to_char(100,'XX')
to_number('4D','XX')
8i
以下的制之转换参考如下脚本
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_dec is null or p_base is null ) then
return null;
end if;
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_str is null or p_from_base is null ) then
return null;
end if;
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_dec;
/

##########[Q]
能不能介SYS_CONTEXT详细用法
**********[A]
利用以下的查询,你就明白了
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual

##########[Q]
么获得今天是星期几,还关于其它日期函数用法
**********[A]
可以用to_char来解决,如
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
取之前可以置日期言,如
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
可以在函数中指定
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
其它更多用法,可以参考to_charto_date函数
得完整的时间格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
随便介几个其它函数的用法:
本月的天数
SELECT to_char(last_day(SYSDATE),'dd') days FROM dual
今年的天数
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual
下个星期一的日期
SELECT Next_day(SYSDATE,'monday') FROM dual

##########[Q]
随机抽取前N记录问题
**********[A]8i
以上版本
select * from (select * from tablename order by sys_guid()) where rownum < N;
select * from (select * from tablename order by dbms_random.value) where rownum< N;
注:dbms_random包需要手工安装,位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value(100,200)
可以100200的随机数

##########[Q]
抽取从N行到M行的记录,如从20行到30行的记录
**********[A]select * from (select rownum id,t.* from table where ……
and rownum <= 30) where id > 20;

##########[Q]
么样抽取重复记录
**********[A]select * from table t1 where where t1.rowed !=
(select max(rowed) from table t2
where t1.id=t2.id and t1.name=t2.name)
或者
select count(*), t.col_a,t.col_b from table t
group by col_a,col_b
having count(*)>1
如果想除重复记录,可以把第一个句的select换为delete

##########[Q]
么样设置自治事
**********[A]8i
以上版本,不影响主事
pragma autonomous_transaction;
……
commit|rollback;

##########[Q]
么样程中停指定时间
**********[A]DBMS_LOCK
包的sleep
如:dbms_lock.sleep(5);表示5秒。

##########[Q]
么样快速算事时间与日志量
**********[A]
可以采用似如下的脚本
DECLARE
start_time NUMBER;
end_time NUMBER;
start_redo_size NUMBER;
end_redo_size NUMBER;
BEGIN
start_time := dbms_utility.get_time;
SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
--transaction start
INSERT INTO t1
SELECT * FROM All_Objects;
--other dml statement
COMMIT;
end_time := dbms_utility.get_time;
SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
AND s.NAME='redo size';
dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');
dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');
END;

##########[Q]
样创临时
**********[A]8i
以上版本
create global temporary tablename(column list)
on commit preserve rows; --
提交保留数据话临时
on commit delete rows; --
提交除数据务临时
临时是相于会的,的会看不到的数据。

##########[Q]
么样PL/SQLDDL
**********[A]1
8i以下版本dbms_sql
2
8i以上版本可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');

##########[Q]
么样获IP地址
**********[A]
(817以上)utl_inaddr.get_host_address
端:sys_context('userenv','ip_address')

##########[Q]
么样加密存储过
**********[A]
wrap命令,如(假定你的存储过程保存a.sql
wrap iname=a.sql
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing a.sql to a.plb
提示a.sql转换为a.plb就是加密了的脚本,a.plb即可生成加密了的存储过

##########[Q]
么样ORACLE中定运行存储过
**********[A]
可以利用dbms_job包来定运行作,如行存储过程,一个简单的例子,提交一个作
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');
commit;
END;
之后,就可以用以下查询提交的作
select * from user_jobs;

##########[Q]
么样从数据得毫秒
**********[A]9i
以上版本,有一个timestamp得毫秒,如
SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1,
to_char(current_timestamp) time2 from dual;

TIME1 TIME2
----------------------------- ----------------------------------------------------------------
2003-10-24 10:48:45.656000 24-OCT-03 10.48.45.656000 AM +08:00
可以看到,毫秒在to_char对应的是FF
8i
以上版本可以建一个如下的java函数
SQL>create or replace and compile
java source
named "MyTimestamp"
as
import java.lang.String;
import java.sql.Timestamp;

public class MyTimestamp
{
public static String getTimestamp()
{
return(new Timestamp(System.currentTimeMillis())).toString();
}
};
SQL>java created.
注:注意java法,注意大小写
SQL>create or replace function my_timestamp return varchar2
as language java
name 'MyTimestamp.getTimestamp() return java.lang.String';
/
SQL>function created.
SQL>select my_timestamp,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ORACLE_TIME from dual;
MY_TIMESTAMP ORACLE_TIME
------------------------ -------------------
2003-03-17 19:15:59.688 2003-03-17 19:15:59
如果只想1/100(hsecs)可以利用dbms_utility.get_time

##########[Q]
如果存在就更新,不存在就插入可以用一个实现吗
**********[A]9i
支持了,是Merge,但是只支持select查询
如果是条数据记录,可以写作select …… from dual的子查询

MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;

MERGE INTO course c
USING (SELECT course_name, period,
course_hours
FROM course_updates) cu
ON (c.course_name = cu.course_name
AND c.period = cu.period)
WHEN MATCHED THEN
UPDATE
SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
INSERT (c.course_name, c.period,
c.course_hours)
VALUES (cu.course_name, cu.period,
cu.course_hours);

##########[Q]
么实现,右与外
**********[A]
9i以前可以这么:

select a.id,a.name,b.address from a,b
where a.id=b.id(+)
:
select a.id,a.name,b.address from a,b
where a.id(+)=b.id

SELECT a.id,a.name,b.address
FROM a,b
WHERE a.id = b.id(+)
UNION
SELECT b.id,'' name,b.address
FROM b
WHERE NOT EXISTS (
SELECT * FROM a
WHERE a.id = b.id);
9i以上,已经开始支持SQL99准,所以,以上句可以写成:
内部联结
select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
where other_clause

select a.id,a.name,b.address
from a left outer join b on a.id=b.id
where other_clause

select a.id,a.name,b.address
from a right outer join b on a.id=b.id
where other_clause

select a.id,a.name,b.address
from a full outer join b on a.id=b.id
where other_clause
or
select a.id,a.name,b.address
from a full outer join b using (id)
where other_clause

##########[Q]
么实现一条记录根据条件多表插入
**********[A]9i
以上可以通Insert all句完成,仅仅是一个句,如:
INSERT ALL
WHEN (id=1) THEN
INTO table_1 (id, name)
values(id,name)
WHEN (id=2) THEN
INTO table_2 (id, name)
values(id,name)
ELSE
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
如果没有条件的完成个表的插入,如
INSERT ALL
INTO table_1 (id, name)
values(id,name)
INTO table_2 (id, name)
values(id,name)
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;

##########[Q]
如何实现行列转换
**********[A]1
、固定列数的行列转换

student subject grade
---------------------------
student1
80
student1
数学 70
student1
60
student2
90
student2
数学 80
student2
100
……
转换为
数学
student1 80 70 60
student2 90 80 100
……
句如下:
select student,sum(decode(subject,'
', grade,null)) "",
sum(decode(subject,'
数学', grade,null)) "数学",
sum(decode(subject,'
', grade,null)) ""
from table
group by student

2
、不定列行列转换

c1 c2
--------------
1

1

1

2

2

3

……
转换为
1
我是
2
知道
3

型的转换借助于PL/SQL来完成,一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;
即可

##########[Q]
么样实现取前N记录
**********[A]8i
以上版本,利用分析函数
个部薪水前三名的工或个班成前三名的学生。
Select * from
(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
from emp)
where rn<=3

##########[Q]
么样把相邻记录合并到一条记录
**********[A]8i
以上版本,分析函数laglead可以提取后一条或前一天记录到本记录
Select deptno,ename,hiredate,lag(hiredate,1,null) over
(partition by deptno order by hiredate,ename) last_hire
from emp
order by depno,hiredate

##########[Q]
如何取得一列中第N大的
**********[A]select * from
(select t.*,dense_rank() over (order by t2 desc) rank from t)
where rank = &N;

##########[Q]
么样查询内容出到文本
**********[A]
spool
sqlplus –s " / as sysdba" <<EOF
set heading off
set feedback off
spool temp.txt
  select * from tab;
dbms_output.put_line(‘test’);
spool off
exit
EOF

##########[Q]
如何在SQL*PLUS境中OS命令?
**********[A]
比如入了SQLPLUS,启了数据,忽然想起没有启,此不用退出SQLPLUS,也不用另外起一个命令行窗口,直接入:
SQL> host lsntctl start
或者unix/linux平台下
SQL>!
windows
平台下
SQL>$
总结HOST 可以直接OS命令。
注:cd命令无法正确行。

##########[Q]
么设置存储过程的用者
**********[A]
普通存储过程都是所有者限,如果想用者限,参考如下
create or replace
procedure ……()
AUTHID CURRENT_USER
As
begin
……
end;

##########[Q]
快速得用个表或表分区的记录
**********[A]
可以分析,然后查询user_tables字典,或者采用如下脚本即可
SET SERVEROUTPUT ON SIZE 20000
DECLARE
miCount INTEGER;
BEGIN
FOR c_tab IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miCount;
dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));
--if it is partition table
SELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name;
IF miCount >0 THEN
FOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOP
EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'

INTO miCount;
dbms_output.put_line(' '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));
END LOOP;
END IF;
END LOOP;
END;

**********[A]
Oracle发邮
##########[Q]
可以利用utl_smtp发邮件,以下是一个简单邮件的例子程序
/****************************************************************************
parameter: Rcpter in varchar2
接收者
Mail_Content in Varchar2
件内容
desc: ·
件到指定
·
只能指定一个箱,如果需要送到多个箱,需要另外的助程序
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
mail_content IN VARCHAR2)
IS
conn utl_smtp.connection;
--write title
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
END;
BEGIN
--opne connect
conn := utl_smtp.open_connection('smtp.com');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
--write title
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" <'||rcpter||'>');
send_header('Subject', 'DB Info');
--write mail content
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
--close connect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
WHEN OTHERS THEN
NULL;
END sp_send_mail;


**********[A]
么样Oracle中写操作系文件,如写日志
##########[Q]
可以利用utl_file包,但是,在此之前,要注意置好Utl_file_dir初始化参数
/**************************************************************************
parameter:textContext in varchar2
日志内容
desc: ·
写日志,把内容到服器指定目
·
配置Utl_file_dir初始化参数,并保日志路径与Utl_file_dir路径一致或者是其中一个
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
IS
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--open file
write_file_name := 'db_alert.log';
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END sp_Write_log;

第二部分、ORACLE构架体系
##########[Q]ORACLE
的有那些数据
**********[A]
的数据型有
CHAR
固定度字符域,最大度可达2000个字
NCHAR
多字字符集的固定度字符域,度随字符集而定,最多2000个字符或2000个字
VARCHAR2
变长度字符域,最大度可达4000个字符
NVARCHAR2
多字字符集的可变长度字符域,度随字符集而定,最多4000个字符或4000个字
DATE
用于存全部日期的固定(7个字)字符域,时间日期的一部分存其中。除非
过设init.ora文件的NLS_DATE_FORMAT参数来取代日期格式,否则查询时,日期以
DD-MON-YY
格式表示,如13-APR-99表示1999.4.13
NUMBER
变长度数列,允许值为0、正数和数。NUMBER通常以4个字或更少的字,最多21
LONG
变长度字符域,最大度可到2GB
RAW
表示二制数据的可变长度字符域,最长为2000个字
LONGRAW
表示二制数据的可变长度字符域,最长为2GB
MLSLABEL
只用于TrustedOracle个数据行使用25个字
BLOB
制大象,最大4GB
CLOB
字符大象,最大4GB
NCLOB
多字字符集的CLOB数据型,最大4GB
BFILE
外部二制文件,大小由操作系决定
ROWID
表示RowID的二制数据Oracle8RowID的数值为10个字,在Oracle7中使用的限定
RowID
格式6个字
UROWID
用于数据址的二制数据,最大4000个字

##########[Q]Oracle
有哪些常见关键字,不能被用于象名
**********[A]
8i版本例,一般保留关键字不能用做象名
ACCESS ADD ALL ALTER AND ANY AS ASC AUDIT BETWEEN BY CHAR CHECK CLUSTER COLUMN COMMENT COMPRESS CONNECT CREATE CURRENT DATE DECIMAL DEFAULT DELETE DESC DISTINCT DROP ELSE EXCLUSIVE EXISTS FILE FLOAT FOR FROM GRANT GROUP HAVING IDENTIFIED IMMEDIATE IN INCREMENT INDEX INITIAL INSERT INTEGER INTERSECT INTO IS LEVEL LIKE LOCK LONG MAXEXTENTS MINUS MLSLABEL MODE MODIFY NOAUDIT NOCOMPRESS NOT NOWAIT NULL NUMBER OF OFFLINE ON ONLINE OPTION OR ORDER PCTFREE PRIOR PRIVILEGES PUBLIC RAW RENAME RESOURCE REVOKE ROW ROWID ROWNUM ROWS SELECT SESSION SET SHARE SIZE SMALLINT START SUCCESSFUL SYNONYM SYSDATE TABLE THEN TO TRIGGER UID UNION UNIQUE UPDATE USER VALIDATE VALUES VARCHAR VARCHAR2 VIEW WHENEVER WHERE WITH
详细信息可以v$reserved_words视图

##########[Q]
么查看数据版本
**********[A]select * from v$version
包含版本信息,核心版本信息,位数信息(32位或64)
至于位数信息,在linux/unix平台上,可以通file看,如
file $ORACLE_HOME/bin/oracle

##########[Q]
么查看数据参数
**********[A]show parameter
参数名
如通show parameter spfile可以9i是否使用spfile文件
或者select * from v$parameter
除了部分参数,Oracle有大量含参数,可以通如下:
SELECT NAME
,VALUE
,decode(isdefault, 'TRUE','Y','N') as "Default"
,decode(ISEM,'TRUE','Y','N') as SesMod
,decode(ISYM,'IMMEDIATE', 'I',
'DEFERRED', 'D',
'FALSE', 'N') as SysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N') as Modified
,decode(IADJ,'TRUE','Y','N') as Adjusted
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppi***/256,1),1,'TRUE','FALSE') as ISEM
,decode(bitand(ksppi***/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = '_'
AND x.inst_id = USERENV('Instance')
)
ORDER BY NAME

##########[Q]
么样查看数据字符集
**********[A]
数据器字符集select * from nls_database_parameters,其来源于props$,是表示数据的字符集。
端字符集select * from nls_instance_parameters,其来源于v$parameter
表示客端的字符集的置,可能是参数文件,量或者是注册表
字符集 select * from nls_session_parameters,其来源于v$nls_parameters,表示会自己的置,可能是会量或者是alter session完成,如果会没有特殊的置,将与nls_instance_parameters一致。
端的字符集要求与服器一致,才能正确示数据的非Ascii字符。如果多个置存在的候,alter session>>注册表>参数文件
字符集要求一致,但是置却可以不同,置建用英文。如字符集是zhs16gbknls_lang可以是American_America.zhs16gbk

##########[Q]
么样修改字符集
**********[A]8i
以上版本可以通alter database来修改字符集,但也只限于子集到超集,不建修改props$表,将可能错误
Startup nomount;
Alter database mount exclusive;
Alter system enable restricted session;
Alter system set job_queue_process=0;
Alter database open;
Alter database character set zhs16gbk;

##########[Q]
建立基于函数索引
**********[A]8i
以上版本,确保
Query_rewrite_enabled=true
Query_rewrite_integrity=trusted
Compatible=8.1.0
以上
Create index indexname on table (function(field));

##########[Q]
么样表或表分区
**********[A]
表的
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
分区的
alter table tablename move (partition partname)
[update global indexes]
之后之后必重建索引
Alter index indexname rebuild
如果表有Lob段,那正常的Alter不能移Lob段到的表空,而仅仅是移了表段,可以采用如下的方法移Lob
alter table tablename move
lob(lobsegname) store as (tablespace newts);

##########[Q]
么获得当前的SCN
**********[A]9i
以下版本
select max(ktuxescnw*power(2,32)+ktuxescnb) from x$ktuxe;
如果是9i以上版本,可以通以下
select dbms_flashback.get_system_change_number from dual;

##########[Q]ROWID
构与
**********[A]8
以上版本的ROWID
OOOOOOFFFBBBBBBRRR
8
以下ROWID成(也叫受限Rowid
BBBBBBBB.RRRR.FFFF
其中,OIDF是文件IDBIDR是行ID
如果我们查询一个表的ROWID,根据其中的信息,可以知道表确切占用了多少个而知道占用了多少数据空(此数据空不等于表的分配空

##########[Q]
么样获象的DDL
**********[A]
第三方工具就不了主要一下9i以上版本的dbms_metadata
1
象的DDL
set heading off
set echo off
set feedback off
set pages off
set long 90000
select dbms_metadata.get_ddl('TABLE','TABLENAME','SCAME') from dual;
如果取整个用的脚本,可以用如下
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
当然,如果是索引,需要修改相tableindex

##########[Q]
如何束的索引在的表空
**********[A]1
、先建索引,再
2
、利用如下
create table test
(c1 number constraint pk_c1_id primary key
using index tablespace useridex,
c2 varchar2(10)
) tablespace userdate;

##########[Q]
知道那些表没有建立主
**********[A]
一般的情况下,表的主是必要的,没有主的表可以是不符合设计规范的。
SELECT table_name
FROM User_tables t
WHERE NOT EXISTS
(SELECT table_name
FROM User_constraints c
WHERE constraint_type = 'P'
AND t.table_name=c.table_name)
其它相数据字典解
user_tables

user_tab_columns
表的列
user_constraints

user_cons_columns
束与列的
user_indexes
索引
user_ind_columns
索引与列的

##########[Q]dbms_output
提示冲区不,怎增加
**********[A]dbms_output.enable(20000);
另外,如果dbms_output的信息不能示,
需要
set serveroutput on

##########[Q]
么样修改表的列名
**********[A]9i
以上版本可以采用rname命令
ALTER TABLE UserName.TabName
RENAME COLUMN SourceColumn TO DestColumn
9i
以下版本可以采用create table …… as select * from SourceTable的方式。
另外,8i以上可以支持除列了
ALTER TABLE UserName.TabName
SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName
DROP (ColumnName) CASCADE CONSTRAINTS

##########[Q]
么样给sqlplus安装帮助
**********[A]SQLPLUS
的帮助必手工安装,shell脚本$ORACLE_HOME/bin/helpins
在安装之前,必SYSTEM_PASS量,如:
$ setenv SYSTEM_PASS SYSTEM/MANAGER
$ helpins
如果不该环量,将在运行脚本的候提示
当然,除了shell脚本,可以利用sql脚本安装,那就不用量了,但是,我system
$ sqlplus system/manager
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql
安装之后,你就可以象如下的方法使用帮助了
SQL> help index

##########[Q]
么样快速下Oracle
**********[A]
得下器地址,在http面上有
ftp://updates.oracle.com
然后用ftp,用名与密metalink的用名与密
如我知道了丁号3095277 (9204丁集)
ftp> cd 3095277
250 Changed directory OK.
ftp> ls
200 PORT command OK.
150 Opening data connection for file listing.
p3095277_9204_AIX64-5L.zip
p3095277_9204_AIX64.zip
……
p3095277_9204_WINNT.zip
226 Listing complete. Data connection has been closed.
ftp: 208 bytes received in 0.02Seconds 13.00Kbytes/sec.
ftp>
知道了个信息,我用用flashget,网络蚂蚁就可以下了。
添加如下
ftp://updates.oracle.com/3095277/p3...04_AIX64-5L.zip
或替后面的部分所需要的内容
注意,如果是flashget,网络蚂蚁请输认证名及密,就是你的metalink的用名与密

##########[Q]
如何移数据文件
**********[A]1
关闭数据,利用os
a.shutdown immediate
关闭数据
b.
os下拷数据文件到新的地点
c.Startup mount
mount
d.Alter database rename datafile '
老文件' to '新文件';
e.Alter database open;
数据
2
、利用Rman机操作
RMAN> sql "alter database datafile ''file name'' offline";
RMAN> run {
2> copy datafile 'old file location'
3> to 'new file location';
4> switch datafile ' old file location'
5> to datafilecopy ' new file location';
6> }
RMAN> sql "alter database datafile ''file name'' online";
明:利用OS也可以机操作,不关闭数据,与rman步骤,利用rman与利用os的原理一,在rmancopy是拷数据文件,相当于OScp,而switch相当于alter database rename,用来更新控制文件。

##########[Q]
如果管理机日志与成
**********[A]
以下是常操作,如果在OPA/RAC下注意线程号
增加一个日志文件
Alter database add logfile [group n] '
文件全名' size 10M;
上增加一个成
Alter database add logfile member '
文件全名' to group n;
除一个日志成
Alter database drop logfile member '
文件全名';
除整个日志
Alter database drop logfile group n;

##########[Q]
么样计REDO BLOCK的大小
**********[A]
算方法(redo size + redo wastage) / redo blocks written + 16
具体如下例子
SQL> select name ,value from v$sysstat where name like '%redo%';
NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 2
redo synch time 0
redo entries 76
redo size 19412
redo buffer allocation retries 0
redo wastage 5884
redo writer latching time 0
redo writes 22
redo blocks written 51
redo write time 0
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
SQL> select (19412+5884)/51 + 16 '"Redo black(byte)" from dual;
Redo black(byte)
------------------
512

##########[Q]
控制文件包含哪些基本内容
**********[A]
控制文件主要包含如下条目,可以通dump控制文件内容看到
DATABASE ENTRY
CHECKPOINT PROGRESS RECORDS
REDO THREAD RECORDS
LOG FILE RECORDS
DATA FILE RECORDS
TEMP FILE RECORDS
TABLESPACE RECORDS
LOG FILE HISTORY RECORDS
OFFLINE RANGE RECORDS
ARCHIVED LOG RECORDS
BACKUP SET RECORDS
BACKUP PIECE RECORDS
BACKUP DATAFILE RECORDS
BACKUP LOG RECORDS
DATAFILE COPY RECORDS
BACKUP DATAFILE CORRUPTION RECORDS
DATAFILE COPY CORRUPTION RECORDS
DELETION RECORDS
PROXY COPY RECORDS
INCARNATION RECORDS

##########[Q]
如果发现表中有坏,如何索其它未坏的数据
**********[A]
首先需要找到坏ID(可以运行dbverify实现),假设为,假定文件编码为。运行下面的查询查找段名:
SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where
file_id =
AND between block_id and (block_id + blocks - 1)
一旦找到坏段名称,若段是一个表,最好建立一个临时表,存放好的数据。若段是索引,则删除它,再重建。
create table good_table
as
select from bad_table where rowid not in
(select rowid
from bad_table where substr(rowid,10,6) = )
里要注意8以前的受限ROWIDROWID的差
可以使用断事件10231
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';
建一个临时good_table的表中除坏的数据都索出来
SQL>CREATE TABLE good_table as select * from bad_table;
最后关闭诊断事件
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context off ';
ROWID构,可以参考dbms_rowid.rowid_create函数。

##########[Q]
建了数据的所有用,我可以些用户吗
**********[A]ORACLE
数据库创建的候,建了一系列默的用和表空,以下是他的列表
·SYS/CHANGE_ON_INSTALL or INTERNAL
,数据字典所有者,超级权限所有者(SYSDBA)
建脚本:?/rdbms/admin/sql.bsq and various cat*.sql
议创建后立即修改密
此用不能被
·SYSTEM/MANAGER
数据管理用DBA角色
建脚本:?/rdbms/admin/sql.bsq
议创建后立即修改密
此用不能被
·OUTLN/OUTLN
划的存
建脚本:?/rdbms/admin/sql.bsq
议创建后立即修改密
此用不能被
---------------------------------------------------
·SCOTT/TIGER, ADAMS/WOOD, JONES/STEEL, CLARK/CLOTH and BLAKE/PAPER.
实验测试,含有例表EMPDEPT
建脚本:?/rdbms/admin/utlsampl.sql
可以修改密
可以被除,在境建议删除或
·HR/HR (Human Resources), OE/OE (Order Entry), SH/SH (Sales History).
实验测试,含有例表EMPLOYEESDEPARTMENTS
建脚本:?/demo/schema/mksample.sql
可以修改密
可以被除,在境建议删除或
·DBSNMP/DBSNMP
Oracle Intelligent agent
建脚本:?/rdbms/admin/catsnmp.sql, called from catalog.sql
可以改--需要放置新密snmp_rw.ora文件
如果不需要Intelligent Agents,可以
---------------------------------------------------
以下用都是可安装用,如果不需要,就不需要安装
·CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge)
管理用
建脚本:?/ctx/admin/dr0csys.sql
·TRACESVR/TRACE
Oracle Trace server
建脚本:?/rdbms/admin/otrcsvr.sql
·ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
建脚本:?/ord/admin/ordinst.sql
·ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
建脚本:?/ord/admin/ordinst.sql
·DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
建脚本:?/ds/sql/dssys_init.sql
·MDSYS/MDSYS
Oracle Spatial administrator user
建脚本:?/ord/admin/ordinst.sql
·AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
建脚本:?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql
·PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/UTLESTAT
建脚本:?/rdbms/admin/statscre.sql

第三部分、份与恢

##########[Q]
如何/关闭归
**********[A]
如果档,log_archive_start=true启自动归档,否只能手工档,如果是关闭档,则设参数false
注意:如果是OPS/RAC境,需要先把parallel_server = true掉,然后行如下步骤,最后用个参数重新启
1

a.
关闭数据shutdown immediate
b. startup mount
c. alter database archivelog
d. alter database opne
2
、禁止
a.
关闭数据shutdown immediate
b. startup mount
c. alter database noarchivelog
d. alter database open
档信息可以通如下
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:/oracle/ora92/database/archive
Oldest online log sequence 131
Next log sequence to archive 133
Current log sequence 133

##########[Q]
样设置定时归
**********[A]9i
以上版本,保证归档的最小隔不超n
Archive_lag_target = n
位:秒0~7200

##########[Q]
不同版本怎么导/
**********[A]
出用低版本,入用当前版本
如果版本跨越太大,需要用到中版本

##########[Q]
不同的字符集之前怎么导数据
**********[A]a.
前条件是保证导/入符合其他字符集准,如客户环境与数据字符集一致。
b.修改dmp文件的23节为数据的字符集,注意要成十六制。
参考函数(以下函数中的ID是十制的):
nls_charset_name
根据字符集ID得字符集名称
nls_charset_id
根据字符集名称得字符集ID

##########[Q]
么样备份控制文件
**********[A]
线备一个二制的文件
alter database backup controlfile to '$BACKUP_DEPT/controlfile.000' [reuse];
文本文件方式
alter database backup controlfile to trace [resetlogs|noresetlogs];

##########[Q]
控制文件坏如何恢
**********[A]1
、如果是个控制文件
只需要关闭数据,拷一个好的数据文件覆盖掉坏的数据文件即可
或者是修改init.ora文件的相部分
2
、如果是失全部控制文件,需要建控制文件或从份恢
建控制文件的脚本可以通alter database backup controlfile to trace取。

##########[Q]
么样热备份一个表空
**********[A]Alter tablespace
名称 begin backup;
host cp
个表空的数据文件目的地;
Alter tablespace
名称 end backup;
如果是份多个表空或整个数据,只需要一个一个表空的操作下来就可以了。

##########[Q]
快速得到整个数据热备脚本
**********[A]
可以写一段似的脚本
SQL>set serveroutput on
begin
dbms_output.enable(10000);
for bk_ts in (select distinct t.ts#,t.name from v$tablespace t,v$datafile d where t.ts#=d.ts#) loop
dbms_output.put_line('--'||bk_ts.name);
dbms_output.put_line('alter tablespace '||bk_ts.name||' begin backup;');
for bk_file in (select file#,name from v$datafile where ts#=bk_ts.ts#) loop
dbms_output.put_line('host cp '||bk_file.name||' $BACKUP_DEPT/');
end loop;
dbms_output.put_line('alter tablespace '||bk_ts.name||' end backup;');
end loop;
end;
/

##########[Q]
失一个数据文件,但是没有份,怎么样数据
**********[A]
如果没有份只能是数据文件了,会致相的数据失。
SQL>startup mount
--ARCHIVELOG
模式命令
SQL>Alter database datafile 'file name' offline;
--NOARCHIVELOG
模式命令
SQL>Alter database datafile 'file name' offline drop;
SQLl>Alter database open;
注意:数据文件不能是系数据文件

##########[Q]
失一个数据文件,没有份但是有数据文件建以来的档怎
**********[A]
如下条件
a.
不能是系数据文件
b.
不能失控制文件
如果足以上条件,
SQL>startup mount
SQL>Alter database create datafile 'file name' as 'file name' size ... reuse;
SQL>recover datafile n; -
文件号
或者
SQL>recover datafile 'file name';
或者
SQL>recover database;
SQL>Alter database open;

##########[Q]
机日志坏如何恢
**********[A]1
、如果是非当前日志而且档,可以使用
Alter database clear logfile group n
建一个新的日志文件
如果日志没有档,需要用
Alter database clear unarchived logfile group n
2
、如果是当前日志坏,一般不能clear可能意味着失数据
如果有份,可以采用行不完全恢
如果没有份,可能只能用_allow_resetlogs_corruption=true制恢了,但是,这样的方法是不建的,最好在有Oracle support的指行。

##########[Q]
么样创RMAN
**********[A]
首先,建一个数据,一般都是RMAN,并recovery_catalog_owner角色
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
然后,用个用建恢
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
最后,你可以在恢注册目数据
rman catalog rman/rman target backdba/backdba
RMAN> register database;

##########[Q]
么样在恢候移数据文件,恢的地点
**********[A]
一个RMAN的例子
run {
set until time 'Jul 01 1999 00:05:00';
allocate channel d1 type disk;
set newname for datafile '/u04/oracle/prod/sys1prod.dbf'
to '/u02/oracle/prod/sys1prod.dbf';
set newname for datafile '/u04/oracle/prod/usr1prod.dbf'
to '/u02/oracle/prod/usr1prod.dbf';
set newname for datafile '/u04/oracle/prod/tmp1prod.dbf'
to '/u02/oracle/prod/tmp1prod.dbf';
restore controlfile to '/u02/oracle/prod/ctl1prod.ora';
replicate controlfile from '/u02/oracle/prod/ctl1prod.ora';
restore database;
sql "alter database mount";
switch datafile all;
recover database;
sql "alter database open resetlogs";
release channel d1;
}

##########[Q]
份片(backuppiece)中恢(restore)控制文件与数据文件
**********[A]
可以使用如下方法,在RMAN中恢复备份片的控制文件
restore controlfile from backuppiecefile;
如果是9i的自动备份,可以采用如下的方法
restore controlfile from autobackup;
但是,如果控制文件全部失,需要指定DBID,如SET DBID=?
动备份控制文件的默格式是%F个格式的形式
c-IIIIIIIIII-YYYYMMDD-QQ
,其中IIIIIIIIII就是DBID
至于恢(restore)数据文件,oracle 816始有个包dbms_backup_restore
nomount 下就可以行,可以 815甚至之前的份片,出来的文件用于恢
可以在SQLPLUS中运行,如下
SQL>startup nomount
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.deviceallocate('', params=>'');
6 dbms_backup_restore.restoresetdatafile;
7 dbms_backup_restore.restorecontrolfileto('E:/Oracle/oradata/penny/control01.ctl');
8 dbms_backup_restore.restoreDataFileto(1,'E:/Oracle/oradata/penny/system01.dbf');
9 dbms_backup_restore.restoreDataFileto(2,'E:/Oracle/oradata/penny/UNDOTBS01.DBF');
10 dbms_backup_restore.restoreDataFileto(3,'E:/ORACLE/ORADATA/PENNY/USERS01.DBF');
11 dbms_backup_restore.restorebackuppiece('D:/orabak/BACKUP_1_4_04F4IAJT.PENNY',done=>done);
12 END;
13 /
PL/SQL
程已成功完成。
SQL> alter database mount;

##########[Q]Rman
format格式中的%s似的西代表什
**********[A]
可以参考如下
%c
份片的拷
%d
数据名称
%D
位于月中的第几天 (DD)
%M
位于年中的第几月 (MM)
%F
一个基于DBID唯一的名称,个格式的形式c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据DBIDYYYYMMDD日期,QQ是一个1-256的序列
%n
数据名称,向右填到最大八个字符
%u
一个八个字符的名称代表份集与时间
%p
该备份集中的份片号,从1始到建的文件数
%U
一个唯一的文件名,代表%u_%p_%c
%s
份集的号
%t
份集时间
%T
年月日格式(YYYYMMDD)

##########[Q]
exec dbms_logmnr_d.build('Logminer.ora','file directory'),提示下超界,怎么办
**********[A]
完整错误信息如下,
SQL> exec dbms_logmnr_d.build('Logminer.ora','file directory')
BEGIN dbms_logmnr_d.build('Logminer.ora','file directory'); END;
*
ERROR
位于第 1 :
ORA-06532:
超出限制
ORA-06512:
"SYS.DBMS_LOGMNR_D", line 793
ORA-06512:
line 1
解决
1.
编辑位于"$ORACLE_HOME/rdbms/admin"下的文件"dbmslmd.sql"
:
TYPE col_desc_array IS VARRAY(513) OF col_description;

TYPE col_desc_array IS VARRAY(700) OF col_description;
并保存文件
2.
运行改后的脚本
SQLPLUS> Connect internal
SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
3.
重新编译该
SQLPLUS> alter package DBMS_LOGMNR_D compile body;

##########[Q]
execute dbms_logmnr.start_logmnr(DictFileName=>'DictFileName')提示ORA-01843:无效的月份,个是什原因
**********[A]
分析start_logmnr
PROCEDURE start_logmnr(
startScn IN NUMBER default 0 ,
endScn IN NUMBER default 0,
startTime IN DATE default TO_DATE('01-jan-1988','DD-MON-YYYY'),
endTime IN DATE default TO_DATE('01-jan-2988','DD-MON-YYYY'),
DictFileName IN VARCHAR2 default '',
Options IN BINARY_INTEGER default 0 );
可以知道,如果TO_DATE('01-jan-1988','DD-MON-YYYY'),将致以上错误
所以解决法可以
1
Alter session set NLS_LANGUAGE=American
2
、用似如下的方法
execute dbms_logmnr.start_logmnr (DictFileName=> 'f:/temp2/TESTDICT.ora', starttime => TO_DATE(
'01-01-1988','DD-MM-YYYY'), endTime=>TO_DATE('01-01-2988','DD-MM-YYYY'));

第四部分、性能
##########[Q]
如果置自跟踪
**********[A]
system
$ORACLE_HOME/rdbms/admin/utlxplan.sql划表
$ORACLE_HOME/sqlplus/admin/plustrce.sqlplustrace角色
如果想划表让每个用都能使用,
SQL>create public synonym plan_table for plan_table;
SQL> grant all on plan_table to public;
如果想跟踪的角色让每个用都能使用,
SQL> grant plustrace to public;
如下/停止跟踪
SET AUTOTRACE ON |OFF
| ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN

##########[Q]
如果跟踪自己的会或者是人的会
**********[A]
跟踪自己的会简单
Alter session set sql_trace true|false
Or
Exec dbms_session.set_sql_trace(TRUE);
如果跟踪人的会,需要用一个包
exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)
跟踪的信息在user_dump_dest 下可以找到或通如下脚本得文件名称(适用于Win境,如果是unix需要做一定修改)
SELECT p1.value||'/'||p2.value||'_ora_'||p.spid||'.ora' filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID')
最后,可以通Tkprof来解析跟踪文件,如
Tkprof
原文件文件 sys=n


##########[Q]
么设置整个数据跟踪
**********[A]
文档上的alter system set sql_trace=true是不成功的
但是可以通过设置事件来完成个工作,作用相等
alter system set events
'10046 trace name context forever,level 1';
如果关闭跟踪,可以用如下
alter system set events
'10046 trace name context off';
其中的level 1与上面的8都是跟踪级别
level 1
:跟踪SQL句,等于sql_trace=true
level 4
:包括量的详细信息
level 8
:包括等待事件
level 12
:包括量与等待事件

##########[Q]
么样根据OS程快速DB程信息与正在行的
**********[A]
有些候,我OS上操作,象TOP之后我得到的OS程,怎快速根据OS信息DB信息呢?
可以写如下脚本:
$more whoit.sh
#!/bin/sh
sqlplus /nolog <
connect / as sysdba
col machine format a30
col program format a40
set line 200
select sid,serial# ,username,osuser,machine,program,process,to_char(logon_time,'yyyy/mm/dd hh24:mi:ss')
from v/$session where paddr in
( select addr from v/$process where spid in($1));

select sql_text from v/$sqltext_with_newlines
where hash_value in
(select SQL_HASH_VALUE from v/$session where
paddr in (select addr from v/$process where spid=$1)
)
order by piece;

exit;
EOF
然后,我只要OS境下如下行即可
$./whoit.sh Spid

##########[Q]
么样分析表或索引
**********[A]
命令行方式可以采用analyze命令
Analyze table tablename compute statistics;
Analyze index|cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS
FOR TABLE
FOR ALL [LOCAL] INDEXES
FOR ALL [INDEXED] COLUMNS;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE
[CASCADE]|[INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [INTO TableName]
等等。
如果想分析整个用或数据可以采用工具包,可以并行分析
Dbms_utility(8i
以前的工具包)
Dbms_stats(8i
以后提供的工具包)

dbms_stats.gather_schema_stats(User,estimate_percent=>100,cascade=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
命令与工具包的一些总结
1
于分区表,建使用DBMS_STATS,而不是使用Analyze句。
a) 可以并行行,多个用,多个Table
b)
可以得到整个分区表的数据和个分区的数据。
c) 可以在不同级别Compute Statistics个分区,子分区,全表,所有分区
d)
可以倒出统计信息
e)
可以用收集统计信息
2
DBMS_STATS的缺点
a)
不能Validate Structure
b)
不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息,两个仍旧需要使用Analyze句。
c) DBMS_STATS
索引Analyze,因CascadeFalse,需要手工指定True
3
oracle 9里面的External TableAnalyze不能使用,只能使用DBMS_STATS来收集信息。

##########[Q]
么样快速重整索引
**********[A]
rebuild句,可以快速重整或移索引到的表空
rebuild
有重建整个索引数的功能,可以在不除原始索引的情况下改索引的存参数

alter index index_name rebuild tablespace ts_name
storage(……);
如果要快速重建整个用下的索引,可以用如下脚本,当然,需要根据你自己的情况做相修改
SQL> set heading off
SQL> set feedback off
SQL> spool d:/index.sql
SQL> SELECT 'alter index ' || index_name || ' rebuild '
||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'
FROM all_indexes
WHERE ( tablespace_name != 'INDEXES'
OR next_extent != ( 256 * 1024 )
)
AND owner = USER
SQL>spool off
另外一个合并索引的句是
alter index index_name coalesce
仅仅是合并索引中同一leaf block
消耗不大,于有些索引中存在大量空的情况下,有一些作用。

##########[Q]
如何使用Hint提示
**********[A]
select/delete/update后写/*+ hint */
select /*+ index(TABLE_NAME INDEX_NAME) */ col1...
注意/*+不能有空格
如用hint指定使用某个索引

select /*+ index(cbotab) */ col1 from cbotab;
select /*+ index(cbotab cbotab1) */ col1 from cbotab;
select /*+ index(a cbotab1) */ col1 from cbotab a;
其中
TABLE_NAME
是必要写的,且如果在查询中使用了表的名,在hint也要用表的名来代替表名;
INDEX_NAME
可以不必写,Oracle会根据统计值选一个索引;
如果索引名或表名写了,那hint就会被忽略;

##########[Q]
么样快速制表或者是插入数据
**********[A]
快速制表可以指定Nologging选项
如:Create table t1 nologging
as select * from t2;
快速插入数据可以指定append提示,但是需要注意
noarchivelog
模式下,默用了append就是nologging模式的。
archivelog下,需要把表置程Nologging模式。
insert /*+ append */ into t1
select * from t2
注意:如果在9i境中并置了FORCE LOGGING以上操作是无效的,并不会加快,当然,可以通如下NO FORCE LOGGING
Alter database no force logging;
是否启了FORCE LOGGING,可以用如下
SQL> select force_logging from v$database;

##########[Q]
避免使用特定索引
**********[A]
在很多候,Oracle错误的使用索引而致效率的明下降,我可以使用一点点技巧而避免使用不使用的索引,如:
test,有字段a,b,c,d,在a,b,c上建立合索引inx_a(a,b,c),在b独建立了一个索引Inx_b(b)
在正常情况下,where a=? and b=? and c=?会用到索引inx_a
where b=?
会用到索引inx_b
但是,where a=? and b=? and c=? group by b会用到哪个索引呢?在分析数据不正确(很长时间没有分析)或根本没有分析数据的情况下,oracle往往会使用索引inx_b过执划的分析,个索引的使用,将大大耗费查询时间
当然,我可以通如下的技巧避免使用inx_b,而使用inx_a
where a=? and b=? and c=? group by b||'' --如果b是字符
where a=? and b=? and c=? group by b+0 --
如果b是数字
过这样简单的改,往往可以是查询时间提交很多倍
当然,我可以使用no_index提示,相信很多人没有用,也是一个不的方法:
select /*+ no_index(t,inx_b) */ * from test t
where a=? and b=? and c=? group by b

##########[Q]Oracle
么时候会使用跳式索引
**********[A]
9i的一个新特性跳式索引(Index Skip Scan).
例如表有索引index(a,b,c),当查询条件
where b=?
候,可能会使用到索引index(a,b,c)
如,划中出如下
INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)
Oracle
化器(里指的是CBO)对查询应Index Skip Scans至少要有几个条件:
1
化器认为是合适的。
2 索引中的前列的唯一的数量能足一定的条件(如重复值很多)。
3
化器要知道前列的分布(分析/统计表得到)
4 合适的SQL
等。

##########[Q]
么样创建使用虚索引
**********[A]
可以使用nosegment选项,如
create index virtual_index_name on table_name(col_name) nosegment;
如果在哪个session需要测试索引,可以利用含参数来
alter session set "_use_nosegment_indexes" = true;
就可以利用explain plan for select ……来看虚索引的效果
利用@$ORACLE_HOME/rdbms/admin/utlxpls
最后,根据需要,我可以除虚索引,如普通索引一
drop index virtual_index_name;
注意:虚索引并不是物理存在的,所以虚索引并不等同于物理索引,不要用自跟踪去测试索引,因那是实际执行的效果,是用不到虚索引的。

##########[Q]
样监控无用的索引
**********[A]Oracle 9i
以上,可以控索引的使用情况,如果一段时间内没有使用的索引,一般就是无用的索引

控:alter index index_name monitoring usage;
检查使用状select * from v$object_usage;
停止控:alter index index_name nomonitoring usage;
当然,如果想控整个用下的索引,可以采用如下的脚本:
set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
FROM dba_indexes
WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on
------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql
SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
FROM dba_indexes
WHERE owner = USER;
spool off
set heading on
set echo on
set feedback on

##########[Q]
么样能固定我的
**********[A]
可以使用OUTLINE来固定SQL句的
用如下句可以建一个OUTLINE
Create oe replace outline OutLn_Name on
Select Col1,Col2 from Table
where ……
如果要Outline,可以采用
Drop Outline OutLn_Name;
于已经创建了的OutLine,存放在OUTLNOL$HINTS表下面
于有些句,你可以使用update outln.ol$hints来更新outline
update outln.ol$hints(ol_name,'TEST1','TEST2','TEST2','TEST1)
where ol_name in ('TEST1','TEST2');
这样,你就把Test1 OUTLINETest2 OUTLINE
如果想利用已存在的OUTLINE,需要置以下参数
Alter system/session set Query_rewrite_enabled = true
Alter system/session set use_stored_outlines = true

##########[Q]v$sysstat
中的class代表什
**********[A]
统计类别
1
代表事例活
2
代表Redo buffer
4
代表
8
代表数据冲活
16
代表OS
32
代表并行活
64
代表表访问
128
代表调试信息

##########[Q]
么杀掉特定的数据
**********[A] Alter system kill session 'sid,serial#';
或者
alter system disconnect session 'sid,serial#' immediate;
win上,可以采用oracle提供的orakill掉一个线程(其就是一个Oracle程)
Linux/Unix上,可以直接利用kill掉数据库进对应OS

##########[Q]
快速等待
**********[A]
数据是比费资源的,特等待的候,我找到生等待的,有可能的该进程。
句将找到数据中所有的DML生的可以发现,任何DML句其实产生了两个,一个是表,一个是行
可以通alter system kill session ‘sid,serial#’掉会
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL
如果生了等待,我可能更想知道是谁锁了表而引起的等待
以下的句可以查询谁锁了表,而在等待。
SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
o.owner,o.object_name,o.object_type,s.sid,s.serial#
FROM v$locked_object l,dba_objects o,v$session s
WHERE l.object_id=o.object_id
AND l.session_id=s.sid
ORDER BY o.object_id,xidusn DESC
以上查询结果是一个构,如果有子点,表示有等待生。如果想知道用了哪个回段,可以关联V$rollname,其中xidusn就是回段的USN

##########[Q]
如何有效的除一个大表(extent数很多的表)
**********[A]
一个有很多(100k)extent的表,如果只是简单地用drop table,会很大量消耗CPUOraclefet$uet$数据字典行操作),可能会用上几天的时间好的方法是分多次extent,以减轻这种消耗:
1. truncate table big-table reuse storage;
2. alter table big-table deallocate unused keep 2000m (
原来大小的n-1/n);
3. alter table big-table deallocate unused keep 1500m ;
....
4. drop table big-table;

##########[Q]
如何收缩临时数据文件的大小
**********[A]9i
以下版本采用
ALTER DATABASE DATAFILE 'file name' RESIZE 100M
似的
9i
以上版本采用
ALTER DATABASE TEMPFILE 'file name' RESIZE 100M
注意,临时数据文件在使用,一般不能收,除非关闭数据或断所有会,停止对临时数据文件的使用。

##########[Q]
清理临时
**********[A]
可以使用如下
1
使用如下看一下认谁在用临时
SELECT username,sid,serial#,sql_address,machine,program,
tablespace,segtype, contents
FROM v$session se,v$sort_usage su
WHERE se.saddr=su.session_addr
2
那些正在使用临时段的
SQL>Alter system kill session 'sid,serial#';
3
、把TEMP表空一下
SQL>Alter tablespace TEMP coalesce;
可以使用断事件
1
确定TEMP表空ts#
SQL> select ts#, name FROM v$tablespace;
TS# NAME
-----------------------
0 SYSYEM
1 RBS
2 USERS
3* TEMP
……
2
行清理操作
alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1'
明:
temp
表空TS# 3*, So TS#+ 1= 4
如果想清除所有表空临时段,
TS# = 2147483647

##########[Q]
么样dump数据内部构,如上面示的控制文件的
**********[A]
的有
1
、分析数据文件转储数据文件nm
alter system dump datafile n block m
2
、分析日志文件
alter system dump logfile logfilename;
3
、分析控制文件的内容
alter session set events 'immediate trace name CONTROLF level 10'
4
、分析所有数据文件
alter session set events 'immediate trace name FILE_HDRS level 10'
5
、分析日志文件
alter session set events 'immediate trace name REDOHDR level 10'
6
、分析系,最好10一次,做三次
alter session set events 'immediate trace name SYSTEMSTATE level 10'
7
、分析程状
alter session set events 'immediate trace name PROCESSSTATE level 10'
8
、分析Library Cache详细情况
alter session set events 'immediate trace name library_cache level 10'

##########[Q]
如何得所有的事件代
**********[A]
事件代一般从10000 to 10999,以下列出了个范的事件代与信息
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR2(120);
BEGIN
dbms_output.enable (1000000);
FOR err_num IN 10000..10999
LOOP
err_msg := SQLERRM (-err_num);
IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
dbms_output.put_line (err_msg);
END IF;
END LOOP;
END;
/
Unix上,事件信息放在一个文本文件里
$ORACLE_HOME/rdbms/mesg/oraus.msg
可以用如下脚本看事件信息
event=10000
while [ $event -ne 10999 ]
do
event=`expr $event + 1`
oerr ora $event
done
于已确保的/正在跟踪的事件,可以用如下脚本
SET SERVEROUTPUT ON
DECLARE
l_level NUMBER;
BEGIN
FOR l_event IN 10000..10999
LOOP
dbms_system.read_ev (l_event,l_level);
IF l_level > 0 THEN
dbms_output.put_line ('Event '||TO_CHAR (l_event)||
' is set at level '||TO_CHAR (l_level));
END IF;
END LOOP;
END;
/

##########[Q]
STATSPACK,我怎使用它?
**********[A]Statspack
Oracle 8i以上提供的一个非常好的性能控与断工具,基本上全部包含了BSTAT/ESTAT的功能,更多的信息
可以参考附文档$ORACLE_HOME/rdbms/admin/spdoc.txt
安装Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql --
,第一次可以不需要
sqlplus "/ as sysdba" @spcreate.sql --
需要根据提示入表空
使用Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; --
行信息收集统计次运行都将生一个快照号
--
得快照号,必要有两个以上的快照,才能生成
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql --
入需要看的始快照号与束快照号
其他相脚本s:
spauto.sql -
利用dbms_job提交一个作,自STATPACK的信息收集统计
sppurge.sql -
清除一段范内的统计信息,需要提供始快照与束快照号
sptrunc.sql -
清除(truncate)所有统计信息

第五部分、ORACLE与安全
##########[Q]
如何限定特定IP访问数据
**********[A]
可以利用登器、cmgw或者是在$OREACLE_HOME/network/admin下新增一个protocol.ora文件(有些os可能是. protocol.ora),9i可以直接修改sqlnet.ora
增加如下内容:
tcp.validnode_checking=yes
#
许访问ip
tcp.inited_nodes=(ip1,ip2,……)
#
不允许访问ip
tcp.excluded_nodes=(ip1,ip2,……)

##########[Q]如何穿防火墙连接数据
**********[A]
问题只会在WIN平台出UNIX平台会自解决。
解决方法:
在服器端的SQLNET.ORA应类
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
TRACE_LEVEL_CLIENT = 16
注册表的HOME0[HKEY_LOCAL_MACHINE]
USE_SHARED_SOCKET=TRUE

##########[Q]
如何利用hostname方式接数据
host name
方式只支持tcp/ip协议的小局域网
修改listener.ora中的如下信息
(SID_DESC =
(GLOBAL_DBNAME = ur_hostname) --
你的机器名
(ORACLE_HOME = E:/oracle/ora92) --oracle home
(SID_NAME = orcl) --sid name
)
然后在客
sqlnet.ora中,确保有
NAMES.DIRECTORY_PATH= (HOSTNAME)
你就可以利用数据器的名称访问数据

##########[Q]dbms_repcat_admin
来什安全
**********[A]
如果一个用dbms_repcat_admin包,将得极大的系统权限。
以下情况可能包的限:
1
、在sysgrant execute on dbms_repcat_admin to public[|user_name]
2
、用户拥execute any procedure限于9i以下,9i须显示授
如果用过执行如下句:
exec sys.dbms_repcat_admin.grant_admin_any_schema('user_name');
得极大的系
可以从user_sys_privs详细信息

##########[Q]
在不知道用候,怎么样到另外一个用户执行操作后并不影响?
**********[A]
如下的方法,可以安全使用,然后再跳回来,在某些候比有用
需要Alter user限或DBA限:
SQL> select password from dba_users where username='SCOTT';
PASSWORD
-----------------------------
F894844C34402B67
SQL> alter user scott identified by lion;
User altered.
SQL> connect scott/lion
Connected.
REM Do whatever you like...
SQL> connect system/manager
Connected.
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
SQL> connect scott/tiger
Connected.

##########[Q]
如何加固你的数据
**********[A]
要注意以下方面
1.
修改sys, system的口令。
2. Lock
,修改,除默 dbsnmp,ctxsys等。
3.
REMOTE_OS_AUTHENT改成False,防止程机器直接登
4.
O7_DICTIONARY_ACCESSIBILITY改成False
5.
把一些限从PUBLIC Role取消掉。
6. 检查数据的数据文件的安全性。不要置成666的。检查其他dba
7.
把一些不需要的服(比如ftp, nfs关闭掉)
8.
限制数据主机上面的用量。
9. 定期检查Metalink/OTN上面的security Alert。比如:http://otn.oracle.com/deploy/security/alerts.htm
10.
把你的数据用放在一个独的子网中,要不然你的用很容易被sniffer去。或者采用advance security加密。
11.
限止只有某些ip才能访问你的数据
12. lsnrctl
要加密,要不然人很容易从外面掉你的listener
13.
如果可能,不要使用默1521端口
<
 
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值