Turn off user password expiration in ORACLE

Hi,

First of all you can check if user password not expire:
 
SELECT LIMIT FROM dba_profiles WHERE resource_name ='FAILED_LOGIN_ATTEMPTS' 
and PROFILE = (select profile from dba_users where username = 'SCOTT');

Replace scott with user you are interested in , if it is unlimited or no rows selected it means it wont expire.

You can disable passsword expiration for specified user:

SELECT PROFILE FROM dba_users WHERE username = 'User you are interested in';

When you know profile name, you can change it:

ALTER PROFILE <profile_name> LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE <profile_name> LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Best regards.

SQL SERVER – FIX : Error 15023: User already exists in current database.

February 15, 2007 by pinaldave

Error 15023: User already exists in current database.

1) This is the best Solution.
First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan.
USE YourDB
GO
EXEC sp_change_users_login 'Report'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘ColdFusion’ is UserName, ‘cf’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.
USE YourDB
GO
EXEC sp_change_users_login 'Auto_Fix', 'ColdFusion', NULL, 'cf'
GO

Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified
USE YourDB
GO
EXEC sp_change_users_login 'update_one', 'ColdFusion', 'ColdFusion'
GO

2) If login account has permission to drop other users, run following T-SQL in Query Analyzer. This will drop the user.
USE YourDB
GO
EXEC sp_dropuser 'ColdFusion'
GO

Create the same user again in the database without any error.

How to create Backup & Delete all rows from a table

Hello,

Sometime you should make backup for a table and then delete all from original table.
You can do it which next steps.

First of all you have check data in original table:
1. select * from CHOICE_VALUES

When you know count of rows this is a time for backup table:
2. create table CHOICE_VALUES_backup as select * from  CHOICE_VALUES;

Delete old data from original table.
3. delete from CHOICE_VALUES;
4. commit;

Good luck....

How to kill terminating/long running/hang request oracle

Action:

1) #First Terminate the Request as follows

         update fnd_concurrent_requests

         set status_code='X', phase_code='C'

         where request_id=10122488;

         commit;

2) #Then change the status with Completed-Error as follows.

         update fnd_concurrent_requests

         set status_code='E', phase_code='C'

         where request_id=10122488;

         commit;

#This will change the status of any request.

#Status Code

E -  Error

X -  Terminate

G -  Warning

Session timeouts, profile options and adconfig for the Oracle eBusiness Suite

Symptoms:

Your log on session is no longer valid. Would you like to log back in so you can continue working? If you do not log in, any outstanding data changes in your forms will not be saved.

Your Session has expired, please login again

Solution:
Set the values of the following profile options appropriately. See Note:171261.1 for details:


ICX:Session Timeout
E.g. From 30 to null (minutes)
ICX: Limit time
E.g. From 10 to 24 (hours)

Remember: When you refresh or clone Test / Development environments the setting for ICX:Session Timeout gets reset!

Also you can solve the issue in $CONTEXT_FILE
located in directory $APPL_TOP/admin/$CONTEXT_NAME.xml.

<session_timeout oa_var="s_sesstimeout">1800000</session_timeout>

And set to the appropriate value. Note that s_sesstimeout is in milliseconds so multiple by (1000*60) to get the time in minutes that is represented in the profile option.
E.g. if you want to set it to 60 minutes then in the context file put value 60 * 1000 * 60 = 3600000. Either that or just set to null:

<session_timeout oa_var="s_sesstimeout">1800000</session_timeout>

Then next time you run adconfig.sh (or a patch/clone does) then you won't have to reset profile options.

How To EXPORT/IMPORT Full DB in ORACLE

Hi,
If you'd like to make full export for your oracle DB, you can use "EXP" command for this.
Here you see examples for "exp" command:

exp system/manager@xyz FULL=Y FILE=FULL.DMP

Here is EXPORT example by OWNER:
exp system@manager@xyz FILE=File_name.dmp OWNER=Owner_Name


You should drop tables before import:
Create file drop_list.sql


set head off
set pagesize 0
set verify off
set feedback off
spool drop_objects.sql
select 'drop table '||table_name ||';' 
from user_tables
/
spool off

Connect with user/password and run this script: sqlplus owner/owner @drop_list.sql
The script will create file drop_objects.sql - this is a list of tables that should be deleted.



When you should import dumped file to other database you can use "IMP" command.
imp system/manager@xyz FULL=Y FILE=FULL.DMP


Here is IMPORT example by OWNER:
imp system@manager@xyz FILE=File_name.dmp OWNER=Owner_Name


Also you can export/import specific schema with same commands with out "FULL" prefix.

How to shrink LOG file in MS-SQL Server 2005/2008

Hi,

There was a problem with Log file was bigger that 40Gb  in my DB.

With these steps I managed to shrink a file:


USE [DBNAME]
GO
CHECKPOINT -- Write checkpoint to the log file, and force SQL to write all dirty pages to the database.
GO
ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE -- Set database to Simple recovery, essentially truncating all the non-active part of the transaction log file.
GO
ALTER DATABASE [DBNAME] SET OFFLINE WITH ROLLBACK IMMEDIATE -- Kill all active transactions/connections rolling back any changes that are outstanding.
GO
ALTER DATABASE [DBNAME] SET ONLINE -- Bring database back online (should have no active connection now).
GO
DBCC SHRINKFILE('LOGICAL_FILE_NAME',0) -- Since we have killed all active transactions, and we have set database to simple recovery mode; all VLF should be in inactive state now; and you should be able to shrink the file.
GO 


Of course you should change backup recovery mode to FULL after this steps.