How to change properties/attributes of partition table?

At times one would want to change properties/attributes of a table so new partitions are affected by change not the partitions already created, like example compression, change PCTFREE, default tablespace. -- enable compression for all operations and change PCTFREE SQL> ALTER TABLE scott.part_table MODIFY DEFAULT ATTRIBUTES COMPRESS FOR ALL OPERATIONS PCTFREE 5; -- Change default tablespace for new partitions SQL> ALTER TABLE scott.part_table MODIFY DEFAULT … Continue reading How to change properties/attributes of partition table?

Using sys.dbms_iob one can manage jobs in dba_jobs.

DBMS_JOB allows one to create/manage jobs under user who has logged but using sys.dbms_ijob one can manage jobs all jobs scheduled in DBA_JOBS. Here are some of the functions available in sys.dbms_ijob. To execute/run job: You don’t have to be an owner of the job SQL> exec sys.dbms_ijob.run(5); If one tried executing the job not owned by the user using DBMS_JOB, one can’t run it … Continue reading Using sys.dbms_iob one can manage jobs in dba_jobs.

A utility to change oracle schema password using Java

The following java standalone program can be used to change Oracle password. It uses the OCI calls to change the password. /* Copyright (C) 2010-2011 Amin Jaffer This program is free software: you can redistribute it and/or modify. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A … Continue reading A utility to change oracle schema password using Java

How to view/change statistics retention and space used to store stats history?

See the oldest statistics history available SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY FROM DUAL; GET_STATS_HISTORY_AVAILABILITY ————————————————————————— 25-OCT-11 10.00.55.093000000 PM -07:00 See the stats retention SQL> select dbms_stats.get_stats_history_retention from dual; GET_STATS_HISTORY_RETENTION ————————— 31 Modify retention SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60); Space currently used to store statistics in SYSAUX in KBytes, so increasing the retention would affect it’s size SQL> select occupant_desc, space_usage_kbytes from v$sysaux_occupants where OCCUPANT_DESC like '%Statistics%'; OCCUPANT_DESC SPACE_USAGE_KBYTES —————————————————————- … Continue reading How to view/change statistics retention and space used to store stats history?

Changing Oracle password in 11g using ALTER USER IDENTIFIED BY VALUES

In 11g the Oracle the hash password is no longer stored in DBA_USERS, it is stored in SYS.USER$ table in the column “PASSWORD” and “SPARE4”. So there are different ways password can be set depending on if “PASSWORD” and “SPARE4” are set in SYS.USER$ and what you want PASSWORD_VERSIONS to be. If only “SPARE4” is used to set the password the password version is 11g … Continue reading Changing Oracle password in 11g using ALTER USER IDENTIFIED BY VALUES

How to find/modify SQLServer Agent logfile location?

To get the location of SQLServer Agent log file, the log file is called SQLAGENT.out DECLARE @oem_errorlog nvarchar(255) EXECUTE master.dbo.xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’, N’ErrorLogFile’, @oem_errorlog OUTPUT, N’no_output’ PRINT @oem_errorlog Sample Output: C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT To modify location and name of SQLServer Agent logfile USE [msdb] GO EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N’C:\temp\SQLAGENT.OUT’ GO To recycle SQLServer Agent logfile EXEC msdb.dbo.sp_cycle_agent_errorlog When SQLServer Agent recycles the logfile, SQLAGENT.out.1 … Continue reading How to find/modify SQLServer Agent logfile location?

How to find/set max processes allowed per user on OS-level?

On AIX — List number of max processes allowed per user $ /home/oracle:$ /usr/sbin/lsattr -E -l sys0 -a maxuproc maxuproc 2048 Maximum number of PROCESSES allowed per user True The value returned by ulimit may not always match so use the value returned by lsattr. To change the value of user process by running chdev $ chdev -l sys0 -a maxumproc=new_limit Continue reading How to find/set max processes allowed per user on OS-level?