Tuesday, March 29, 2011
Wednesday, March 23, 2011
ORACLE PLSQL - Report Formatting
Changing a Column Heading
COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY'
COLUMN COMMISSION_PCT HEADING COMMISSION
SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
LAST NAME MONTHLY SALARY COMMISSION
------------------------- -------------- ----------
Russell 14000 .4
Partners 13500 .3
COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY'
COLUMN COMMISSION_PCT HEADING COMMISSION
SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
LAST NAME MONTHLY SALARY COMMISSION
------------------------- -------------- ----------
Russell 14000 .4
Partners 13500 .3
Splitting a Column Heading
COLUMN SALARY HEADING 'MONTHLY|SALARY' COLUMN LAST_NAME HEADING 'LAST|NAME'
LAST MONTHLY
NAME SALARY COMMISSION
------------------------- ---------- ----------
Russell 14000 .4
Partners 13500 .3
Setting the Underline Character
SET UNDERLINE =
/
LAST MONTHLY
NAME SALARY COMMISSION
========================= ========== ==========
Russell 14000 .4
Partners 13500 .3
Formatting a NUMBER Column
COLUMN SALARY FORMAT $99,990
LAST MONTHLY
NAME SALARY COMMISSION
------------------------- -------- ----------
Russell $14,000 .4
Partners $13,500 .3
Formatting a Character Column
LAST MONTHLY
NAME SALARY COMMISSION
---- -------- ----------
Russ $14,000 .4
ell
Resetting Column Display Attributes to their Defaults
CLEAR COLUMNS
Suppressing Duplicate Values in a Break Column
BREAK ON DEPARTMENT_ID;
BREAK ON DEPARTMENT_ID SKIP 1
SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY
------------- ------------------------- ----------
20 Hartstein 13000
80 Russell 14000
Partners 13500
90 King 24000
Kochhar 17000
Combining Spacing Techniques
BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID SKIP 1
TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO
Page: 1
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
20 MK_MAN Hartstein 13000
Page: 2
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
------------- ---------- ------------------------- ----------
80 SA_MAN Russell 14000
Partners 13500
Computing and Printing Subtotals
break on DEPARTMENT_ID page nodup on JOB_ID skip 1 nodupCOMPUTE SUM OF SALARY ON DEPARTMENT_ID
DEPARTMENT_ID JOB_ID LAST_NAME SALARY ------------- ---------- ------------------------- ---------- 20 MK_MAN Hartstein 13000 ************* ********** ---------- sum 13000 DEPARTMENT_ID JOB_ID LAST_NAME SALARY ------------- ---------- ------------------------- ---------- 80 SA_MAN Russell 14000 Partners 13500 ************* ********** ---------- sum 27500ExampleCOLUMN DUMMY NOPRINT; COMPUTE SUM OF SALARY ON DUMMY; BREAK ON DUMMY SKIP 1; SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID;
DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 20 Hartstein 13000 ---------- 13000 80 Russell 14000 80 Partners 13500 ---------- 27500 90 King 24000 90 Kochhar 17000 90 De Haan 17000 ---------- 58000Computing Multiple Summary Lines on the Same Break Column
BREAK ON DEPARTMENT_ID COMPUTE AVG SUM OF SALARY ON DEPARTMENT_IDSELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID = 30 ORDER BY DEPARTMENT_ID, SALARY;
DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 30 Colmenares 2500 Himuro 2600 Tobias 2800 Baida 2900 Khoo 3100 Raphaely 11000 ************* ---------- avg 4150 sum 24900Listing and Removing COMPUTE Definitions
CLEAR BREAKSCLEAR COMPUTESSetting the Top and Bottom Titles and Headers and FootersTTITLE CENTER - "ACME SALES DEPARTMENT PERSONNEL REPORT" BTITLE CENTER "COMPANY CONFIDENTIAL"ACME SALES DEPARTMENT PERSONNEL REPORT PERFECT WIDGETS COMPANY CONFIDENTIAL ACME SALES DEPARTMENT PERSONNEL REPORT DEPARTMENT_ID LAST_NAME SALARY ------------- ------------------------- ---------- 30 Colmenares 2500 30 Himuro 2600 30 Tobias 2800 30 Baida 2900 30 Khoo 3100 30 Raphaely 11000 COMPANY CONFIDENTIALSetting Page Dimensions
To set the number of lines between the beginning of each page and the top title, use the NEWPAGE variable of the SET command:SET NEWPAGE number_of_linesSET PAGESIZE number_of_linesSET LINESIZE number_of_charactersCreating a Flat File SET NEWPAGE 0 SET SPACE 0 SET LINESIZE 80 SET PAGESIZE 0 SET ECHO OFF SET FEEDBACK OFF SET VERIFY OFF SET HEADING OFF SET MARKUP HTML OFF SPOOL OFFTo store the results of a query in a file—and still display them on the screen—enter the SPOOL command in the following form: SPOOL file_nameSending Query Results to a Printer First, use EDIT to create the script with your operating system text editor.
EDIT EMPRPTNext, enter the following commands into the file, using your text editor:
SPOOL TEMP CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN DEPARTMENT_ID HEADING DEPARTMENT COLUMN LAST_NAME HEADING 'LAST NAME' COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999 BREAK ON DEPARTMENT_ID SKIP 1 ON REPORT COMPUTE SUM OF SALARY ON DEPARTMENT_ID COMPUTE SUM OF SALARY ON REPORT SET PAGESIZE 24 SET NEWPAGE 0 SET LINESIZE 70 TTITLE CENTER 'A C M E W I D G E T' SKIP 2 - LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' - FORMAT 999 SQL.PNO SKIP 2 BTITLE CENTER 'COMPANY CONFIDENTIAL' SELECT DEPARTMENT_ID, LAST_NAME, SALARY FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID; SPOOL OFFIf you do not want to see the output on your screen, you can also add SET TERMOUT OFF to the beginning of the file and SET TERMOUT ON to the end of the file. run the script EMPRPT:
@EMPRPTSQL*Plus displays the output on your screen (unless you set TERMOUT to OFF), and spools it to the file TEMP:
A C M E W I D G E T EMPLOYEE REPORT PAGE: 1 DEPARTMENT LAST NAME MONTHLY SALARY ---------- ------------------------- -------------- 20 Hartstein $13,000 ********** -------------- sum $13,000 80 Russell $14,000 Partners $13,500 ********** -------------- sum $27,500 90 King $24,000 Kochhar $17,000 De Haan $17,000 ********** -------------- sum $58,000 -------------- sum $98,500 COMPANY CONFIDENTIALRef: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b12170/ch7.htm
Friday, February 25, 2011
Oracle Database 11g New Features
SQL Tuning Improvements in Oracle 11G
- SQL Access Advisor can now make recommendations on how partitioning might be applied to existing tables, indexes, and materialized views to improve an application’s performance.
- SQL statements can now tune themselves automatically, this has come up as a revolution to an oracle databases.
The 10g automatic tuning advisor makes tuning suggestions. 11g automatically apply SQL profiles for statements where the suggested profile gives 3-times better performance that the existing statement.
The performance comparisons are done by a new administrative task during a user-specified maintenance window.
- Statistics for the Cost-Based Optimizer (CBO) are now published separately from being gathered.
- Oracle Database 11g now supports retention of historical execution plans for a SQL statement.
- This means that the CBO can compare a new execution plan against the original plan
- Statistics can be gathered for multiple columns. Collecting statistics for multiple columns gives the CBO the ability to more accurately select rows based on common multi-column conditions or joins.
Result Caches
Oracle database would provide a method to retain the result set in memory from a complex query. example, a list of all Zip Codes and their corresponding Area names for lookup when processing addresses for new customers.
- The SQL query result cache is an area of memory in the Shared Global Area (SGA) that can retain the result sets that a query generates.
- The PL/SQL function result cache can store the results from a PL/SQL function call.
- The results are immediately available for reuse by any user session.
- For user sessions that connect to the database through an application server, the OCI client cache permits those sessions to simply share the results that are already cached on the application server without having to reissue a query directly against the database.
- This suggests that the result data will be cached in the data buffers, and not the intermediate data blocks that were accessed to obtain the query results.
You can cache SQL and PL/SQL results for super-fast subsequent retrieval.
- SQL> Alter table table_name cache results;
New System/Performance Testing Tools in Oracle 11G
Patching the database’s application code, database patch set, or hardware configuration will affect that database’s performance, which usually meant purchasing a third-party package (e.g. Mercury Interactive’s LoadRunner) to generate a sample workload against the database using the next version of the application code,and then comparing the results against baseline performance for the current application code version.
This can be very useful for oracle regression testing.
Database Replay
Database Replay can capture generated workloads from production systems at the database level. Therefore, it’s no longer necessary to run actual application code to duplicate the load on the database, and this also improves accuracy of the simulated workload because it limits or removes other factors like network latency.
These captured workloads can then be replayed on a quality assurance i.e. UAT database so that the impact of application changes, software patches, and even hardware upgrades can be measured accurately. This feature is especially valuable in detecting performance issues that could potentially cause a production database performance that might go otherwise undetected until the changes have been deployed.
SQL Performance Analyzer (SPA)
The SQL Performance Analyzer enhances the existing Oracle Database 10g SQL tuning components.
The SPA provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a performance baseline before a major database or system change, make the desired change to the system,and then replay the SQL workload against the modified database or configuration.
The before and after performance of the SQL workload can then be compared with just a few clicks of the mouse. The DBA only needs to find any SQL statements that are now performing poorly and tune them via the SQL Tuning Advisor.
Repair Advisors
Oracle 10G introduced some of the database performance advisory framework like Undo Advisor, Segment Advisor, SQL Tuning Advisor, SQL Access Advisor, MTTR Advisor, and the best of all Automatic Database Diagnostic Monitor (ADDM). Oracle Database 11g expands this advisory framework by introducing a new Database Repair Advisor.
The main goal of this new Advisor is to locate root causes of a failure, identify and present options for repairing these root causes, and correct the identified problems with self-healing mechanisms.
Fault Diagnostics in Oracle 11G
Oracle Database 11g adds some of improved fault diagnostics mechanism to make it extremely easy for DBA’s who have little exposure of system/database can easily detect and quickly resolve problems using this advance feature. Here are some of the points mentioned:
Automatic Diagnostic Repository
The Automatic Diagnostic Repository (ADR) is at the heart of Oracle Database 11g’s new fault diagnostic framework. The ADR is a central, file-based repository external to the database itself, and it’s composed of the diagnostic data – alert logs, core dumps, background dumps, and user trace files - collected from individual database components from the first moment that a critical error is detected. When critical errors are detected, Oracle automatically creates an “incident” ticket, notifying the DBA instantly.
Automatic Health Monitoring
When a problem within the database is detected, the new Health Monitor (HM) utility will automatically perform a series of integrity checks to determine if the problem can be traced to corruption within database blocks, redo log blocks, undo segments, or dictionary table blocks. HM can also be fired manually to perform checks against the database’s health on a periodic basis.
Hang Manager
Oracle Database 10g introduced the Hang Analysis tool in Enterprise Manager, and Oracle Database 11g now expands this concept with the Hang Manager.
Incident Packaging Service
If the problem can’t be solved using these tools, it may be time to ask for help from Oracle Support.
The new Incident Packaging Service (IPS) facility provides tools for gathering and packaging all necessary logs that Oracle Support typically needs to resolve a Service Request.
Support Workbench
Though it’s stored outside of the database itself, the ADR can be accessed via either Enterprise Manager or command-line utilities.
Once the ADR has detected and reported a critical problem, the DBA can interrogate the ADR; report on the source of the problem, and in some cases even implement repairs through the Support Workbench, a new facility which comes with Enterprise Manager.
Flashback Enhancements in Oracle 11G
Oracle Database 10g database has come up flashback feature to recover the database with the ability to perform an incomplete recovery of the database with Flashback Database enhancement.
Oracle Database 10g also provided four new logical database recovery features: Flashback Table, Flashback Drop, Flashback Version Query, and Flashback Transaction Query.
Oracle Database 11g expands this new feature by introducing two new Flashback features:
Flashback Data Archive
This new feature offers the ability to retain the reciprocal UNDO information for critical data significantly beyond the point in time that it would be flushed out of the UNDO tablespace.
Therefore, it’s now possible to hold onto these reciprocal transactions essentially indefinitely.
Once this feature is enabled, all retained transaction history can be viewed, and this eliminates the task of creating corresponding history tracking tables for critical transactional tables.
And as you might expect, Oracle Database 11g also provides methods to automatically purge data retained in the Flashback Data Archive once a specified retention period has been exceeded.
Flashback Transaction
This is an extension of the Flashback Transaction Query functionality introduced in Oracle Database 10g, Flashback Transaction allows the DBA to back out of the database one or more transactions.
As well as any corresponding dependent transactions by applying the appropriate UNDO statements for the affected transaction’s to the corresponding affected rows in the database.
Automatic Memory Tuning
In 11g, all memory can be tuned automatically by setting one parameter. Prior to Oracle 11g, the DBA set the sga_target and sga_max_size parameters, allowing Oracle to reallocate RAM within the SGA. The PGA was independent, as governed by the pga_aggregate_target parameter.
Now in Oracle 11g we see the memory_max_target parameter which governs the total maximum RAM for both the PGA and SGA regions and the new memory_target parameter which governs the existing sizes. This allows RAM to be de-allocated from the SGA and transferred to the PGA.
As of Oracle 10g, Automatic Memory Management (AMM) only allows shifting of RAM within the confines of sga_max_size. Now in Oracle 11g, we see this new Automatic Shared Memory Management (ASMM).
The memory_target parameter is dynamic (changeable with "alter system" commands), whereby RAM can be de-allocated from an instances SGA/PGA and re-allocated to another instance.
MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed. In a text initialization parameter file, if you omit the line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.
If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero.
After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
Some of the Administration related features are as follows:
Virtual columns
- Oracle 11g virtual table columns are columns that are actually functions.
For Example you can create table as follows:
SQL>create table t1 (c1 number, c2 number, c3 as (c1+c2) virtual);
Faster sorting
Starting in 10gr2 we see an improved sort algorithm, “Oracle10gRw introduced a new sort algorithm which is using less memory and CPU resources. A hidden parameter _newsort_enabled = {TRUE|FALSE} governs whether the new sort algorithm will be used.”
Data Compression
Compression technology that can reduce the amount of storage required for data sets by up to two thirds.
Oracle uses a unique compression algorithm specifically designed to work with relational data. The algorithm works by eliminating duplicate values within a database block, even across multiple columns.
Compressed blocks contain a structure called a symbol table that maintains compression metadata. When a block is compressed, duplicate values are eliminated by first adding a single copy of the duplicate value to the symbol table.
Each duplicate value is then replaced by a short reference to the appropriate entry in the symbol table. Through this innovative design, compressed data is self-contained within the database block as the metadata used to translate compressed data into its original state is contained within the block.
When compared with competing compression algorithms that maintain a global database symbol table, Oracle’s unique approach offers significant performance benefits by not introducing additional I/O when accessing compressed data.
One significant advantage is Oracle’s ability to read compressed blocks directly without having to first uncompress the block. Therefore, there is no measurable performance degradation for accessing compressed data.
In fact, in many cases performance may improve due to the reduction in I/O since Oracle will have to access fewer blocks. Further, you can achieve a more efficient buffer cache by storing more data in the cache without having to increase your memory.
Rman Backups and Datapump exports can also be compressed. Datapump exports can be compressed using the following four options available with compress clause:
- All.
- Data-only.
- Metadata-only.
- None.
New partitioning capabilities in Oracle 11G
- Partitioning by parent/child references.
- Partitioning by virtual columns.
- More composite partitioning options, including
- Range/Range.
- List/Range.
- List/Hash.
- List/List.
Interval partitioning, which allows you to automatically create new partitions based on intervals, such as every month or every day.
In previous versions of Oracle, Composite Partitioning, or the use of subpartition, was permitted but the partition/subpartition combinations were limited.
In Oracle Database 11g, these restrictions have been removed to allow for all combinations of Composite Partitions, including the new Interval Partitions.
Partitioning Matrix
Range List Hash Range YES YES YES List YES YES YES Interval YES YES YES
Enhancements in Database Security
Passwords can be Case Sensitive from Oracle 11G onwards.
Case Sensitivity
11g will support case sensitive passwords automatically, when a user is created with the familiar syntax, Oracle will automatically store a new type of password verifier (hash value) in the data dictionary table that holds the user information. The same happens when a user or DBA changes an existing password.
Strong Authentication
The verifier is based on 160 bit SHA-1 hashing algorithm, this means that in addition to allow for case sensitivity, the hashing algorithm was changed to create a longer and more secure type of verifier that is accepted generally by the industry and which is employed in several widely used security applications and protocols.
Backwards Compatibility
When a database is migrated, only the hash values based on the legacy password hashing algorithm are available, therefore the passwords will remain case insensitive until they are changed.
It is recommended to change all database passwords soon after the migration to 11g as to take advantage of the added security that the case sensitive passwords and the new SHA-1 hashing provide.
A DBA should change the administrative accounts such as the SYS, SYSTEM etc. immediately and can use a password management policy (Profile) to force end-user password changes within a reasonable time.
Administration
The view DBA_USERS has new column PASSWORD_VERSIONS rendered as follows:
decode(length(u.password),16,'10G ',NULL)||NVL2(u.spare4, '11G ' ,NULL) for example:
SQL> select USERNAME, PASSWORD_VERSIONS from dba_users;
USERNAME PASSWORD
Disabling Password Case Sensitivity
When you create or modify user accounts, by default, passwords are case sensitive. To control the use of case sensitivity in passwords for application-to-password file authentication, set the SEC_CASE_SENSITIVE_LOGON initialization parameter. Only users who have the SYSDBA or SYSOPER privilege can set the SEC_CASE_SENSITIVE_LOGIN parameter. Set it to TRUE to enable case sensitivity or FALSE to disable case sensitivity.
For greater security, Oracle recommends that you enable case sensitivity in passwords. However, if you have compatibility issues with your applications, you can use this parameter to disable password case sensitivity. Examples of application compatibility issues are passwords for your applications being hard-coded to be case insensitive, or different application modules being inconsistent about case sensitivity when sending credentials to start a database session.
Password File
Passwords stored in the password file have also become case sensitive and store the same kind of hash value in addition to the old-style verifier, this is reflected by the ignorecase argument to the orapwd utility, it is 'n' (no) by default so when you create a password file for remote sysdba and sysoper authentication, the SYS password will be case sensitive.
Naturally, when you grant SYSDBA to an existing user, check if this user has the 11G type password listed in DBA_USERS.PASSWORD_VERSIONS otherwise Oracle can only store the old-style verifier for that user in the passwordfile and the password will then not be case sensitive.
If the password of the user that was granted SYSDBA or SYSOPER is changed later, Oracle will still store the new verifier and the password will then become case sensitive.
Subscribe to:
Comments (Atom)