Pages
Categories
Apex Audit Book review Bugs Character sets Database links Dataguard Email General musings Grid control Installs Linux Materialized views Old Oracle forms Performance tuning PL/SQL RAC Rman Scripts Security Sharepoint Space Spfile SQL*Net SQL Developer SQL server Stats Uncategorized Windows WordpressCategories
- Apex (1)
- Audit (1)
- Book review (2)
- Bugs (6)
- Character sets (6)
- Database links (2)
- Dataguard (1)
- Email (3)
- General musings (6)
- Grid control (2)
- Installs (10)
- Linux (16)
- Materialized views (2)
- Old (1)
- Oracle forms (4)
- Performance tuning (17)
- PL/SQL (4)
- RAC (6)
- Rman (10)
- Scripts (30)
- Security (3)
- Sharepoint (1)
- Space (6)
- Spfile (2)
- SQL Developer (1)
- SQL server (5)
- SQL*Net (4)
- Stats (4)
- Uncategorized (7)
- Windows (7)
- Wordpress (3)
Archives
- April 2012
- March 2012
- February 2012
- December 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- November 2010
- October 2010
- September 2010
- August 2010
- July 2010
- June 2010
- May 2010
- April 2010
- March 2010
- February 2010
- April 2009
- March 2009
- November 2008
- January 2008
- November 2007
- June 2007
- May 2007
- April 2007
- March 2007
- February 2007
- January 2007
Recent Comments
- Shaikh Mujahid on Oracle Developer Suite 10g 9.0.4.0.1 windows software download media
- Ranjith on ORA-38029 Object Statistics Are Locked – due to import with rows=n
- Shaikh Mujahid on Oracle Developer Suite 10g 9.0.4.0.1 windows software download media
- Tom on Fix for TOAD error ORA-02404: specified plan table not found
- fayaz on Oracle Forms popup dialog alert box
Author Archives: Andrew Fraser
Database Password Changes from users web page
This is a Pl/sql wrapper around “alter user” to allow front end interfaces (Servicedesk, Javascript or PHP web pages, etc.) to safely change passwords. CREATE OR REPLACE PROCEDURE sys.php_reset_passwords /*************************** || Name : sys.php_reset_passwords || Author : Andrew Fraser || … Continue reading
Posted in Scripts, Security
Leave a comment
Documentation and Comments
Steve Feuerstein here: There are two forms of code documentation: external and internal. External documentation is descriptive information about a program which is written and stored separately from the program itself. Internal documentation, also known as inline documentation or comments, … Continue reading
Posted in Uncategorized
Leave a comment
Change many oracle database parameters using a temporary pfile/init.ora from spfile
Spfiles are better than pfiles (init.ora’s) for most purposes, since they allow parameters to be changed with “alter system set parameter” SQL commands. However if you want to change a whole load of parameters in one go, it is easier … Continue reading
Posted in Spfile
Leave a comment
List all crons and database links with scripts
I’m currently listing all interfaces – database links and cron jobs – into excel, and used these two scripts to automate the process. Shell script to list all crons (note that this assumes crons in /etc/cron.d run as root): for … Continue reading
Posted in Database links, Scripts
Leave a comment
unlock orcladmin password in shell script
Shell script to check if orcladmin account is locked, and unlock it if required # Check to see if orcladmin account is locked, and unlock it if it is. if [ “`ldapbind -p <myport> -D cn=orcladmin -w <myorcladminpassword>`” = “bind … Continue reading
Posted in Oracle forms, Scripts, Security
Leave a comment
Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”
Indexed views (the SQL Server equivalent of Oracle’s materialized views) are a good performance fix, especially for reporting and data warehouse systems. However they only work with inner joins. Attempts to create an indexed view with outer joins fail with: … Continue reading
Posted in Performance tuning, SQL server
Leave a comment
Oracle range limit AD BC dates
This is the extreme limit for oracle dates: alter session set nls_date_format = ‘Dy DD-Mon-YYYY AD’ ; select sysdate, to_date(’01-JAN-4712 BC’,'DD-MON-YYYY AD’), to_date(’30-DEC-9999 AD’,'DD-MON-YYYY AD’) from dual ; Without encountering this error: ORA-01841: (full) year must be between -4713 and … Continue reading
Posted in Uncategorized
Leave a comment
Oracle SQL Developer privileges for Other Users procedures and package bodies
To view code for procedures and package bodies in SQL Developer that are owned by other users, you need the following privilege: grant select_catalog_role to <user> ; This privilege does not give access to the sys.link$ view, so should be … Continue reading
Posted in SQL Developer
Leave a comment
Oracle Data Guard Overview
Oracle Data Guard is Oracle’s main solution for high availability in the event of a disaster. This overview is for Oracle 11gR2. A pdf version of this page is available here.
Switch Oracle Forms List of Values on and off dynamically at run time
In e.g. post-query block level trigger: IF block.check_field = ‘F’ THEN — switch on Set_Item_Property( ‘block.field’ , LOV_NAME, ‘your lov’) ; Set_Item_Property( ‘block.field’, VALIDATE_FROM_LIST, PROPERTY_TRUE); ELSE — switch off Set_Item_Property( ‘block.field’ , LOV_NAME, ”) ; Set_Item_Property( ‘block.field’, VALIDATE_FROM_LIST, PROPERTY_FALSE); END … Continue reading
Posted in Oracle forms
Leave a comment