Skip navigation.

DBA Scripts and Articles

Syndicate content
This blog is intended to share my knowledge on Oracle and various scripts that I use as an oracle DBA.
Updated: 14 hours 4 min ago

Keep jobs active with screen command

Thu, 2014-06-12 12:00

What is the screen command ? When you have long running jobs, you need to keep them active when you disconnect from server. The screen command allows you to detach your terminal from you session. By doing this there is no link between your session and your terminal and you can disconnect from server while [...]

The post Keep jobs active with screen command appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Export data to excel

Tue, 2014-05-27 16:59

who never needed to export data to excel from sqlplus ? everybody had one day needed to do that. Sqlplus support the HTML markup which provides an excellent result once opened in excel. With the HTML markup, sqlplus create a table with all columns from your query including the name of your columns as table [...]

The post Export data to excel appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Unindexed foreign keys

Sun, 2014-05-25 16:09

Unindexed foreign keys can lead to bad database performance due to lock contention and full table scans performed on the child table. Here is a diagram which illustrate the situation: In this exemple Oracle needs to lock the entire employees table when the primary key of the departments table is modified, in addition to that [...]

The post Unindexed foreign keys appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

SQL Activity for the last hour

Sat, 2014-05-17 12:59

This script can be used to show the top 10 SQL activity for the last hour. It uses the v$active_session_history view to search top SQL by resource consumption. Top 10 SQL Activity [crayon-53ce84fa1b4f4770649590/] Here is the result you can obtain: and the active sessions history graph for the same period:

The post SQL Activity for the last hour appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Diagnostic and Tuning Pack Components

Tue, 2014-04-29 14:32

I was looking today, for the list of these Diagnostic and Tuning packs components to be sure to not use licensed views in in databases that don’t have those packs. It was not an easy search so I decided to share it with you, if you get lucky you’ll find this page and it will [...]

The post Diagnostic and Tuning Pack Components appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Information Lifecycle Management

Thu, 2014-04-24 14:22

Presentation of Information Lifecycle Management (ILM) With data volume growing, finding adapted storage solutions to storage costs and performance objectives is a real challenge for IT department in large companies. Information Lifecycle management is about managing the data all along its useful life while offering the best performance and storage cost as low as possible. The [...]

The post Information Lifecycle Management appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Result Cache concept and benefits

Tue, 2014-04-22 15:46

This feature was first introduced in Oracle 11g and was meant to increase performance of repetitive queries returning the same data. The Result Cache feature is interesting if your application always look for static data, or data that is rarely updated, for these reasons, it is firstly destinated to Data Warehouses databases (OLAP) as many [...]

The post Result Cache concept and benefits appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Far Sync (Oracle 12c New Feature)

Tue, 2014-04-15 13:50

Oracle Far Sync is an Oracle 12c new feature for Oracle Data Guard. This feature is meant to resolve the performance problems induced by network latency when you maintain a standby database geographically distant of the primary database. In this type of situation you sometimes have to make a compromise between performance and data loss. [...]

The post Far Sync (Oracle 12c New Feature) appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Average Active Sessions (AAS)

Tue, 2014-04-08 08:54

The Average Active Sessions (AAS) metric is a very good indicator of the database activity. This metric represents the number of sessions, either working or waiting for a resource at a specific point in time. Idle sessions are not included in the calculation of this metric. To calculate AAS, we need another metric called “DB [...]

The post Average Active Sessions (AAS) appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Top 10 sessions from v$active_session_history

Mon, 2014-04-07 10:21

Description This query returns the top 10 sessions from v$active_session_history. The result is ordered by total resources consumed by the session including I/O, WAITS and CPU Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. top 10 sessions from v$active_session_history [crayon-53ce84fa1debe432057044/] [...]

The post Top 10 sessions from v$active_session_history appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Oracle instance activity repartition

Fri, 2014-04-04 10:16

Description This query will show you repartition percentage between I/O, WAITS and CPU from the v$active_session_history view Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. Instance activity repartition [crayon-5398ce47da050995799081/] This query returns percentage of I/O, CPU and waits consumed by [...]

The post Oracle instance activity repartition appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs