How to alter the password expiration policy for certain user.

To alter the password expiry policy for a certain user profile

in Oracle first check wich profile the user is in using:

select profile from DBA_USERS where username = <username>;

Then you can change the limit to never expire using:

alter profile <profile_name> limit password_life_time UNLIMITED;

If you want to previously check the limit you may use:

select resource_name,limit from dba_profiles where profile='<profile_name>';

How to check the space left on the DEFAULT_TABLESPACE of the logged user

SELECT
  ts.tablespace_name,
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
FROM
  user_free_space fs,
  user_tablespaces ts,
  user_users us
WHERE
  fs.tablespace_name(+)   = ts.tablespace_name
AND ts.tablespace_name(+) = us.default_tablespace
GROUP BY
  ts.tablespace_name;

Max Memory Settings for SQL Server 2005/2008

Suggested Max Memory Settings for SQL Server 2005/2008

• Posted on 29 October 2009
• Comments
•   Briefcase
•   Print

It is pretty important to make sure you set the Max Server memory setting for SQL Server 2005/2008 to something besides the default setting (which allows SQL Server to use as much memory as it wants, subject to signals from the operating system that it is under memory pressure). This is especially important with larger, busier systems that may be under memory pressure. 

This setting controls how much memory can be used by the SQL Server Buffer Pool.  If you don’t set an upper limit for this value, other parts of SQL Server, and the operating system can be starved for memory, which can cause instability and performance problems. It is even more important to set this correctly if you have “Lock Pages in Memory” enabled for the SQL Server service account (which I always do for x64 systems with more than 4GB of memory).

These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).

Physical RAM                        MaxServerMem Setting
2GB                                           1500
4GB                                           3200
6GB                                           4800
8GB                                           6400
12GB                                         10000
16GB                                         13500
24GB                                         21500
32GB                                         29000
48GB                                         44000
64GB                                         60000
72GB                                         68000
96GB                                         92000
128GB                                       124000

If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003):

Physical RAM            Target Avail RAM in Task Manager
< 4GB                               512MB – 1GB
4-32GB                              1GB – 2GB
32-128GB                            2GB – 4GB
> 128GB                              > 4GB

You can use T-SQL to set your MaxServerMemory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.

-- Turn on advanced optionsEXEC  sp_configure'Show Advanced Options',1;GO
RECONFIGURE;GO
-- Set max server memory = 3500MB for the serverEXEC  sp_configure'max server memory (MB)',3500;GO
RECONFIGURE;GO

-- See what the current values are
EXEC sp_configure;

You can also change this setting in the SSMS GUI, as you see below:

Finally, I have learned that it is a good idea to temporarily adjust your MaxServerMemory setting downward by a few GB if you know you will be doing a large file copy on your database server (such as copying a large database backup file).

Taken from: http://www.sqlservercentral.com/blogs/glennberry/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008/

How to identify what locked PL/SQL package

select * from v$locked_object lo join dba_objects o on lo.object_id = o.object_id
where o.object_name = 'xxPACKAGE NAMExx' and o.object_type = 'PACKAGE';

ORA-02020: too many database links in use

ORA-02020: too many database links in use

Error :


ORA-02020: too many database links in use


Solution :

Increase the open_links and open_links_instance parameter in the DB . Bounce Database

Or

SQL>alter session close database link "link name";


Thank you

Steps to Configure database in archive log mode

Please follow below steps to configure database in archive log mode.

Check if the database running archive log mode or not by using following query.

SQL> select NAME,LOG_MODE from v$database;

NAME      LOG_MODE
——— ————
RMANDV1   NOARCHIVELOG

There are two init.ora parameters we need to change before configuring database in archive log mode.

1)LOG_ARCHIVE_DEST —/u01/app/archive/rmandv1
2)LOG_ARCHIVE_FORMAT —’rmandv1%t_%s_%r.arc’

alter system set LOG_ARCHIVE_DEST=’/d01/app/archive/rmandv1′ scope =both;

alter system set LOG_ARCHIVE_FORMAT=’rmandv1%t_%s_%r.arc’ scope =spfile;

Step1)Shutdown the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step2)Start  up a new instance and mount, but do not open the database.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  180355072 bytes
Fixed Size                  2094896 bytes
Variable Size              88082640 bytes
Database Buffers           83886080 bytes
Redo Buffers                6291456 bytes
Database mounted.

Step3)Put the database in archive log mode

SQL> alter database archivelog;

Database altered

Now open the database

SQL> alter database open;

Database altered.

check now if your database is in archive log mode.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/archive/rmandv1
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

How to find Oracle Application connection problems

If you can't login into EBS via logon page you have few tools to find the problems:


http://<server>.<domain>:<PORT>/OA_HTML/ServletPing

http://<server>.<domain>:<PORT>/OA_HTML/jsp/fnd/aoljtest.jsp

http://<server>.<domain>:<PORT>/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE

http://<server>.<domain>:<PORT>

http://<server>.<domain>:<PORT>/OA_MEDIA/FNDLOGOL.gif

http://<server>.<domain>:<PORT>/forms/frmservlet

http://<hostname.domainname>:<port>/OA_HTML/fndvald.jsp?username=sysadmin&password=<sysadmin_password>

Good luck.