Manage

Manage

Learn to apply best practices and optimize your operations.

• How to rebuild a database to change Oracle block size

  Oracle expert Brian Peasland explains how to rebuild an Oracle database in order to change the block size from 4K to 8K. 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

• 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

• 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

• 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 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

• 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 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 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 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

• Five DBA best practices

  Former DBA of the Year Arup Nanda provides five best practices for the Oracle DBA.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

• 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

• 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

• 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

• How to validate zip code, city, and state code

  Continue Reading

• How to convert milliseconds to the proper date format

  How to convert milliseconds in Oracle to the proper date format.Continue Reading

• How to select the highest grade from list of names and ID's

  Continue Reading