 |
| From 2009_Oct |
June 28th 2009
Skutz Falls to Marie Canyon - 2hr ride, not including optional time spent beached
SQL> SELECT * FROM user_object_size WHERE name = 'PKG1';
NAME TYPE SOURCE_SIZE PARSED_SIZE CODE_SIZE ERROR_SIZE
----------------------------------------------------------------
PKG1 PACKAGE 46 165 119 0
PKG1 PACKAGE BODY 82 0 139 0
SQL> select * from table(UTILITY.tb_obj.rowid_info('AAARUtAAEAAABm2AAA'));
COLUMN_VALUE
--------------------------------------------------------------------------------
rowid: AAARUtAAEAAABm2AAA
file_id: 4
block_id: 6582
data_object_id: 70957
owner: INVDB
object_name: BOOK_UPLOAD_LOG
extent_id: 0
7 rows selected.
tb_ 是toolbox的缩写.
CREATE OR REPLACE PACKAGE UTILITY.tb_obj
/*
| Copyright Information Here
|
| File name:
|
| Overview:
|
| Author(s):
|
| Modification History:
| Date Who What
|
*/
IS
TYPE print_tab IS TABLE OF varchar2(200);
FUNCTION rowid_info (p_rowid in varchar2) return print_tab PIPELINED DETERMINISTIC;
PROCEDURE subprogram_name;
END tb_obj;
/
CREATE OR REPLACE PACKAGE BODY UTILITY.tb_obj
/*
| Copyright Information Here
|
| File name:
|
| Overview:
Tool box to get object information.
| Author(s):
|
| Modification History:
| Date Who What
31-Mar-2009 Charlie (Yi): Create the package.
It meets user requirements
It is maintainable
It runs fast enough to minimize user frustration
**) Pl/SQL development workflow, Four steps of preparing an application
- Validate program requirements
# ask lots of questions
# what users ask for is not always the easiest way to solve a problem
# consider other approaches, include business processes and programming algorithms
- Implement header of the program
# good name for the program, accurately represent the purpose of the program
# inputs and outputs
# overload sub-procedure ?
- Define the test cases
# Verify it works
# how will I know when I am done with this program
- Build test code
|
|
*/
IS
PROCEDURE initialize_pkg
IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE( 'tb_obj','init' );
END initialize_pkg;
FUNCTION rowid_info (p_rowid in varchar2)
return print_tab PIPELINED DETERMINISTIC
/*
| Copyright Information Here
|
| File name:
|
| Overview: Display extended rowid information.
|
| Author(s): Charlie MuJiang
|
| Modification History:
| Date Who What
31-Mar-2009 Charlie (Yi): Create the function.
Privilege:
grant select on dba_objects to utility;
grant select on dba_extents to utility;
Call example:
SQL> select * from table(UTILITY.tb_obj.rowid_info('AAARUtAAEAAABm2AAA'));
COLUMN_VALUE
--------------------------------------------------------------------------------
rowid: AAARUtAAEAAABm2AAA
file_id: 4
block_id: 6582
data_object_id: 70957
owner: INVDB
object_name: BOOK_UPLOAD_LOG
extent_id: 0
7 rows selected.
|
*/
IS
ln_block_id number;
ln_file_id number;
ln_object_id number;
ln_extent_id number;
ls_object_name varchar2(30);
ls_owner varchar2(30);
PROCEDURE initialize
IS
BEGIN
DBMS_APPLICATION_INFO.SET_ACTION( 'rowid_info' );
END initialize;
PROCEDURE cleanup
IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(Null,Null);
END cleanup;
BEGIN
initialize;
/*
Main body of program
*/
debug.f('start by user %s', user);
select dbms_rowid.ROWID_BLOCK_NUMBER(p_rowid),
dbms_rowid.ROWID_RELATIVE_FNO(p_rowid),
dbms_rowid.ROWID_OBJECT(p_rowid)
into ln_block_id,ln_file_id,ln_object_id
from dual;
select owner,object_name
into ls_owner,ls_object_name
from dba_objects
where data_object_id=ln_object_id;
PIPE ROW(' rowid: '||p_rowid);
PIPE ROW(' file_id: '||ln_file_id);
PIPE ROW(' block_id: '||ln_block_id);
PIPE ROW('data_object_id: '||ln_object_id);
for c in (
select extent_id,
owner, segment_name,partition_name, segment_type,tablespace_name
from dba_extents
where
owner=ls_owner and
segment_name=ls_object_name and
file_id=ln_file_id and
ln_block_id between block_id and block_id + blocks - 1
)
loop
PIPE ROW(' owner: '||c.owner);
PIPE ROW(' object_name: '||c.segment_name);
PIPE ROW('partition_name: '||c.partition_name);
PIPE ROW(' segment_type: '||c.segment_type);
PIPE ROW(' tbs_name: '||c.tablespace_name);
PIPE ROW(' extent_id: '||c.extent_id);
end loop;
debug.f('process %s rows', SQL%RowCount);
cleanup;
EXCEPTION
WHEN NO_DATA_NEEDED THEN
cleanup;
return;
WHEN OTHERS
THEN
/* Don't forget to clean up here, too! */
cleanup;
/* Standard error logging mechanism */
u$err.err;
RollBack;
raise;
END rowid_info;
PROCEDURE subprogram_name
/*
| Copyright Information Here
|
| File name:
|
| Overview:
|
| Author(s):
|
| Modification History:
| Date Who What
|
*/
IS
PROCEDURE initialize
IS
BEGIN
DBMS_APPLICATION_INFO.SET_ACTION( 'subprogram_name' );
END initialize;
PROCEDURE cleanup
IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(Null,Null);
END cleanup;
BEGIN
initialize;
/*
Main body of program
*/
debug.f('update %s rows', SQL%RowCount);
cleanup;
EXCEPTION
WHEN OTHERS
THEN
/* Don't forget to clean up here, too! */
cleanup;
/* Standard error logging mechanism */
u$err.err;
--RollBack;
raise;
END subprogram_name;
BEGIN
initialize_pkg;
END tb_obj;
/
0 parallel hint
1 no access path spec
2 table scan
3 index unique
4 index range
5 index and-equal
6 order by using an index
7 open cluster
8 hash cluster
9 rowid lookup
10 range scan backwards
11 rowid range scan
12 driving_site hint
14 cache hint
15 nocache hint
16 partitions hint
17 nopartitions hint
18 anti-join
19 index rowid range scan
20 bitmap index
21 parallel_index hint
22 noparallel_index hint
23 index fast full scan
24 swap inputs to join
25 fact table
26 not a fact table
27 merge of this view
28 do not push join predicate into this view
29 push join predicate into this view
30 no_merge of this view
31 semi-join
ROWID N1 N2
------------------ ---------- ----------
AAAd7XAAEAAABXCAAA 3 0
ROWID N1 N2
------------------ ---------- ----------
AAAd7XAAEAAABXCAAA 3 0
select * from emp
where (emp_no = 1 or emp_no = 2 or emp_no = 3);
SELECT /*+ QB_NAME(qb) FULL(@qb e) */ employee_id, last_name参考:
FROM employees e
WHERE last_name = 'Smith';
Posts
All Comments
