Elvis & Figo

IMP命令如何把对象导入用户的默认表空间，而不是对象原来的表空间

网上整理，不是原创，作者不祥

1.sqlplus system/sys
2.创建数据库用户
SQL> create user newuser identified by newpass
2 default tablespace newspace
3 temporary tablespace temp
4 /
3.授予权限给用户
SQL> grant connect,resource to newuser;
SQL> grant dba to newuser;
4.移除无限表空间权限
SQL> revoke unlimited tablespace from newuser;
5.取消用户在原表空间的配额
SQL> alter user newuser quota 0 on users;
6.授予用户在默认表空间的无限配额
SQL> alter user newuser quota unlimited on newspace;
7.SQL> exit
8.执行导入
imp newuser/newpass file=data.dmp fromuser=olduser touser=newuser grants=n

Cursor Sharing监控相关语句

自己总结和摘录的一些如何发现cursor sharing问题相关的一些SQL，很多是摘自Performance Tuning Guide，绝大部分并非原创。主要是探讨如何去发现相似的SQL或者查找SQL没有共享的原因。

一个经常存取的表，一定要把它的数据和索引分布在不同的磁盘上吗？

One popular approach to manual I/O distribution suggests separating a frequently used table from its index. This is not correct. During the course of a transaction, the index is read first, and then the table is read. Because these I/Os occur sequentially, the table and index can be stored on the same disk without contention. It is not sufficient to separate a datafile simply because the datafile contains indexes or table data. The decision to segregate a file should be made only when the I/O rate for that file affects database performance.

-- 引用自 <<Oracle Database Performance Tuning Guide>>

常用的Oracle脚本参考

http://www.oracle-base.com/dba/DBACategories.php

2008-05-21：基本上都是一些简单的东西，注释也不错，比较容易看懂，在这个基础上自行总结汇总还是需要的。

数据库统计信息不更新导致的性能问题

SQL如下，系统动态生成的，主要是查看符合多个条件付款对应的所有明细记录。语句本身有问题，因为，多个in可以合并成一个in，相关的条件and起来即可，这个暂时忽略。

SELECT doc.* FROM TLK_PAYMENT_DETAIL doc
WHERE ISTMP=0 AND (((((((ITEM_contractnumber = '06603833'))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.ITEM_单据类型 IN ('4','8'))))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.ITEM_IsReverse = '0')))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.stateint = 1048576)))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.istmp <> 1)))
AND ((ITEM_expensecode = '33000100')))

删除数据库表空间

几个必要的步骤：

1 - alter tablespace xxxx offline normal;

2 - drop tablespace xxxx including contents and datafiles

3 - 由于windows版本的oracle不会自动删除文件，需要重启oracle，删除文件。

估算undo表空间大小

Ora-01632解决办法

Oracle中关于函数的使用

Create Refresh Group

BEGIN
DBMS_REFRESH.MAKE(
name => '"GOLDENBEAR"."XXXX"',
list => '',
next_date => SYSDATE,
interval => '/*10:Mins*/ sysdate + 10/(60*24)',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => FALSE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/

BEGIN
DBMS_REFRESH.ADD(
name => '"GOLDENBEAR"."XXXX"',
list => '"GOLDENBEAR"."DYNA_DKARE"',
lax => TRUE);
END;
/

BEGIN
DBMS_REFRESH.ADD(
name => '"MVIEWADMIN"."GOLDENBEAR_REFRESH_GROUP"',
list => '"GOLDENBEAR"."T_DYNAFORM_ITEM"',
lax => TRUE);
END;
/

打印CLOB的方法

-- 由于每行只能打印255字符的限制，这里每100字符打印一次
-- 所以格式不太好看
CREATE OR REPLACE
procedure PRINT_CLOB (p_clob_in in CLOB) is
v_buffer varchar2(300);
v_buffer_size integer := 100;
v_offset integer := 1;
begin
--
IF p_clob_in is null THEN
return;
END IF;
--
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_clob_in)/v_buffer_size) LOOP
v_buffer :=DBMS_LOB.SUBSTR(p_clob_in, v_buffer_size, v_offset);
dbms_output.put_line(v_buffer);
v_offset := v_offset + v_buffer_size;
END LOOP;
end;

BLOB转换成CLOB的方法

SET CONSTRAINT[S] 语句

SET CONSTRAINT xxxx IMMEDIATE/DEFERRED

SET CONSTRAINTS xxx, yyy, zzz / ALL IMMEDIATE/DEFERRED

To Report Exception Rows When Enabling Constraint

Cursor相关的脚本示例

一些常用的cursor相关的脚本

 查看全文

Oracle记录集处理脚本示例

一组演示如何使用Oracle返回记录集的脚本。

PGA相关统计脚本

-- PGA performance consideration
-- Ideally, the size of a work area is big enough that it
-- can accommodate the input data and auxiliary memory structures
-- allocated by its associated SQL operator. This is known as
-- the optimal size of a work area.

-- When the size of the work area is smaller than optimal,
-- the response time increases, because an extra pass is
-- performed over part of the input data. This is known as
-- the one-pass size of the work area.

-- Under the one-pass threshold, when the size of a work area
-- is far too small compared to the input data size, multiple
-- passes over the input data are needed. This could dramatically
-- increase the response time of the operator. This is known as
-- the multi-pass size of the work area.

-- hit ratio regarding PGA
-- (BP * 100) / (BP + EBP)
-- BP: Bytes processed
-- EBP: Extract bytes processed (through one pass or multiple passes)
select value from v$pgastat where name = 'bytes processed'

select value from v$pgastat where name = 'extra bytes read/written'

select value from v$pgastat where name = 'cache hit percentage'

-- check for % of times that work areas were executed in optimal,
-- one-pass, or multi-pass mode since start-up.
select optimal_count, round(optimal_count*100/total, 2) optimal_perc,
onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
from
(select decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(optimal_executions) optimal_count,
sum(onepass_executions) onepass_count,
sum(multipasses_executions) multipass_count
from v$sql_workarea_histogram
where low_optimal_size > 64*1024);

-- to see the active work area
select to_number(decode(sid, 65535, null, sid)) sid,
operation_type operation,
trunc(expected_size/1024) esize,
trunc(actual_mem_used/1024) mem,
trunc(max_mem_used/1024) "max mem",
number_passes pass,
trunc(tempseg_size/1024) tsize
from v$sql_workarea_active
order by 1,2;

Shared pool相关统计脚本

-- check the library cache statistics by name space (type)
select namespace, pins, pinhits, reloads, invalidations
from v$librarycache
order by namespace

-- to caculate the overall hit ratio of libary cache
select sum(pinhits) / sum(pins)
from v$librarycache

-- check for free memory in shared pool
select * from v$sgastat
where name = 'free memory'
and pool = 'shared pool';

-- check for library cache memory allocations
select * from v$library_cache_memory

-- check for data dictionary hit ratio
select parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
from v$rowcache
where gets > 0
group by parameter;

-- check for overall data dictionary hit ratio
select (sum(gets - getmisses - fixed)) / sum(gets) "row cache"
from v$rowcache;

-- check for reserved shared pool
select * from v$shared_pool_reserved