Script to unlock the schema

_____________________________________________________________________________________________________________________________
REM Script to unlock the schema
set linesize 132
select owner,table_name from dba_tab_statistics where  stattype_locked is not null and owner not in (‘SYS’,’SYSTEM’) ;
/
DECLARE
v_table1 VARCHAR2(30);
v_owner1 VARCHAR2(30);
cursor C1 is select owner,table_name from dba_tab_statistics where
stattype_locked is not null and owner not in (‘SYS’,’SYSTEM’) ;
BEGIN
OPEN C1;
loop
fetch C1 into v_owner1,v_table1;
exit when C1%NOTFOUND;
dbms_stats.unlock_schema_stats(v_owner1);
dbms_stats.unlock_table_stats(v_owner1,v_table1);
end loop;
close C1;
END;
/
select owner,table_name from dba_tab_statistics where  stattype_locked is not null and owner not in (‘SYS’,’SYSTEM’) ;
_____________________________________________________________________________________________________________________________

Free Space SQL

Free Space query
—————

SELECT dbaspace.tablespace_name “Tablespace Name”,
nvl(sum(dbaspace.total_space),0) “Total”,
nvl((sum(dbaspace.total_space) – sum(dbaspace.free_space)),0) “Used”,
nvl(sum(dbaspace.free_space),0) “Free”,
nvl((sum(dbaspace.free_space)/sum(dbaspace.total_space)*100),0) “Free Percentage”
FROM
(
select tablespace_name,0 total_space,sum(bytes/(1024*1024)) free_space
from dba_free_space
group by tablespace_name
union
select tablespace_name,0 total_space,sum(bytes/(1024*1024)) free_space
from dba_temp_files
group by tablespace_name
union
select tablespace_name,sum(bytes/(1024*1024)) total_space,0 from
dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/(1024*1024)) total_space,0 from
dba_temp_files
group by tablespace_name) dbaspace
group by dbaspace.tablespace_name
order by dbaspace.tablespace_name
/

PL/SQL Native Compilation Without Needing a Third-Party C Compiler

PL/SQL Native Compilation Without Needing a Third-Party C Compiler

The benefit of PL/SQL native compilation is automatically available with Oracle Database 11g. No third-party software (neither a C compiler nor a DLL loader) is needed.

Little crunch on plsql_native_library_dir parameter for HP-UX Itanium platform  pointing to some existing  directory , PL/SQL native compilation utilise the directory for
compilation .Note : It has point to existing directory otherwise you can see so many failure (HP-UX Itanium specific)

Insight

“6.1 Location of the Memory Mapped Files”
——————————————
When a PL/SQL unit is compiled using PLSQL_CODE_TYPE=NATIVE in Oracle
Database 11g, the resulting machine code is loaded into an operating system
shared memory object before it is copied to the database catalog.
Additionally, when the native code is fetched from the catalog for execution,
it is first loaded into a shared memory object. On HP, these shared memory
objects are memory mapped files, which are staged in a directory
automatically created by Oracle under the dbs subdirectory of the directory
specified by the environment variable ORACLE_HOME. If you need to change the
location of the staging directory, contact Oracle support.

Tar and Gunzip utilities for production system (General)

TAR and GUNZIP tools are extremely useful when comes to cloning and backup activities .

• tar cvf – * | gzip > appltop.tar.gz — This command will TAR and gunzip the whole appltop .You can use the command according to your requirements

Tar and gunzip

• tar -cvf file.tar directory — Simple Command for TAR
  

ScribeFire makes life easier for Bloggers

ScribeFire makes life easier for Bloggers . ScribeFire is Firefox addon through we can edit ,create and delete the blog contents . Its very handy and lighter to use . Thanks to Firefox .

Running AutoConfig(AC) on particular product

My friend asked me how to run the AC on particular product like AD,GL,..etc . Looks like an good option but I didn’t get any hits how to achieve this .

I think there is no explicit parameter in adconfig.pl where we can pass the value of product top.

After some analysis , I found out some option to run AC for particular product and it works too .

For eg. if you want to run for AD (Applications DBA ) alone.

You can pass the particular top driver file parameter to the adconfig.pl . The command looks like this

—-

perl adconfig.pl driver=/d1/V1210/apps/apps_st/appl/ad/12.0.0/admin/driver/adtmpl.drv

We need to change the <PROD>tmpl.drv and I hope it will works for all products.

This blog is one year old

I take this opportunity to thank everyone encouraged me to start this blog

Difference in registry key for Windows

This topic is common to all . I found its very interesting . It will be helpful to Windows oracle APPS customer.

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet

ControlSet001 = Set of boot information used to boot the system.

ControlSet002 = Set of boot information used for “Last Known
Good” configuration.

CurrentControlSet = Maps to either 001 or 002 above, depending
on how the system was booted. All edits should be done to this set,
as doing so will assure you’re modifying whichever control set is
currently in force.

How to find an AD patchset level ?

SQL> select PATCH_LEVEL from fnd_product_installations where APPLICATION_ID=50;

PATCH_LEVEL
——————————
11i.AD.I

This query works for both 11i and R12 instance

How to merge two apps patches.

Merging apps patches will be useful when you want to reduce the downtime for patch application

Simple steps to merge two patches.

1. Download two required apps patches (pR12_xxxxx_Solaris.zip) and (P12_yyyyy_Solaris.zip).
2. Create one source directory (for e.g. src )
3. Unzip two apps patches into src directory.
4. Create one destination directory (for e.g. dest)
5. Source the Apps env and issue the following command.
   1. Perl <ad_top>/bin/admerge.pl –s src –d dest
6. Patch is merged and created u_merged.drv . Use this driver file for adpatch application