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 !