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 … Continue reading How to change properties/attributes of partition table?
Tag: modify
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 … 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 … 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, … 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 … 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 … 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 … Continue reading How to find/set max processes allowed per user on OS-level?
How to change unix prompt?
C-shell (csh) Example: set prompt="[%n@%m %c] \! %" oracle@localhost ~] 39 % In this above example sets prompt to display username, hostname, current working directory and history number. %n - username, %m - hostname, %c - pwd, \! - history number K-shell (ksh) Example: $ export PS1='$USER [$PWD] $ ' oracle [/home] $ In the … Continue reading How to change unix prompt?
ORA-28221: REPLACE not specified
When a user name has a profile that has a password verify function set and the user tries to change the password using "ALTER USER" without the REPLACE clause, and also the user doesn't have ALTER USER privilege Oracle will generate the "ORA-28221: REPLACE not specified" error message. So the user needs to include the … Continue reading ORA-28221: REPLACE not specified
Global_names and global_name in Oracle
http://arjudba.blogspot.com/2008/05/globalnames-and-globalname-in-oracle.html To view and change global_name 22:53:34 system@TESTPRD> select * FROM global_name; GLOBAL_NAME ------------------------------------------------------------------------------- TESTPRD.WORLD 22:53:43 system@TESTPRD> ALTER DATABASE RENAME GLOBAL_NAME TO TESTDV.WORLD; 22:54:22 system@ALOCPRD> select * FROM global_name; GLOBAL_NAME ------------------------------------------------------------------------------- TESTDV.WORLD
How to change AWR retention, interval, topnsql?
Using dbms_workload_repository.modify_snapshot_settings one can modify retention, interval and topnsql. -- get the dbid which is needed to passs to dbms_workload_repository.modify_snapshot_settings SQL> select dbid from v$database; DBID ---------- 1992878807 -- retention=>value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour = 64800), max value can be set by passing a … Continue reading How to change AWR retention, interval, topnsql?
Set title of a window
Using the script one can set the title of a window, one of the reason for doing would be distinguish a window from another. The following example was tried on AIX. $ cat settitle.ksh TITLEBAR="33]0;${1} 07" echo $TITLEBAR # in this example the title of the window will change to "Hi" $ ./settitle.ksh Hi
How to change one’s login shell?
Using chsh one can change their login shell. To show the shells that are available $ chsh -l /bin/sh /bin/bash /sbin/nologin /bin/ash /bin/bsh /bin/ksh /usr/bin/ksh /usr/bin/pdksh /bin/tcsh /bin/csh The following shows how the shell for oracle was changed from tcsh to ksh $ cat /etc/passwd | grep oracle oracle:x:501:500:oracle:/home/oracle:/bin/tcsh $ chsh -s /bin/ksh Changing shell … Continue reading How to change one’s login shell?
How to tell if a parameter can be modified in a session/system?
By querying V$parameter one can find if the parameter can be modified in session and/or system. If isses_modifiable is true then value can be modified in session, if FALSE then it can't be modified using ALTER SESSION SET.. If issys_modifiable = DEFERRED, the value can be changed with scope=spfile specified (if spfile is used) and … Continue reading How to tell if a parameter can be modified in a session/system?
How to modify SQL*Plus prompt
undefine usr db col usr new_value usr col db new_value db set termout off select lower(user) usr, instance_name db from v$instance / set termout on set sqlprompt '&&usr.@&&db.> '
alexlima.com 