Oracle 小结

[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]怎么样dump数据库内部结构，如上面显示的控制文件的结构

[A]常见的有

1、分析数据文件块，转储数据文件n的块m

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] 如何有效的删除一个大表(extent数很多的表)

[A] 一个有很多(100k)extent的表，如果只是简单地用drop table的话，会很大量消耗CPU（Oracle要对fet$、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]数据库的锁是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。

这个语句将查找到数据库中所有的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]怎么快速得到整个数据库的热备脚本

[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]控制文件主要包含如下条目，可以通过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]以下是常见操作，如果在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]联机日志损坏如何恢复

[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]怎么样快速下载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]dbms_output提示缓冲区不够，怎么增加

[A]dbms_output.enable(20000);

另外，如果dbms_output的信息不能显示，

需要设置

set serveroutput on

[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]如何创建约束的索引在别的表空间上

[A]1、先创建索引，再创建约束

2、利用如下语句创建

create table test

(c1 number constraint pk_c1_id primary key

using index tablespace useridex,

c2 varchar2(10)

) tablespace userdate;

[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;

当然，如果是索引，则需要修改相关table到index

[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(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM 

,decode(bitand(ksppiflg/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]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]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，这个数据类型每行使用2至5个字节 

BLOB二进制大对象，最大长度为4GB 

CLOB字符大对象，最大长度为4GB 

NCLOB多字节字符集的CLOB数据类型，最大长度为4GB 

BFILE外部二进制文件，大小由操作系统决定 

ROWID表示RowID的二进制数据，Oracle8RowID的数值为10个字节，在Oracle7中使用的限定 

RowID格式为6个字节 

UROWID用于数据寻址的二进制数据，最大长度为4000个字节 

[Q]怎么样在Oracle中写操作系统文件，如写日志

[A]可以利用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;

[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;

[Q]怎么样把查询内容输出到文本

[A]用spool如

如sqlplus –s " / as sysdba" <

set heading off

set feedback off

spool temp.txt

　 select * from tab;

dbms_output.put_line(‘test’);

spool off

exit

EOF

[Q]怎么样从数据库中获得毫秒

[A]9i以上版本，有一个timestamp类型获得毫秒，如

SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1, 

to_char(current_timestamp) time2 from dual;

[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;