Manage
Learn to apply best practices and optimize your operations.
Manage
Learn to apply best practices and optimize your operations.
How to rebuild a built-in Oracle package body
Oracle expert Brian Peasland explains what commands to use in order to rebuild a built-in Oracle package body. Continue Reading
How to fix drop table and truncate table commands frozen in Oracle 10g
Read Oracle expert Brian Peasland's explanation of why drop table and truncate table commands may be frozen in Oracle 10g. Continue Reading
How to return multiple values for THEN clause in an SQL CASE expression
Read this tip from SQL expert Rudy Limeback on how to return multiple values in THEN clause of SQL CASE expression. Continue Reading
-
Writing single-row and multiple-row subqueries
This chapter excerpt explains how to use and write single-row and multiple-row subqueries in SQL, with sample code and exercises. Continue Reading
List the types of SQL subqueries
In this chapter, learn about the three broad divisions of a subquery in SQL: Single-row, multiple-row and correlated subqueries. Continue Reading
Using subqueries in SQL
In this chapter, read examples and sample code for the types of problems that SQL subqueries can solve, including star transformation, generating a table from which to SELECT, generating rows to be passed to a DML statement and more.Continue Reading
Tips for derived tables in SQL and using FULL OUTER JOINs
SQL expert Rudy Limeback gives his strategy for using FULL OUTER JOINs on derived tables in SQL. Read the tip here.Continue Reading
How to write an SQL query for two foreign keys to the same table
A SQL user asks how to write a query for two foreign keys to the same table. Read SQL expert Rudy Limeback's answer here.Continue Reading
How to create an SQL CHECK constraint for two letters
SQL expert Rudy Limeback explains how to create a SQL CHECK constraint for two letters when trying to write a query to retrieve data from two tables.Continue Reading
How to disable Oracle constraints and triggers during an import in 10g
An Oracle user asks expert Brian Peasland if constraints and triggers can be disabled on an Oracle import in Oracle 10g and how to complete this task.Continue Reading
-
Define SQL subqueries
This chapter of "OCA Oracle Database 11g: SQL Fundamentals I Exam Guide" explains how a subquery, a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery, is defined and used in SQL.Continue Reading
How to configure Oracle DB_BLOCK_SIZE and Solaris operating system block size
Read this expert explanation of DB_BLOCK_SIZE vs. operating system block size configuration in an Oracle 10g database operating on Solaris 10.Continue Reading
How to return a zero in SQL instead of no row back for a select count
Want your row to return a zero instead of getting no row backs for some combinations when doing a select count in SQL? Find out how here.Continue Reading
Tips for how to size an Oracle server
Using the usage patterns on an existing server, Database Design and Architecture expert Brian Peasland explains how to size an Oracle server.Continue Reading
How to restore a database using RMAN to Point in Time
An Oracle user asks how to restore a database using RMAN to Point in Time.Continue Reading
How to move data faster with a database link
Oracle expert Brian Peasland explains how to move data across a database link to speed up an import.Continue Reading
How to get history of modified data from Oracle databases
An Oracle user asks how to get the history of data modified from an Oracle database.Continue Reading
Recreating advanced queuing objects during database upgrade
Expert Brian Peasland explains how solving the ORA-28031 error will enable an Oracle user to recreate queue tables.Continue Reading
How to create Datafiles in a Data Guard (10g) environment
OCP Rama Balaji explains how to create datafiiles in a Data Guard (10g) environment. She came across this Data Guard situation lately where a datafile was added to the primary database, but failed during creation on the standby.Continue Reading
How to drop tablespace with missing datafile
Oracle expert Brian Peasland explains how to drop a tablespace with a missing datafile and recreate the tablespace.Continue Reading
How to archive and decommission a database
An Oracle user asks the best way to archive and decommision a 5GB Oracle 7.3 Database.Continue Reading
How to transfer data from Oracle to MS SQL Server
SOA and Integration expert Harish Harbham explains how to transfer data from Oracle to MS SQL Server.Continue Reading
How to use DISTINCT on just one column
SQL expert Rudy Limeback explains how to use DISTINCT on just one column.Continue Reading
Oracle password best practices
Passwords are a critical component of your Oracle security infrastructure.This chapter focuses on establishing a system that ensures that all your passwords are difficult to guess, then configuring protections to thwart password-guessing attacks ...Continue Reading
Data virtualization: The answer to the integration problem?
Data integration is tough, but help is coming to an enterprise near you in the form of data virtualization, according to experts.Continue Reading
How to move tables from system tablespace to user tablespace
I want to move a few tables from the system tablespace to a user tablespace. I have some data on the tables, and I also have some user privileges. I want to retain both.Continue Reading
How to post backup to remote server with RMAN
I need to take a backup of the local server and post that backup to the central server via Internet with RMAN. I can back up the database and connect to the remote database. But I can't find how to post the backup to the remote server.Continue Reading
How to export data by date in Oracle
Is there a way to export data by date?Continue Reading
How to create a database schema in Oracle?
How to create a database schema?Continue Reading
How to find transactions being committed to Oracle database
How to find the transactions being committed? Which database views help to find this?Continue Reading
How to migrate 1.5 TB Oracle database from Solaris to AIX
We have a huge database (1.5 TB) on Solaris (Oracle 10g) and we want to migrate it to AIX -- is expdp the best solution?Continue Reading
Integrity constraints and referential integrity
What is the difference between integrity constraints and referential integrity?Continue Reading
How to create a password file in Oracle 9i?
How to create a password file in Oracle 9i? What is the exact command and do we need to make any changes in the parameter file?Continue Reading
How to export table from one Oracle database to another
I need to export (using the Oracle exp utility) a table from one Oracle database to another Oracle database.Continue Reading
How to read a STATSPACK report
Part of our "How do I do that in Oracle?" series, this tip explains in detail how to read and understand a STATSPACK report.Continue Reading
How to uninstall Oracle
Part of our "How do I do that in Oracle?" series, this tip, compiled largely of advice from our expert Oracle DBAs, explains in detail how to uninstall Oracle software from your system.Continue Reading
How to find Oracle database instance size
How to find out the Oracle database instance size (BG + SGA)?Continue Reading
How to recover if incremental backup fails
If somehow only Tuesday's backup did not run or failed, then can I use the whole week's backup to restore and recover the database? In this situation what would be the backup strategy?Continue Reading
How to import/export Oracle dump file from Windows to Solaris
Can you give me the command prompt to import and export a dump file from Windows 2003 to Sun Solaris?Continue Reading
How to apply an Oracle patch
Part of our "How do I do that in Oracle?" series, this tip explains in detail how to apply a patch or patchset to your Oracle system, with advice compiled largely from installation and upgrades expert Maria Anderson.Continue Reading
Best practice for tablespaces for large tables
What is the best practice for large tables in a database -- separate tablespaces for each big table or put all big tables in one tablespace?Continue Reading
How to improve performance of column update
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four hours and counting. What can I do to improve this performance?Continue Reading
How to create an index using a procedure in Oracle
How can we create an index using a procedure?Continue Reading
Interview questions about managing an Oracle database
I have an interview relating to managing an Oracle database. What types of questions will they ask me?Continue Reading
How to increase SGA_MAX_SIZE in Oracle
How to increase SGA_MAX_SIZE in Oracle? I got the error "ORA-03113: end-of-file on communication channel" during startup.Continue Reading
Patch to upgrade to Oracle 10.2.0.3
I installed Oracle 10.2.0.1 client on Sun Solaris 5.9. Is there any patch to upgrade this 10.2.0.1 to 10.2.0.3?Continue Reading
All employees under a given manager
I want to display all the employees who report to a person. My data is like a tree structure.Continue Reading
Five DBA best practices
Former DBA of the Year Arup Nanda provides five best practices for the Oracle DBA.Continue Reading
How to attach Oracle database backup files to existing server
My client provided backup files of the Oracle database. I know how to import from normal database backups (.dmp) but am still ignorant of attaching data files to the existing server.Continue Reading
Backup and recovery strategy for very large databases
I have two very large databases and I'm looking for a good backup/recovery strategy. One is an OLTP Oracle 9.2.0.5 10TB database and the other is an Oracle 10.2 data warehouse that is 12TB.Continue Reading
Backup strategy for 1TB database
I work for a small firm with less than 400GB of data which is backed up on a daily basis. What is the best backup strategy if the firm expands its database to 1TB?Continue Reading
Difference between migration and upgrade
I believe this is a not a good response to the following question. The manual does not specifically or categorically answer the question proposed. Migrate/export (duplicate copy) is really different from the concept upgrade (replace Oracle version).Continue Reading
How to modify sqlnet.ora file to restrict database access?
I am currently trying to restrict access to my database through listing the IP addresses with permission. I read that I should modify my sqlnet.ora file. The problem is I do not have this file.Continue Reading
How to drop all tables from database with one SQL query
How to drop all tables from the database using one SQL query?Continue Reading
Patch to upgrade from 9.2.0.1.0 to 9.2.0.7.0
Currently we have Oracle 9i (9.2.0.1.0) on Windows 2003 Server and we would like to upgrade to 9.2.0.7.0. Which patch do I have to apply and how to apply the patch?Continue Reading
Opening recovered database with UPGRADE option
I have a backup of an Oracle 8i database which I needed to restore on Oracle 10g Server. I created a new database on the server, placed all the datafiles and redo logs and then re-created the controlfile and did a recover database using the backup ...Continue Reading
How to insert Arabic data in Oracle?
How to insert Arabic data in Oracle 9i?Continue Reading
Query for locally managed vs. dictionary-managed tablespace
What is the query to find out if the tablespace is locally managed or dictionary-managed?Continue Reading
How to replicate an Oracle database to SQL Server?
I am trying to find the best solution for having a database replicated from Oracle to SQL Server without creating an Oracle instance in our machine. This is not a migration; the Oracle database will continue to be used as is, but reporting is to be ...Continue Reading
How to create a database link with an OS Authenticated user?
How to create a database link with an OS Authenticated user?Continue Reading
How to view a list of stored procedures through SQL*Plus?
How can I get a list of stored procedure objects from SQL*Plus? How can I view the source of the stored procedures?Continue Reading
How to estimate database size in Oracle
What are the main considerations for estimating the size of an Oracle database? Are there any rules of thumb? How to estimate the size and number of datafiles, redo log files, control files and archived redo log files?Continue Reading
Creating test cases for upgrade
We are upgrading Oracle and will want to create test cases to verify that the upgrade has not adversely affected the data or the applications that access this data.Continue Reading
How to UNION tables that are not union-compatible
Is there any way to UNION two tables that are not union-compatible?Continue Reading
Relational Database Management System (RDBMS) fundamentals tutorial
New to database administration or development? Here's a quick guide that can get you started by providing background on the relational model and practical advice for working with a relational database management system.Continue Reading
Upgrading PeopleSoft, part 2: Installation and the move to production
The second part of this series on upgrading PeopleSoft delves into data conversion, condensing the time required, and the pain points to watch out for.Continue Reading
Upgrading PeopleSoft, part 1: The first steps
With Project Fusion looming, you need to get up to speed on some of the technology that will be fused into the new apps suite. This tip explains a PeopleSoft upgrade.Continue Reading
How to avoid resource busy error
How to get the lengths of values in "long" datatype
How to connect two databases to one listener
The Top 10 (more or less) J2EE best practices
Much has been written in recent years about J2EE best practices, including books and articles offering insight into how J2EE apps should be written. Here are the best.Continue Reading
System performance slow after SAP ERP upgrade
How to send consolidated invoices
Understanding autoallocate and segment space management
How to reuse space in Oracle
How to work with deferred constraints
Here's how to defer a unique key constraint until the end of the transaction.Continue Reading
What are modern database management system's applications?
How to view report output generated by another user
How to determine what DDL changes have been made
Manage the Windows tnsnames.ora file location
Database administration tip #4 from "30 tips in 30 minutes," brought to you by the IOUG.Continue Reading
Six tips for managing outsourcing relationships
Outsourcing your critical IT operations to one vendor is harrowing enough, but what do you do when two vendors have their fingers in the pie?Continue Reading
PL/SQL control structures: Best practices
Here are several best practices you should take into account when you work with IF, FOR, WHILE, and GOTO statements in PL/SQL.Continue Reading
How to validate zip code, city, and state code
How to retrieve a DB2 date in Julian format
How to find which stored procedure is currently running
Deciding how to handle in-doubt transactions
This tip can help you decide how to handle in-doubt transactions.Continue Reading
How to find VARCHAR2 columns that contain only numbers
Check if a VARCHAR2 column contains a valid number using a pure SQL statement.Continue Reading
How to run two or more databases in different time zones on the same machine
How to run two or more databases in different time zones on the same Unix machine.Continue Reading
How to modify the snapshot structure without dropping the table
Here's a script to modify the structure of the snapshot for Oracle replication without dropping and recreating the snapshot.Continue Reading
How to convert milliseconds to the proper date format
How to convert milliseconds in Oracle to the proper date format.Continue Reading
Best backup strategy for a data warehouse
How to find carriage returns in data
CARRIAGE RETURNS basically screw up records and create problems while processing them. Here's how to find them.Continue Reading
How to avoid 'mutating table' errors
A "mutating table" error can be avoided by writing a procedure/function that declares the pragma autonomous_transaction.Continue Reading
Checking internal concurrent manager status
How to check which process belongs to which database by comparing the parent process with the Internal Manager process.Continue Reading
How to find longest running transaction in Sybase
This tip allows you to access the table that tracks server transactions.Continue Reading
How to speed up loading production data into test database
How to resize the columns in a given table
How to find the first 5 highest salaried employees in each department
How to get the maximum value for a row
How to select the highest grade from list of names and ID's