Create Database Link in Another Schema

Creating database link has several restrictions. One of them is that you can’t create DB link in another schema, for example the following script is not correct:

create database link hr.remote_db_link
connect to k identified by k
using 'test'


If you run this then database link will be created with the name “hr.remote_db_link”  in your schema not in HR schema.

To solve this, there can be done the following:

–Create procedure from our user in HR which will execute database creation script

 create procedure hr."db_create_link_proc"
 is
 begin
      execute immediate  'create database link remote_db_link
                          connect to k identified by k
                          using ''test'' ';
 end;

–Execute that procedure from our schema

 begin
      hr."db_create_link_proc";
 end;

–Then you can drop this procedure from our schema

drop procedure hr."db_create_link_proc"

That is all.

Block Media Recovery

You can use Block Media Recovery to recover just corrupted blocks not entire datafile. It has several advantages:

1. It decreases Mean Time To Recover(MTTR). As I mentioned above, you need to recover just corrupted blocks and not all the blocks in datafile.

2. Affected datafile stays online. If you do not use Block Media Recovery you must take datafile offline, which means that affected datafile will be unavailable. During BMR just corrupted blocks are not available.

When you are recovering blocks you must know exact address of the block. Each block is recovered individually.

Also database must be in ARCHIVELOG mode and backup of the database must exist.

I have a table called TEST which is saved in TSTTBS tablespace. I have backuped database, then I’ve opened TSTTBS.DBF datafile by UltraEdit and spoiled block which
belongs to the TEST table. You do not need this steps Smile . I did it for to simulate block corruption.

SQL> select * from a;
select * from a
*
ERROR at line 1:
ORA-01578:ORACLE data block corrupted (file # 5, block # 11)
ORA-01110:data file 5:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TSTTBS.DBF'

Error shows that block number 12 is corrupted in datafile 5.

Connect target database by RMAN, and run the following:

RMAN> blockrecover datafile 5 block 11;

It should not take time like 00:00:00 or output shouldn’t be something unusual for example for the firs time I had the following output:

Starting blockrecover at 29-MAR-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished blockrecover at 29-MAR-11

It was because I have had taken a bad backup. It may also happen when RMAN couldn’t find backup files or you have indicated wrong datafile number or block number.

The followings are restrictions and usage notes of BLOCKRECOVER:

  1. The target database must be mounted or open. You do not have to take a datafile offline if you are performing block media recovery on it.
  2. You can only perform complete media recovery of individual blocks. Point-in-time recovery of individual data blocks is not supported.
  3. You can only perform block media recovery on corrupt blocks.
  4. Blocks marked media corrupt are not accessible until recovery completes.
  5. You cannot perform block media recovery when using a backup control file.
  6. You cannot use proxy backups to perform block media recovery. If the only backups that you have are proxy backups, then you can restore them to a nondefault location on disk, which causes RMAN to view the restored files as datafile copies. You can then use the datafile copies for block media recovery.
  7. You must have a full backup of the file containing the corrupt blocks: block media recovery cannot use incremental backups.
  8. If RMAN fails to access a specific archived redo log file needed for block media recovery, it performs restore failover, trying all other backups listed in the RMAN repository that are suitable for use in this operation, and only fails if no suitable backup is available. See
  9. The datafile header block (block 1) cannot be recovered.
  10. You cannot perform block media recovery in NOARCHIVELOG mode.

Let’s check if corrupted block is recovered.

SQL> select * from a;
select * from a
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110:data file 5:'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\TSTTBS.DBF'

In our example after recovering block  #11 Oracle raised another error indicating that #12 block is corrupted(When multiple blocks are corrupted error is raised one by one). Let’s repeat above steps:

RMAN> blockrecover datafile 5 block 12;
SQL> select * from a;

VC
------------------
Testing Corruption

Configuring Flashback Database

Connect to your database as sysdba.

1.  Ensure that your database is in ARCHIVELOG mode.

SQL> select log_mode from v$database;

LOG_MODE
————————
NOARCHIVELOG

1.1 My database is not in ARCHIVELOG mode let’s enable it.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

SQL> select log_mode from v$database;

LOG_MODE
——————-
ARCHIVELOG

2. Setting up flash recovery area.

SQL> show parameter db_recovery_file_dest;

NAME                                        TYPE             VALUE
————————————   ———–     ————-
db_recovery_file_dest           string
db_recovery_file_dest_size  big integer 0

First of all, db_recovery_file_dest_size parameter must be set.

SQL> alter system set db_recovery_file_dest_size=2G;

Then db_recovery_file_dest parameter.

SQL> alter system set db_recovery_file_dest=’D:\TEST\FLASHRECOVERY’;

3. Setting up retention period.

Because of flash recovery area is used in circular fashion, after some period of time(indicates db_flashback_retention_target,default is 1day) old data is overwritten. This parameter instructs Oracle to save flashback files for a certain minutes before overwriting.

SQL> alter system set db_flashback_retention_target=720;

In our case flashback data will be retained for 12 hours before overwriting.

4. Enable flashback logging.

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;

At this time RVWR process will be started and flashback buffer will be allocated in SGA.

SQL> alter database open;

5. Check if flashbacking is enabled.

SQL> select flashback_on from v$database;

FLASHBACK_ON
———————–
YES

SQL Profile has higher priority than Hint!

Hints on SQL statement do not work???
You may have a syntax error, double check it. But if the syntax is OK, then this problem may be caused by the SQL profile, which doesn’t let hints to work.

Here, in this post I will explain the situation when there is applied SQL profile for some SQL statement and any attempt to use hints on that statement fails.

To solve this problem you should drop that SQL profile or disable it.

Note: This behavior has advantage. It assures that the SQL plan of the SQL statement will not be changed for future runs.

Let’s start…

–Create test table

create table test (n1 number
, n2 number );

–Insert some values

begin
for i in 1 .. 100000
loop
insert into test values(i,i+1);
commit;
end loop;
end;

–Create index

create index test_idx1 on test(n1);

–Analyze table

analyze table test estimate statistics;

–Run the following SQL statement

select /*+ no_index(test test_idx1) */ *
from test where n1=2;

–Calculate explain plan

explain plan for select /*+ no_index(test test_idx1) */ *
from test where n1=2;

select * from table(dbms_xplan.display);

–Output

————————————————————————-
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     8 |    49   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     8 |    49   (5)| 00:00:01 |
————————————————————————–

–Create SQL tuning task

declare
my_task_name varchar2(30);
my_sqltext   clob;
begin
my_sqltext := ‘select /*+ no_index(test test_idx1) */ * from test where n1=2’;

my_task_name := dbms_sqltune.create_tuning_task(
sql_text    => my_sqltext,
scope       => ‘comprehensive’,
time_limit  => 60,
task_name   => ‘my_sql_tuning_task’);
end;

–Execute SQL tuning task

begin
dbms_sqltune.execute_tuning_task( task_name => ‘my_sql_tuning_task’);
end;

–Generate report

select dbms_sqltune.report_tuning_task( ‘my_sql_tuning_task’)
from dual;

–Output

Recommendation (estimated benefit: 95.87%)
——————————————
– Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
‘my_sql_tuning_task’, replace => TRUE);

—————————————————————————————–
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | SELECT STATEMENT            |           |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
—————————————————————————————–

–Accept SQL profile

begin
dbms_sqltune.accept_sql_profile(task_name =>’my_sql_tuning_task’
,replace => true
,name => ‘my_profile_name’);
end;

–Calculate explain plan again

explain plan for select /*+ no_index(test test_idx1) */ *
from test where n1=2;

select * from table(dbms_xplan.display);

–Output

—————————————————————————————–
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————————–
|   0 | SELECT STATEMENT            |           |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST      |     1 |     8 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
—————————————————————————————–

Note: We have indicated not to use index(/*+ NO_INDEX(TEST TEST_IDX1) */ ), but as explain plan shows, Oracle still uses this index.

If we drop sql profile hint will work. Let’s check it:

begin
dbms_sqltune.drop_sql_profile(name => ‘my_profile_name’);
end;

–Calculate explain plan

explain plan for select /*+ no_index(test test_idx1) */ *
from test where n1=2;

select * from table(dbms_xplan.display);

–Output

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      |     1 |     8 |    49   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     8 |    49   (5)| 00:00:01 |
————————————————————————–

So SQL profile has higher priority than hints!!!<- Important

Digital Image Processing using Matlab

Here, in this post I will write a code which will find the area in the picture where the sentence “Digital Image Processing” is written and outlines it.

So the result should be the picture where the sentence “Digital Image Processing” is highlighted.

 

%Reading image
I = imread(‘img1.jpg’);

%Show the original image
figure; imshow(I); title(‘Original Image’);

% Add some text on the right side of the picture, if you want to place this code in one
%line, remove dots.

text(size(I,2),size(I,1)+15,‘Edited by Mariam Kupatadze’,…
‘FontSize’,…
7,…
‘HorizontalAlignment’,…
‘right’);

Digital_Image_Processing_Original_Image

%Detect edges using sobel method
Isobel = edge(I,‘sobel’);

%Show image
figure; imshow(Isobel); title(‘Edge Detected Image’);

text(size(I,2),size(I,1)+15,‘Edited by Mariam Kupatadze’,…
‘FontSize’,…
7,…
‘HorizontalAlignment’,…
‘right’);

Digital_Image_Processing_Edge_Detected_Image

%Remove objects which are connected to the border
Inobord = imclearborder(Isobel);

%Show Image
figure; imshow(Inobord); title(‘Border Cleared Image 1’);

text(size(I,2),size(I,1)+15,‘Edited by Mariam Kupatadze’,…
‘FontSize’,…
7,…
‘HorizontalAlignment’,…
‘right’);

Digital_Image_Processing_Bordered_Cleared_Image_1

%Creates a disk-shaped structuring element by radius=15
StructEl1 = strel(‘disk’,15);

%Creates a linear structuring elements
StructEl2 = strel(‘line’, 3, 90);
StructEl3 = strel(‘line’, 3, 0);

%Remove the linear gaps from the picture
Idil = imdilate(Inobord, [StructEl2 StructEl3]);

%Because of, there left extra objects, I will dilate it by disk-shaped structuring element for to %make them connect to the border.
Idil = imdilate(Idil, StructEl1);

%Show image
figure; imshow(Idil); title(‘Dilated Image’);

text(size(I,2),size(I,1)+15,‘Edited by Mariam Kupatadze’,…
‘FontSize’,…
7,…
‘HorizontalAlignment’,…
‘right’);

Digital_Image_Processing_Dilated_Image

%Again remove objects which are connected to the border
Inobord1 = imclearborder(Idil);

%Show image
figure; imshow(Inobord1); title(‘Border Cleared Image 2’);

text(size(I,2),size(I,1)+15,‘Edited by Mariam Kupatadze’,…
‘FontSize’,…
7,…
‘HorizontalAlignment’,…
‘right’);

Digital_Image_Processing_Border_Cleared_Image_2

%Fill existing holes in the picture
Ifilled = imfill(Inobord1, ‘holes’);

%Show image
figure; imshow(Ifilled); title(‘Holes Filled Image’);

text(size(I,2),size(I,1)+15,‘Edited by Mariam Kupatadze’,…
‘FontSize’,…
7,…
‘HorizontalAlignment’,…
‘right’);

Digital_Image_Processing_Holes_Filled_Image

%Find perimeter of “Ifilled” image, with default connectivity 4
Ioutlined = bwperim(Ifilled);%The same as bwperim(Ifilled,4);
IOut = I;

%Highlight the desired area
IOut(Ioutlined) = 255;

%Show image
figure; imshow(IOut); title(‘Sentence Highlighted Image’);

text(size(I,2),size(I,1)+15,‘Edited by Mariam Kupatadze’,…
‘FontSize’,…7,…
‘HorizontalAlignment’,…
‘right’);

Digital_Image_Processing_Sentence_Highlighted_Image

Install New Unicode Character Set in Oracle(Locale Builder)

Locale Builder is an utility to view, modify, or define locale-specific data. As well as to create your own formats for language, territory, character set, and linguistic sort.

I will show you how to create .nlb(binary file) from .nlt(text file).

First of all, let’s discuss what NLT file is.

NLT file content is in xml format which can be created by Oracle Locale Builder(located in %ORACLE_HOME%\nls\builders\lbuilder.bat.)

1. Run lbuilder.bat

Choose File->New->Character Set.

As you can see every field is free, for better understanding let me open already created character set

Oracle_Locale_Builder_General

 

Oracle_Locale_Builder_Type_Specific

 

Oracle_Locale_Builder_Character_Data_Mapping

Oracle_Locale_Builder_LowerToUppercase_Mapping

Oracle_Locale_Builder_UpperToLowercase_Mapping

Oracle_Locale_Builder_Character_Classification

Oracle_Locale_Builder_Replacement_Characters

Oracle_Locale_Builder_Display_Width

Oracle_Locale_Builder_Multibyte_Equivalent

And the last one shows the NLT file content.

Oracle_Locale_Builder_Preview_NLT

As you can see content is in XML format.

As I said previously NLT file is a text file, but Oracle needs NLB, binary file. Oracle Locale Builder gives the ability to create NLB file from NLT file. Let’s do it.

I consider the situation when we already have NLT file.

2. Run Locale Builder.

Click Generate NLB icon.

Oracle_Locale_Builder_Generate_NLB

Choose the path where your NLT files are.

Be careful, after browsing the folder path I’ve noticed that something wrong is in the path:

Oracle_Locale_Builder_Generate_NLB_BrowsePath

There, nltfiles folder name is displayed twice. Let’s see what happens if we click OK.

Oracle_Locale_Builder_NLB_Generation_Error

So I decided to delete second nltfiles and click OK.

Note: If you highlight folder where NLT files are, so do not enter inside, and click Open during browsing, everything will be OK.

Oracle_Locale_Builder_Generate_NLB_BrowsePath2

Oracle_Locale_Builder_NLB_Generation_Success

NLB files are located in the same directory where NLT files are so for me it is D:\Install\Gbank\nltfiles.

Just a little advice: Save old lx0boot.nlb and lx1boot.nlb files and then replace them by new one, for to avoid any incompatibility .

Copy all of them and paste into %ORACLE_HOME%\nls\data.

New unicode character set is already installed.To use it, do the following:

1. Start->Run->regedit->HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_…-> find and modify NLS_LANG value to AMERICAN_AMERICA.newCharacterSetName(this name can be  found in .nlt file in a  <name> tag for me it is GE8PC866)

2.Open PL/SQL Developer->choose Tools->Preferences…->Fonts->Select-> and then choose Font:.

Retrieve Web-Page Content as XML format in Oracle

In Oracle, there exists a package UTL_HTTP, by which you can make HTTP requests directly from database.
You can use this package if you want to read a webpage.

I will write a simple script, which will send a request to google site and retrieve its content.

declare
v_content  long;
v_url           varchar2(40):=’www.google.com’;
begin
v_content := utl_http.request(v_url);

dbms_output.put_line( v_content );
end;

Note that this code works well if page size is not more than 2GB(Because LONG type saves maximum 2GB).

For larger pages we should use REQUEST_PIECES. By this method you are not limited to 2GB, but  you can indicate maximum bytes by yourself. I will use maximum 100000 bytes.

declare
v_content   utl_http.html_pieces;
v_url            varchar2(25):=’www.google.com’;
begin
v_content := utl_http.request_pieces( v_url,100000);

for i in 1 .. v_content .count
loop
dbms_output.put_line( v_content (i) );
–exit when ( i = 2 );
end loop;
end;

This script will return all content from the web-page. If you want to retrieve just several lines from there, uncomment “exit when ( i = 2 );” and write desired number of lines instead of 2.

Note that the page is returned as an array not as a single variable.

That is all.