Sunday, March 27, 2011

How to: working with SA account in SQL Server 2008

1. To rename sa account from default "sa" to new name

alter login sa with name=securesa
go

2. Disable sa account

alter login sa disable
go

4. Enable sa account

alter login sa enable <---- same as alter login [sa] enable
go


5. Reset password for sa account

alter login sa with password=N'new_password_here'
go

Saturday, March 5, 2011

Estimate SYSAUX tablespace sizing


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production



SQL> @?/rdbms/admin/utlsyxsz.sql


This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name utlsyxsz.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
11:20:19 on Mar 05, 2011 ( Saturday ) in Timezone +08:00


DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR
----------- ---------------------------------------- ----- ----------------- ---
* ORCL vmxavsvr.lab.dbaglobe.com - Linux IA (32 1 09:23:56 (03/05) NO
-bit)


~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 989.4 MB
|
| Total size of SM/AWR 122.9 MB ( 12.4% of SYSAUX )
| Total size of SM/OPTSTAT 19.8 MB ( 2.0% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE 353.9 MB ( 35.8% of SYSAUX )
| Total size of XDB 125.8 MB ( 12.7% of SYSAUX )
| Total size of EM 80.0 MB ( 8.1% of SYSAUX )
| Total size of SDO 73.6 MB ( 7.4% of SYSAUX )
| Total size of AO 34.1 MB ( 3.4% of SYSAUX )
| Total size of XSOQHIST 34.1 MB ( 3.4% of SYSAUX )
| Total size of SM/ADVISOR 18.8 MB ( 1.9% of SYSAUX )
| Total size of ORDIM/ORDDATA 13.4 MB ( 1.4% of SYSAUX )
| Total size of LOGMNR 12.3 MB ( 1.2% of SYSAUX )
| Total size of XSAMD 8.8 MB ( 0.9% of SYSAUX )
| Total size of SM/OTHER 7.7 MB ( 0.8% of SYSAUX )
| Total size of EXPRESSION_FILTER 3.6 MB ( 0.4% of SYSAUX )
| Total size of TEXT 3.6 MB ( 0.4% of SYSAUX )
| Total size of WM 3.5 MB ( 0.4% of SYSAUX )
| Total size of PL/SCOPE 1.6 MB ( 0.2% of SYSAUX )
| Total size of LOGSTDBY 1.4 MB ( 0.1% of SYSAUX )
| Total size of EM_MONITORING_USER 1.4 MB ( 0.1% of SYSAUX )
| Total size of SMON_SCN_TIME 1.1 MB ( 0.1% of SYSAUX )
| Total size of STREAMS 1.0 MB ( 0.1% of SYSAUX )
| Total size of JOB_SCHEDULER 0.4 MB ( 0.0% of SYSAUX )
| Total size of ORDIM 0.4 MB ( 0.0% of SYSAUX )
| Total size of AUTO_TASK 0.3 MB ( 0.0% of SYSAUX )
| Total size of Others 65.9 MB ( 6.7% of SYSAUX )
|

~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
| - Interval Setting (minutes)
| - Retention Setting (days)
| - Number of Instances
| - Average Number of Active Sessions
| - Number of Datafiles

|
| For 'Interval Setting',
| Press to use the current value: 30.0 minutes
| otherwise enter an alternative
|
Enter value for interval: 60

** Value for 'Interval Setting': 60

|
| For 'Retention Setting',
| Press to use the current value: 30.0 days
| otherwise enter an alternative
|
Enter value for retention: 8

** Value for 'Retention Setting': 8

|
| For 'Number of Instances',
| Press to use the current value: 1.00
| otherwise enter an alternative
|
Enter value for num_instances:

** Value for 'Number of Instances': 1

|
| For 'Average Number of Active Sessions',
| Press to use the current value: 0.08
| otherwise enter an alternative
|
Enter value for active_sessions: 1

** Value for 'Average Number of Active Sessions': 1

| ***************************************************
| Estimated size of AWR: 108.9 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 8.00 days
| Num Instances - 1
| Active Sessions - 1.00
| Datafiles - 8
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Optimizer Statistics History
| we need the following values:
|
| - Number of Tables in the Database
| - Number of Partitions in the Database
| - Statistics Retention Period (days)
| - DML Activity in the Database (level)

|
| For 'Number of Tables',
| Press to use the current value: 150.0
| otherwise enter an alternative < a positive integer>
|
Enter value for number_of_tables:

** Value for 'Number of Tables': 150

|
| For 'Number of Partitions',
| Press to use the current value: 61.0
| otherwise enter an alternative < a positive integer>
|
Enter value for number_of_partitions:

** Value for 'Number of Partitions': 61

|
| For 'Statistics Retention',
| Press to use the current value: 31.0 days
| otherwise enter an alternative < a positive integer>
|
Enter value for stats_retention:

** Value for 'Statistics Retention': 31

|
| For 'DML Activity',
| Press to use the current value: 2
| otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity:

** Value for 'DML Activity': 2

| ***************************************************
| Estimated size of Stats history 43.3 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 150
| Indexes - 292
| Columns - 1,225
| Partitions - 61
| Indexes on Partitions - 201
| Columns in Partitions - 394
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 108.9 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 8.00 days
| Num Instances - 1
| Active Sessions - 1.00
| Datafiles - 8
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 43.3 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 150
| Indexes - 292
| Columns - 1,225
| Partitions - 61
| Indexes on Partitions - 201
| Columns in Partitions - 394
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| For all the other components, the estimate
| is equal to the current space usage of
| the component.
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
| ***************************************************
| Summary of SYSAUX Space Estimation
| ***************************************************

| Est size of SQL_MANAGEMENT_BASE 353.9 MB
| Est size of XDB 125.8 MB
| Est size of EM 80.0 MB
| Est size of SDO 73.6 MB
| Est size of AO 34.1 MB
| Est size of XSOQHIST 34.1 MB
| Est size of SM/ADVISOR 18.8 MB
| Est size of ORDIM/ORDDATA 13.4 MB
| Est size of LOGMNR 12.3 MB
| Est size of XSAMD 8.8 MB
| Est size of SM/OTHER 7.7 MB
| Est size of EXPRESSION_FILTER 3.6 MB
| Est size of TEXT 3.6 MB
| Est size of WM 3.5 MB
| Est size of PL/SCOPE 1.6 MB
| Est size of LOGSTDBY 1.4 MB
| Est size of EM_MONITORING_USER 1.4 MB
| Est size of SMON_SCN_TIME 1.1 MB
| Est size of STREAMS 1.0 MB
| Est size of JOB_SCHEDULER 0.4 MB
| Est size of ORDIM 0.4 MB
| Est size of AUTO_TASK 0.3 MB
| Est size of Others 65.9 MB

| Est size of SM/AWR 108.9 MB
| Est size of SM/OPTSTAT 43.3 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 998.9 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

Wednesday, March 2, 2011

Real time SQL monitoring (requires DIAGNOSTIC+TUNING pack license)


SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> set pages 9999
SQL> select dbms_sqltune.report_sql_monitor from dual;

REPORT_SQL_MONITOR
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */ s.* from sh.sales s,SH.channels c where s.channel_id=c.channel_id and c.channel_desc='Partners'

Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : DONGHUA (150:2043)
SQL ID : 68r55a0ccwgzu
SQL Execution ID : 16777216
Execution Started : 03/02/2011 15:23:11
First Refresh Time : 03/02/2011 15:23:11
Last Refresh Time : 03/02/2011 15:23:35
Duration : 24s
Module/Action : SQL*Plus/-
Service : SYS$USERS
Program : sqlplus@vmxavsvr.lab.dbaglobe.com (TNS V1-V3)
Fetch Calls : 17203

Global Stats
===========================================================================
| Elapsed | Cpu | IO | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
===========================================================================
| 1.42 | 0.54 | 0.29 | 0.60 | 17203 | 18807 | 51 | 13MB |
===========================================================================

SQL Plan Monitoring Details (Plan Hash Value=1652006204)
=======================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=======================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 25 | +0 | 1 | 258K | | | | | |
| 1 | HASH JOIN | | 230K | 536 | 25 | +0 | 1 | 258K | | | 383K | 100.00 | Cpu (1) |
| 2 | TABLE ACCESS FULL | CHANNELS | 1 | 3 | 1 | +0 | 1 | 1 | | | | | |
| 3 | PARTITION RANGE ALL | | 919K | 529 | 25 | +0 | 1 | 919K | | | | | |
| 4 | TABLE ACCESS FULL | SALES | 919K | 529 | 25 | +0 | 28 | 919K | 51 | 13MB | | | |
=======================================================================================================================================================



Related Views: V$SQL_MONITOR, V$SQL_PLAN_MONITOR
Applies to Oracle DB 11gR1 onwards