Monday, 19 August 2013

Database Object for Database space usage

As salamo alaikum wa rahmatullah


-> select reason_id, object_type, scope, internal_metric_category,
internal_metric_name from v$alert_types
where group_name = 'Space';

Sample select queries.

-> select tablespace_name, block_size, contents, extent_management from dba_tablespaces;

-> select tablespace_name, count(*) NUM_OBJECTS,sum(bytes), sum(blocks), sum(extents) from dba_segments group by rollup (tablespace_name);

-> select owner, segment_name, tablespace_name,extent_id, file_id, block_id, bytes from dba_extents where segment_name = 'XXX$';

-> select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name;

Thanks and Regards
Mohammad Shahanwaz
oracle's passion

Tuesday, 13 August 2013

Definition of AD utility

As salamo alaikum wa rahmatullah

This top containin Utilities & program code which is very frequently used by DBA’s . So where are these utilities or program in file system ? (I think you all know but for begineers its in AD_TOP/bin )

Lets see one line definition of each

AD Utility ?
AD Utilities are a group of tools designed to install, upgrade, maintain, and patch applications.

is a utility that is used to apply individual patches, mini-packs, or maintenance packs to an Oracle Applications instance.

AD Administration (adadmin) is used performs maintenance/administration tasks on an Oracle Appls Instance. Task associated with adadmin are divided into two broad categories database and file system task.

Utility to determine status of worker (started by adpatch , adadmin or other ad utility) and to control ( restart/drop/skip) workers.

Used to relink apps executables with product libraries , if some library defination is changed or executable is corrupted.

To merge different patches & create single patch . This is helpful when you have too many patches , you can merge them & apply in one shot.

Utility to clone Insatnce from Test to Prod , Vice Versa

To configure different components in Aplications like changing port number or domainname or incresing number of JVM’s

utility to find version of a file in oracle applications (Identification )

To license a product in applications , you might have license to use only 10 products initially then later decided to use 5 more so license them first by this utility

You add a product in application, by default you might have 90-105 ( may be more ) products like AP, GL, AR and now oracle released one more product so add this product using this utility .

This is overview & usages of each application DBA Utility , now in next topic I will start with patching & how these utilities are used & options available with them . If you are amazed/confused whats this patching donot worry I will explain in deatil but if you are a DBA you might already be knowing patching & might have applied coupel of database patches using opatch now lets use ADPATCH to apply application or apps patches.

Thanking you
Mohammad Shahnawaz
oracle's passion

Monday, 12 August 2013

What are re-start file?

As salamo alaikum wa rahmatullah,

The restart files are files used by the managers or workers to restart a failed job. When a job complete the restart files are deleted, but backup files still are saved on the system with the extensions .bak, .bk2 or bk3.

The restart files are located in the $APPL_TOP/admin/<SID>/restart directory.

Thanks and Best Regards
Mohammad Shahnawaz
oracle's passion

Command to check the Trigger at Logon time

As salamo alaikum wa rahmatullah,
conenct / as sysdba

select owner, trigger_name from DBA_TRIGGERS

where trigerring_event like 'LOGON%'

Thank's and Best Regards
Mohammad Shahnawaz
Oracle's Passion

Schema registered with E-Business Suite

As salamo alaikum wa rahmatullah

You can run the following statement to see which schemas are currently registered with the Ebusiness Suite:

select distinct(upper(oracle_username)) sname
from fnd_oracle_userid a, fnd_product_installations b
where a.oracle_id = b.oracle_id
order by sname;

Thanks and Best Regards
Mohammad Shahnawaz
Oralce's Passion