In Oracle databases, it is often difficult for management tablespaces to visualize data from SQL queries. One way to achieve tablespaces visualization and easier management is to create a segmented use mapping (block usage maps) similar to the fragmentation reorganization feature (defragmentation utilities).
Without any charting or graphics functionality, you can use the simple HTML generated by the Oracelmodplsql package, while the Modplsql package can be installed at oracle8i and 9i.
This also contains the risk, because the program must be run by a DBA account, so the DBA account must give each action plan a permission to use, and the action plan must be included in the Wdbsvr.app file. To protect this file, you must make sure that the file is accessible only to Oracle users and DBA groups. If you are willing to write this program as a JSP page or servlet, you should also be very careful to prevent external access to the DBA account.
The DBA does not see a pl/sql query, so you must install Pl/sql as SYS, and then assign permissions to the account that is allowed to run Pl/sql:
Connect sys/<password>
@blkmap. sql
Grant execute on Blkmap to system;
Connect system/<password>
Create synonym Blkmap for sys.blkmap;
In this example, we set up a Blkmap package, which has two entry points for this BLKMAP package. One is a menu that displays the Tablespaces list, and the other is the tablespace fragment map that is actually used:
Create or Replace package Blkma
As
Procedure Ts_menu;
Procedure Ts_map (P_name varchar2);
End Blkmap;
/
Show errors;
The code for the Tablespace menu can be a list of tablespaces associated with the segmented map (block map) page:
Procedure Ts_menu
Is
Begin
HTP.P (' HTP.P (' <body bgcolor= ' white ' > ');
HTP.P (' HTP.P (' <ul> ');
For TS in (select Tablespace_name from dba_tablespaces) loop
HTP.P (' <li><a href= ' blkmap.ts_map?p_id= ' | | ts.tablespace_name| | ' " > '
|| ts.tablespace_name| | ' </a></li> ');
End Loop;
HTP.P (' </ul> ');
HTP.P (' </body> ');
HTP.P (' End Ts_menu;
For tablespace mappings, you can generate a tablespace name and request a segmented map of each file that contains tablespace.
Procedure Ts_map (p_name varchar2)
is
begin
Page_open; --Open the page
htp.p (' tablespace: ' | | P_name);
htp.p (' for file in
(
Select file_id
from Dba_data_files
where tablespace_name = P_name
ORDER by file_id< br>)
Loop
Filemap (file.file_id); --Generate a block map for the file
htp.p (' end loop;
Info_form; --Generate a form for segment info
legend; --Generate a legend for color mappings
Page_close; --Close the page
end Ts_map;
For the segmented mappings that are actually used, we usually need to display a "block" and a color representation. Using labels is more complicated. A better method is to generate the character order, which is the interval after a period of time, which allows the text to be colored at any location.
To make the scoring segment mapping more useful, we want to list the block information for the mouse position and highlight the blocks that make up this fragment, and we can use the tag. First, you build a type that specifies a and a:hover, and then you build the type of each Oracle database segment:
<style type= "Text/css" >
A
{
Text-decoration:none;
Font-family:monospace;
font-size:6pt;
}
a:hover {Background-color:yellow}
A.free {Background-color:white}
A.cache {background-color: #FFCC00}
A.cluster {background-color: #9A0000}
A.index {background-color: #009900}
A.lobindex {background-color: #9AFE66}
a.lobsegment {background-color: #9A99FF}
A.rollback {background-color: #FF3300}
a.temporary {background-color: #DFFEFF}
a.table {background-color: #003399}
A.other {Background-color:magenta}
Body
{
Background-color:white;
Font-family:sans-serif;
font-size:10pt;
}
</style>
We can list the block user, name, type, block number, and fragment length for the mouse position. To do this, we need two JavaScript functions that contain the above information and are invoked and passed to the form's domain:
<script language= "JavaScript" >
function hover (Own,nam,typ,bid,len)
{
Document.form.owner.value = own;
Document.form.name.value = Nam;
Document.form.type.value = Typ;
Document.form.block_id.value = bid;
Document.form.length.value = Len;
return true;
}
function Leave ()
{
Document.form.owner.value = "";
Document.form.name.value = "";
Document.form.type.value = "";
Document.form.block_id.value = "";
Document.form.length.value = "";
return true;
}
</script>
. . .
<form name= "Form" >
<table border=0>
<tr><td>owner:</td><td><input name= "Owner"/></td></tr>
<tr><td>name:</td><td><input name= "Name"/></td></tr>
<tr><td>type:</td><td><input name= "Type"/></td></tr>
<tr><td>block#:</td><td><input name= "block_id"/></td></tr>
<tr><td>length:</td><td><input name= "Length"/></td></tr>
</table>
</form>
For each fragment, we need to create a block tag. Finally, create a chart that shows different color meanings:
<table border=0>
<tr><thcolspan= "2" >Legend</th></tr>
<tr><td><a name= "free" class= "free". </a></td>
<td>free space</td></tr>
<tr><td><a name= "Cache" class= "cache". </a></td>
<td>Cache</td></tr>
<tr><td><a name= "cluster" class= "cluster". </a></td>
<td>Cluster</td></tr>
<tr><td><a name= "index" class= "index". </a></td>
<td>Index</td></tr>
<tr><td><a name= "Lobindex" class= "Lobindex". </a></td>
<td>LobIndex</td></tr>
<tr><td><a name= "lobsegment" class= "Lobsegment". </a></td>
<td>LobSegment</td></tr>
<tr><td><a name= "rollback" class= "rollback". </a></td>
<td>Rollback</td></tr>
<tr><td><a name= "temporary" class= "temporary". </a></td>
<td>Temporary</td></tr>
<tr><td><a name= "table" class= "table". </a></td>
<td>Table</td></tr>
<tr><td><a name= "Other" class= "other". </a></td>
<td>Other</td></tr>
</table>
Here's the complete code:
Create or Replace package Blkmap
Authidcurrent_user
As
Procedure Ts_menu;
Procedure Ts_map (P_name varchar2);
End Blkmap;
/
Show errors;
Create or replace package body Blkmap
As
Procedure Page_open;
Procedure Info_form;
Procedure Filemap (p_id number);
Procedure legend;
Procedure Page_close;
--
Procedure Ts_menu
Is
Begin
HTP.P (' HTP.P (' <body bgcolor= ' white ' > ');
HTP.P (' HTP.P (' <ul> ');
For TS in (select Tablespace_name from dba_tablespaces) loop
HTP.P (' <li><a href= ' blkmap.ts_map?p_name= ' | | ts.tablespace_name| | ' " > '
|| ts.tablespace_name| | ' </a></li> ');
End Loop;
HTP.P (' </ul> ');
HTP.P (' </body> ');
HTP.P (' End Ts_menu;
--
Procedure Ts_map (P_name varchar2)
Is
Begin
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
