FRM-10043: Cannot open file / FRM-10044: Cannot create file.

I received this error due to not having executive privilige on that file directory.


We have to have rx privilege on that directory

So it is easy to change privilege by writing

chmod -R 777 [file_directory] by logging in with root username

Then we will have priviliges write/read/execute on every sub-file of directory specified in [file_directory].

But be cautious about granting these priviliges. You might have to ask firstly your DBA.

Simply check the privileges on the directories you try issue on.

FRM-47023: No such parameter named G_QUERY_FIND exists in form [file_name.fmb]

The cause

Customer can check the version of form [file_name.fmb] from $AU_TOP and [file_name.fmx] from $JA_TOP.If there is mismatch then compile the form [file_name.fmb] using the following
code;

1-)Login server with applmngr username

2-)Change the directory to $AU_TOP/forms/US

3-)Execute following statement


f60gen module=<file_name.fmb> userid=apps/[apps_pwd] output_file=$JA_TOP/forms/US/[file_name.fmx]

HOW TO COMPILE FORMS IN 11i

1-)Login to server with applmngr username
2-)set path $AU_TOP/forms/US
3-)Copy .fmb file to $AU_TOP/forms/US
4-)Execute command below

f60gen module=<file_name>.fmb userid=apps/[apps_pwd] output_file=$CUSTOM_TOP/forms/US/<file_name>.fmx


*   $CUSTOM_TOP is a destination which created .fmx file will be stored.

It is either possible to write a custom destination such as;

$CUSTOM_TOP= /apptest/TEST/testappl/xxxt/11.5.0/forms/US

ALTER SESSION SET CURRENT_SCHEMA=APPS

Easy way to work on apps schema

CREATE DATABASE LINK

There are two main ways which are easy to create database links between databases instances.

Format;

create database link [link_name] connect to [user_name] identified by [password]
using [database_connection_string / allias];


1-) If there is an description about database connection inside tnsnames.ora file about the database which will be connected, it is easy by just writing allias names of it in code.
For instance,

create database link test_hamdi to connect hamdi identified by "hamditest18"  using 'TESTDB';

In tnsnames.ora,connection string will be defined as written here;

TESTDB=
   (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=10.1.1.111)
      (PORT=1532)
    )
    (CONNECT_DATA=
      (SID=TEST)
    )



2-)If there is not any description about database connection string inside tns.names.ora file,it is also possible to write directly appropriate string inside linking sql code just like below;

create database link test_hamdi to connect hamdi identified by "hamditest18" using '(DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=10.1.1.111)
      (PORT=1532)
    )
    (CONNECT_DATA=
      (SID=TEST)
    )
  )'


It is assumed that strings above are connection information for TESTDB database.


WARNING: Be sure that you can ping to each databases from tellnet which database link will be created

ROLLBACK TO SAVEPOINT Sample With Oracle9i,Oracle11g Databases Cases

Let's see the reaction of ROLLBACK TO SAVEPOINT a with a scenerio of two different databases below. It is explained in here with sql coding.

Sql Code:
>create table product
(pcode number(2),
pname varchar2(10));

>insert into product values(1,'pencil');

>insert into product values(2,'pencil');

>savepoint a;

>update product set pcode=10 where pcode=1;

>savepoint b;

>delete from product where pcode=2;

>commit;

>delete from product where pcode=10;


>rollback to savepoint a;


>select * from product;

Oracle 9i result;
This is different.

Oracle 11g result;

 No sql statement rollbacked.
 The rollback generates an error.

GRANT FUNCTION/PROCEDURE TO USER / PUBLIC

If you want to grant a user on a function/procedure, it can be used just like here;

grant execute on  [ function name ] / [ procedure name ] to [ user_name] ;

It is also possible to grant all users to execute function/procedure by this code below;

grant execute on [ function name ] / [ procedure name ] to public;

AS OF TIMESTAMP

This is a very useful sql code to retrieve older data which is deleted.

sys.sample_table is a custom table created by sys user.

select *

from sys.sample_table as of timestamp (systimestamp - interval '10' minute);

select *

from sys.sample_table as of timestamp (systimestamp - interval '10' day);

select *

from sys.sample_table as of timestamp (sysdate -1);     -- 1 day ago 

select *

from sys.sample_table as of timestamp (sysdate - 1/24);   --1 hour ago

select *

from sys.sample_table as of timestamp (sysdate -1/(24*60));     -- 1 minute ago

select *

from sys.sample_table as of timestamp (sysdate - 1/(24*60*60));  --1 second ago

It is also good to see result by testing with these sql below and how it works;

select sysdate , (sysdate + 1)  from dual;  -- 1 day later

select sysdate, (sysdate + 1/24) from dual; --1 hour later

select sysdate, (sysdate + 1/(24*60)) from dual; --1 minute later

select sysdate, (sysdate + 1/(24*60*60)) from dual; --1 second later

There are also two different errors prompted while using 

1-)There is one day difference between these two select statements. What causes these two different errors?

select * 

from sys.sample_table as of timestamp (systimestamp - interval '5' day);

ORA - 01466

select *

from from sys.sample_table as of timestamp (systimestamp - interval '6' day);

ORA - 08180

HOW TO MOVE A TABLE FROM ONE TABLESPACE TO ANOTHER

Let's explain this with an scenerio sample.Here are the steps,

1-)Create a table with sys dba privileges.
2-)Create an index on this table.
3-)Add some constraints on table.
4-)Check which tablespace this table and index belongs to.
5-)Move table from one tablespace from one to another
6-)Insert data to table after moving tablespace and investigate reaction of select statement.



1-)

,

2-)

3-) It is also possible to do these constraints while creating table

4-)

 Retrieved data;

5-)

6-)The first two insert statements before changing table's tablespace

Retrieved data;

The last  insert statement after changing table's tablespace from SYSTEM to XXXT.

Error after changing only table's tablespace;

As seen on scenerio above, If there is an index created on table,it is a must to change that indexes' tablespace too. Index remained at its last block. So need to change it;

                                                                   Just leave yourself on way and walk,the way will appear.

HOW TO RESIZE OR ADD TABLESPACE SIZE ?

How to resize an existing tablespace datafile;

alter database

datafile  '<datafile_path/data_file_name>'

resize 150M;

For example;

alter database

datafile '/ora_data/PROD/proddata/xxxt06.dbf'

resize 150M;

How to add a new datafile on partition for an existing tablespace;

alter tablespace

<tablespace_name>

add datafile '<datafile_path/data_file_name>'

size 200M

autoextend on;

For example;

alter tablespace 

xxxt

add datafile '/ora_data/PROD/proddata/xxxt07.dbf'

size 500M

autoextend off;

I have also added  'autoextend on/off' option which is useful.It will manage automatically extending datafile's size.

To see also tablespace autospace management;

select tablespace_name,segment_space_management

from dba_tablespace;

WHICH TABLESPACE BELONGS TO WHICH DATAFILE?

It is possible to see in two ways;

1-)select *

    from dba_data_files;

2-)select df.name,ta.name

    from v$datafile df,v$tablespace ta

    where df.ts#=ta.ts#; 

Data dictionary stuffs.

GET_HOST_NAME,GET_HOST_ADDRESS

These sql codes below provides us to see our instance's database's host name and host address.

1-)Press F5 to see script output.

2-)Other possible use of utl_inaddr.get_host_name and utl_address.get_host_address

CASCADE

drop user USER_NAME cascade;

It also drops the objects created by user,if you do not write cascade it will prompt  ORA-01922 error.

REMAINDER FUNCTION,MOD FUNCTION

Remainder function does the same job with mod.
But valid for 10g,11g,12c

Mod function is valid for 8i,9i,10g,11g,12c database versions.

Some repetittions;

select abs(-10) from dual;

10

select ceil(1.3) from dual;

2

select floor(1.3) from dual;

1

select power(3,2) from dual;

9

select round(22.5) from dual;

23

select round (-22.5) from dual;

-23

select sign(-30) from dual;

-

ORA-06502 PL/SQL: numeric or value error

This problem occured when our periodic request stopped in our E-Bussiness Suite.

System stopped the request. There is not any answer for now.

Inside the request,there was control filling a table each day twice with varchar2 datatype approxametely having 7-10 size.

It means varchar2(7) and varchar(10) for each row.


But in our code varchar2 type which hold these datas has varchar2(100) size. So in there days,it filled up the table with 55 rows. It equals nearly 55x10=550 size.


So,

Our parameter was not enough sized to hold these datas.


This is a case. It might depend on the issue that you are carrying out.

Jinitiator problem 1 ----->Mozilla Firefox !

I had experimented one method that was useful at the begining for internet explorer.
Which is,


1-)Install Java jre
2-)Install Jinitiator
3-)Copy and replace jvm.dll (you can find it from googling) to JInitiator\bin\hotspot path.

But it had once worked but soon  i have had struggles at some computers.

And keep trying on Mozilla Firefox.

Caught up the solution.

Copy files from

JInitiator\bin 

to

Mozilla Firefox\plugins


Close your browser and re-open again. Here we go!

STRANGE TYPE OF PATH WHILE CHANGING DIRECTORY IN LINUX(cd)

In my scenerio, i had to change directory to media (inside CD-ROM) in Linux.

But here is a strange typing of path.
If you have a path with characters without any space between, it is easy. Just writing cd command will shortly direct you.For instance,

>cd /oracle

But what if you have a file with a long name such as 'oracle database information'

you may not be able to go into that file's directory by just writing

>cd /oracle database information

It is a bit different.


For instance i had a cd installed on my desktop with 'OL5.9 x86_64 dvd 20130111'

To go into directory Server which is located inside it, i have to write reverse slash before the spaces inside path then space can be set respectively.

Just like here;

HOW TO CREATE TKPROF FILE FROM A TRACE FILE IN IBM AIX SERVER FOR AN ORACLE APPLICATION FORM

In IBM Aix server system, you have to find firstly the directory of tkprof.sh file which is located under

ora_home/.../bin

How can i find tkprof.sh file ? That is so easy. Write the code as shown below to your console (Putty,SecureCRT...etc) providing connection to your server.

It will show up the directory where tkprof.sh is located.


Next step you will go into that directory with cd (change directory) command

To execute tkproof.sh and receive and output file from your trace file, you have a format which you have to carry out.That is ;


>tkprof  <full_path_of_trace_file> <output_file> explain=apps/apps_pwd sort='(prsela,exeela,fchela)'

But here one thing you have to be aware.

while executing an sh file you need to write './' at the head of file's name.

eg.--> ./tkprof <full_path_of_trace_file> <output_file> explain=apps/apps_pwd sort='(prsela,exeela,fchela)'

Then it will extract the file to the directory of tkprof.sh file.

Here we go.

Oracle client and networking components were not found

One of my collegues had this error while opening a program implemented with .NET platform connected to Oracle database.


Even 64-bit client is installed on computer,it persisted.Because it is required to install 32-bit as well.
The requirement of  installing 32-bit client is about SSIS package installed on remote server is executed with 32-bit client.

SSIS package informations are folded in this link which is relating to SQL Server Integration Services

http://msdn.microsoft.com/en-us/library/ms141134.aspx


Shortly, simple solution is not to forget installing 32-bit oracle client on computer.

RECYCLE BIN,FLASHBACK

After dropping tables,it is feared to lose datas but still recoverable.

Just like in operating systems,dropping tables means throwing recyclebin.

As it is displayed in picture,there you can see the tables dropped with its details.

Let's see something different.

I will somehow recover the datas from COPY_EMP table with its object name like here;

I have created a table named it turkey with two columns; city and plate_number respectively.I manipulated it with insert statement then dropped it.

The flashback is run on to recover table to its previous state. Good one !

Some Sql Queries 4 (MERGE)

  As seen above,it is possible to do more than one data manipulation language (insert,update,delete) statement with merge statement.

when starting with merge statement,manipulation is made on table written after merge statement.
It is easy. Useful. More than one columns are able to be changed or more datas are able to be added or some rows are able to be deleted.

Some Sql Queries 3 (ALTER,INSERT ALL)

---dropping column
alter table hamdi drop column birthdate;
---renaming table's name
alter table hamdi rename to hamdi2;
---renaming column's name
alter table hamdi2 rename column salary to income;


Here,we have insert all statement 

After 'into' statement we wrote some aliases inside 'values' parenthesis that was used in select statement.

In here aliases that were created work as if they are references filled in select sub-query.

Some Sql Queries 2 (INSERT,ALTER)

insert into (select location_id,city,country_id
            from locations )
values (3500,'New_York','US');


I can insert values as shown format above.Normally,this table has more columns than shown inside insert statement but i just wanted to insert specific columns. Rest of them are null at the row.




Here i altered table by adding a new column 'hamdi'. What a simple!

Some Sql Queries 1 (NOT EXISTS,WITH)

NOT EXISTS 

select department_name
from departments outer
where not exists (select '23523532'
from employees
where department_id=outer.department_id);


In here,this query is written regarding the HR Sample Schemas.

I just wanted to use 'not exists function' to understand how it works.

In here query shows the deparments names' which were not used in 'employees' tables.

'not exists' function finds the appropriate coloumn in 'employees' table itself whatever it is. In subquery there is number '23523532',it does not count anything. Here, anything can be written in here. As if the function itself finds whatever needed and sort out the expected result.


WITH ...

1-)
In this example, in memory we create a table which normally does not exist but in memory and we put its name dept_costs having two columns
department_name, dept_total (SUM(e.salary)) with a subquery joinging two different tables which exist in HR database(employees, departments)

2-)
At this part, we create another table with a column named 'dept_avg' in memory from previous table's columns which was also created in memory.

3-)
In this part we sort out a result with these two tables created in memory.

REP-0110 Unable to open file '.....'

There might be more than one reason that causes this issues;

1-)Incorrect REPORT_PATH in registries
2-)Unsufficient privileges to acccess the files' path.

In my case,the user who had this problem could not access the file path due to some OS concerns.
The password user uses expired so needed to be changed to access specified path of file called by report.

HOW TO CHECK CURRENT USER'S PRIVILEGES

Perhaps,you want to create a user whom priveleges will be the same as someone's privileges but you do not know this someone's privileges on database.Here's an helpfull code which will show you the way.

set heading off
set pages 0
set long 9999999
select dbms_metadata.get_granted_ddl('ROLE_GRANT', user)
from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',user)
from dual;
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', user)
from dual;


Just after writing the code above,press F5 button to see magic.

ORA-20000 Oracle text error DRG-10599 column is indexed

I was trying to compare LIKE  operator and CONTAINS function inside a query to analyse their speed to retrieve data.

I used HR Sample Schema table EMPLOYEE.

What i had to do is to create an index on the column which i tried to retrieve data from.

Then the problem was solved.

But the speed differed at each time that i compiled the queries one to another.I could not come up with an overall idea about using CONTAINS function.

I did the same operation with a table which has million datas in it.

Contest won by CONTAINS function by multiplying five times earlier the operator LIKE.



Index is significant. Speed is important. Experience of database you are using considerably makes difference.

<----LEFT JOIN ----------------------- RIGHT JOIN-----> VARIATIONS

I simply used HR tables DEPARTMENTS AND EMPLOYEES

What does left join do?

Simple.

It takes all datas from the left table  even not matching with the datas joined table's joined datas..

What is from the left table? The table at the left side when joining operation is operated.

LEFT JOIN

RIGHT JOIN

ANOTHER VARIATIONS

LEFT JOIN

RIGHT JOIN

LEFT JOIN

RIGHT JOIN

Oracle SQL Developer Unable to Find Java Virtual Machine

When i try to open sqldeveloper interface,it has shown me a prompt to browse java jdk path. I had java jdk installed on my computer which was 64 bit. I browsed the 64 bit java jdk path but it displayed me an error as in picture.

Notice that
sqldeveloper does not accept 64 bit installed java jdk.It has to be 32 bit.

STEPS THAT I HAVE TAKEN
1-)Downloaded jdk_1.6.0_45
2-)Set Java Home in sqldeveloper.conf file path of jdk_1.6.0_45 as shown below.

There you go!

What is the difference between varchar2 and char?

We have  same datatype which is string but can be defined with different names.
These two names are defined whilst writing in pl-sql codes in format just like i showed down here;

varchar2(size)
char(size)

They might contain same string value for instance my name 'HAMDI' but why their definitions are different.

Let's have a look this issue with an example.
Let's imagine i declared them as they are below;

my_name varchar2(30);

my_name char(30);


i assigned them 'HAMDI'

string value 'HAMDI' has 5 characters. H-A-M-D-I

when char with my_name definition hold this data,it creates 30 character space in storage even it has 5 characters in it and it keeps rest 25 characters space still empty on storage.
In short,it tells storage that whatever i have as a value inside me, give me an area or space for 30 characters.


when varchar2 with my_name definition hold this data,it just request until 30 character space but takes a space for 5 characters in storage.
Shortly, I have a limit until 30 characters but i have 5 characters now so i am taking a space for 5 characters now.



if you are seeking for performance,char is better than varchar2

for example,if you want to update and make longer the size of content data,it will easily use fixed-space  for character specified under char but if you do the same operation with varchar2,it will try to find a space in storage.

LOCK,UNLOCK HR SAMPLE SCHEMAS

I will show how to lock and unlock HR sample schemas from different ways

1-)Command Prompt;Locking,Unlocking HR

2-)Sql Developer (Locking HR)

3-)Error after trying to log in with locked HR 

 4-)Unlocking HR

Database Configuration Assistant

Here I have database configuration step by step;

When i configure a database, i used instrunctions written on a book which is written in Turkish Language. I want to advertise this book at end of all pictures because it is worth to do it. It is a good one for beginners. Simple,Readable,Understandable                   

When the subject comes to set up and configurations issues,it is significant. You need to do each step correctly and you need to be aware of what each step means.

                                                 

1-)Click on the next button

2-)Click on the next button

3-)Here we are choosing what kind of reason we are creating this database for.The choosen option is for Online Transaction Processing.

4-)We write the global name of database,we can write also SID(System Identifier) name with global name but  in RAC (Real Application Clusters) it has to be given differently. But with single instance configuration these names might be same.

5-)We set Oracle Enterprise Manager console in here. OEM(Oracle Enterprise Manager) can be load and configured later too. We do not do any action on Automatic Maintenance Task.

6-)Here we have the most administrative users. Instead writing password for each user,we defined same password for all of them.

7-)Here we might have an error telling us that password does not match Oracle recommended password complexity policy. You can change it but clickin on no will take you another step.

 8-)Here we are choosing Storage Type.This is also asked while deinstalling database.I created a folder named data_DBHAMDI under my ORACLE_HOME path.

9-)Due to instructions on book i did not clicked on these two options which will be explained next book.

10-)I did something reverse than the book i followed at this step. I used Sample Schemas and i clicked on this option. Because i need HR tables which come with sample schema tables.It will consume 

11-) Automatic Memory Management was selected and we let oracle to manage it.We could also specify PGA and  SGA. We can do all later.

12-)We chose character set of database.It is 'WE8ISO8859P9'

13-)It can be seen where data files,control files and online redo log files are stored at picture shown below and the locations are possible to be changed.As defaults,two copy control files are created.It is likely to create more than one control file. It is also likely to be able to change the sizes of redlog files and add new ones.It is also possible to do same instructions after database configuration.

14-)Click on Create Database and Finish

15-)Click on OK.

16-)After completing configuration,another screen will be shown which tells database configuration details.

It is possible to unlock users and password change from Password Management.Enterprise Manager links are also given (Database Control URL)

Buy it! It will be helpful!

By the way ; I do not know who the author is personally. (Footnote)