How to check datafile usage in Oracle Database?

Below is the script script which will list datafile wise Allocated size, Used Size and Free Size
Size details are displayed in MB (Mega Bytes)

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

How to check the tablespace usage in Oracle Database?

Below is the script which will list tablespace wise free space and used space as well as total space.
The Size details are displayed in MB.

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
total_space "Total Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

How to check which tablespace cannot extent causing tablespace full error?

Below is the script to check which tablespace cannot extent causing "tablespace full error"

select a.owner||'.'||a.segment_name "Segment Name",
a.segment_type "Segment Type",
a.bytes/1024/1024 "Size(MB)",
a.next_extent/1024/1024 "Next Extent",
a.tablespace_name "Tablespace Name"
from sys.dba_segments a
where a.tablespace_name not like 'T%MP%' -- Exclude TEMP tablespaces
and next_extent * 1 > ( -- Cannot extend 1x, can change to 2x...
select max(b.bytes)
from dba_free_space b
where a.tablespace_name = b.tablespace_name)
order by 3 desc;

How to check the locked objects in the Oracle Database?

Below is the script to check the locked objects in the Oracle Database

SELECT oracle_username USERNAME,
owner OBJECT_OWNER,
object_name, object_type, s.osuser,
s.SID SID,
s.SERIAL# SERIAL,
DECODE(l.block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global') STATUS,
DECODE(v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(lmode)
) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
and v.session_id = s.sid
ORDER BY oracle_username, session_id;

Note: Run the above select statement as sys user.

How to change SSO Login page to Custom Login Page in Oracle Application server (OAS) ?

To change SSO Login page follow the below steps:

1) The Login page is a jsp file "login.jsp" which can be found in $INFRA_HOME/j2ee/OC4J_SECURITY/applications/sso/web/jsp, you can create your own login file say "my_login_page.jsp" and place it into that directory.

2) Then go to $INFRA_HOME/sso/conf and modify "policy.properties" file (backup the original one first). You have to find the line:
loginPageUrl = /sso/jsp/login.jsp

Replace "login.jsp" with your new login file name "my_login_page.jsp"
loginPageUrl = /sso/jsp/my_login_page.jsp

3) Restart OC4J_SECURITY and HTTP_Server

You can however customize more, check [chapter 12|http://download.oracle.com/docs/cd/B14099_19/idmanage.1012/b14078/custom.htm#i1015535] in the SSO Administration Guide (Creating Deployment-Specific Pages) for more options.

How to Change / Reset ias_admin Password???

— ias_admin is user required to use Enterprise Manager Web (aka Application Server Console - iASConsole) Site.

— ias_admin password is set during Installation of Oracle Application Server (902, 904, 10.1.2.X) or Oracle Identity Management (Infra Tier 10.1.4.X)

—ias_admin account is NOT stored in OID (Oracle Internet Directory), It is stored in XML file (JAZN-XML - Java AuthoriZatioN) (More on JAZN-XML & JAZN-LDAP coming soon..)


You can reset/change ias_admin password in following ways

1. Using Enterprise Manager (Application Server Control) Web Site
–Login to Instance Home Page
–Click on Preferences on top right
–In new screen, click on “Change Password” on left menu
–Enter current password and New Password

2. Using Command line tool
emctl set password <old_password> <new_password>
like
emctl set password welcome1 welcome2
(Here welcome1 is current ias_admin password and welcome2 is new password which you
wish to reset)


If you don’t know current ias_admin password then change it in configuration file

3. Change ias_admin password directly in configuration file
–Backup $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
–Search for entry like below

<user>
<name>ias_admin</name>
<credentials>{903}8QkQ/crno3lX0f3+67dj6WxW9KJMXaCu</credentials>
</user>

and Update new password (welcome1 like )

<user>
<name>ias_admin</name>
<credentials>!welcome1</credentials>
</user>

Note: ! (Exclamation Mark in front of password. This signifies that password is stored in clear text)

Managing Ports in Oracle Application Server (OAS)

The change of ports is required in Oracle Application Sever (OAS) is commonly required when doing any new Installation or reconfiguring the Installation. To change ports in Oracle Application Server (OAS) is a very difficult task and to be done very carefully.

Below is the link that i generally use whenever i require to change any port in Oracle Application Server (OAS).
Note: The below link applies to Oracle Application Server (OAS) 10g.

Managing Ports in Oracle Application Server (OAS)

If you have any queries/doubts, please leave a comment I'll reply ASAP.