DatabaseJournal.com Staff

Oracle Database keeps records of actions of transactions before they are committed and Oracle needs this information to rollback or Undo the Changes to the database. These records in Oracle are called Rollback or Undo Records. These records are used to Rollback transactions when a rollback statement is issued or during recovery of a database […]

This is a Perl script that generates random orders in the Northwind tables. It does not re-set inventory and it does not take into account the employee territories when generating sales – but it does do a good job of making the orders random enough so that there is little repetition. I needed it to […]

Try this Backup_SP. Replace the IP-address and share-name in the procedure with actual IP address of the network share. Schedule it to run daily and what you find is a full backup of the main database everyday with day-of-week tag. The same procedure can be used without any change on different servers like dev, staging, […]

Here’s a Stored Procedure for SQL Server 7.x/2000 that executes VBScript or JScript inside T-SQL batches, stored procedures or triggers. Author: Gleb Oufimtsev Download Script: sp_run_script.zip Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. […]

Book overview Tom Kyte has a simple philosophy: you can treat Oracle as a black box and just stick data into it or you can understand how it works and exploit it as a powerful computing environment. If you choose the latter course, then you will find that there are few information management problems that […]

This script generates simple pivot tables on data and outputs the final SQL statement via Print. For example, given data as shown below: ID year type amt ———– ———– ———– ———– 7 1999 1 23 8 1999 2 44 9 1999 3 55 10 2000 1 66 11 2000 2 77 12 2000 3 88 […]

This script monitors replication by sending out an alert when a local table has at least 100 records more than the remote (replicated) table. Logic flow: Get names for all user tables into a cursor Build dynamic SQL Store results into temp table Get results from temp table Compare results and send an alert if […]

This script queries the sys tables and gets the same information about indexes for all the tables in a database as sp_helpindex and stores them in two tables created by the script. Quite a useful script if you have a large number of tables in a database with many developers adding, dropping, or modifying indexes. […]

Database and Application management has been the primary goal of Database Administrators for many years. With the introduction of Oracle 9i, Oracle has improved many areas of the management infrastructure to make it easier for the DBA to automate the day to day management aspects of the database. In this article we will look at […]

Here’s a nice and small script which scripts reindex statements for all the indexes on the User tables. The script is flexible enough to allow the user to specify the Fillfactor for the indexes and also to specify the owner of the tables to be reindexed. The script has been tested on SQL Server 7.0. […]

This is a modified version of the sp_shrinkDB procedure posted earlier on Swynk.com. It does the same things as the original but has been changed to accept a database name and a backup path as parameters. It now creates and uses a backup device and also a loop to load values into the temp table. […]

Here’s a script that gets all of the trigger names from the sysobjects table and then returns a result set that builds the SQL statements to drop all of the triggers from your database. Just copy and paste the results into a new window and execute. Author: Michael Crandall Download Script: drop_triggers_cursor.txt Disclaimer: We hope […]