Database Object for Database space usage

As salamo alaikum wa rahmatullah

DBA_TABLESPACES
DBA_SEGMENTS
DBA_EXTENTS
DBA_FREE_SPACE
DBA_LMT_FREE_SPACE
DBA_THRESHOLDS
DBA_OUTSTANDING_ALERTS
DBA_ALERT_HISTORY
V$ALERT_TYPES

-> 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

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.

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

ADADMIN ?
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.

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

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

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

ADCLONE ?
Utility to clone Insatnce from Test to Prod , Vice Versa

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

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

ADLICMGR ?
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

ADSPLICE ?
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

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

How to Implement VPD on column level

As salamo alaikum wa rahmatullah


You can implement the VPD at column level. If a column with secure data is referenced by a query, you can use column-level VPD to apply a security policy or display the column with NULL values. This approach is different from the row-based VPD. In column-level VPD all rows may be displayed, but the columns may be shielded from the user. You can apply column-level VPD to a table or a view.
To us column-level VPD, specify a value for the sec_relevant_cols input parameters for the ADD_POLICY procedure of DBMS_RLS. For creating policy use the following command:

begin
DBMS_RLS.ADD_POLICY
 ( object_schema=>'PRACTICE',
   object_name=>'Emp',
   policy_name=>'EMP_SELECT_POLICY',
   function_schema=>'PRACTICE',
   policy_function=>'SECURITY_PACKAGE.EMP_SELECT_SECURITY',
   sec_relevant_cols=>'Price');
end;
/
Here  PRACTICE is name of my local schema, Emp is the name of table, and EMP_SELECT_SECURITY is customized policy function and the Price is the name of Column of EMP table.
You have to create a Package as i created by the name of 'SECURITY_PACKAGE'  and i call the Function EMP_SELECT_SECURITY.
  --
Mohammad Shahnawaz
Oracle's Passion

Viewing Privilege and Role Information

As salamo alaikum wa rahmatullah

To access information about grants of privileges and roles, you can query the following data dictionary views:

View	Description
DBA_COL_PRIVS ALL_COL_PRIVS USER_COL_PRIVS	DBA view describes all column object grants in the database. ALL view describes all column object grants for which the current user or PUBLIC is the object owner, grantor, or grantee. USER view describes column object grants for which the current user is the object owner, grantor, or grantee.
ALL_COL_PRIVS_MADE USER_COL_PRIVS_MADE	ALL view lists column object grants for which the current user is object owner or grantor. USER view describes column object grants for which the current user is the grantor.
ALL_COL_PRIVS_RECD USER_COL_PRIVS_RECD	ALL view describes column object grants for which the current user or PUBLIC is the grantee. USER view describes column object grants for which the current user is the grantee.
DBA_TAB_PRIVS ALL_TAB_PRIVS USER_TAB_PRIVS	DBA view lists all grants on all objects in the database. ALL view lists the grants on objects where the user or PUBLIC is the grantee. USER view lists grants on all objects where the current user is the grantee.
ALL_TAB_PRIVS_MADE USER_TAB_PRIVS_MADE	ALL view lists the all object grants made by the current user or made on the objects owned by the current user. USER view lists grants on all objects owned by the current user.
ALL_TAB_PRIVS_RECD USER_TAB_PRIVS_RECD	ALL view lists object grants for which the user or PUBLIC is the grantee. USER view lists object grants for which the current user is the grantee.
DBA_ROLES	This view lists all roles that exist in the database.
DBA_ROLE_PRIVS USER_ROLE_PRIVS	DBA view lists roles granted to users and roles. USER view lists roles granted to the current user.
DBA_SYS_PRIVS USER_SYS_PRIVS	DBA view lists system privileges granted to users and roles. USER view lists system privileges granted to the current user.
ROLE_ROLE_PRIVS	This view describes roles granted to other roles. Information is provided only about roles to which the user has access.
ROLE_SYS_PRIVS	This view contains information about system privileges granted to roles. Information is provided only about roles to which the user has access.
ROLE_TAB_PRIVS	This view contains information about object privileges granted to roles. Information is provided only about roles to which the user has access.
SESSION_PRIVS	This view lists the privileges that are currently enabled for the user.
SESSION_ROLES	This view lists the roles that are currently enabled to the user.

--

Thanking you

Mohammad Shahnawaz

Oracle's Passion