Visit us often. Computer tips updated
daily. Click here to--> "Tell a friend" so they can get updated
computer tips, too. Please visit our clients, as they support the
computer tips page.
If you would like to submit a tip send us an email with
your tip to
Avoid over-dependence on SQL*Plus with the Group By and Rollup SQL
- While the SQL*Plus language is an invaluable tool, it’s
important not to get so dependent on it that you forget what to do if it isn’t
available. For example, following is a nifty script that computes the amount
of storage space (in bytes) taken up by your own database objects, such as
tables or indexes. The script is composed of two parts. The first three lines
are SQL*Plus commands. The next two lines make up the SQL query.
- column sum(bytes) heading BYTES format 999,999
compute sum of bytes on report
break on report skip 1
- If you run these lines in SQL*Plus or iSQL*Plus, the output
appears like this:
- SEGMENT_NAME BYTES
- But if you type this script in HTML DB, JDeveloper’s SQL
Worksheet, Enterprise Manager’s SQL Scratchpad, or some non-Oracle
application, you’ll probably get the error message ORA-00900: invalid SQL
statement because of the three SQL*Plus commands at the top.
Fortunately, SQL is more powerful today than it was when SQL*Plus was
originally designed. You can produce approximately the same report with
straight SQL, as shown here:
- select segment_name,
to_char(sum(bytes),'999G999') as BYTES
group by rollup (segment_name)
Detect SQL and bind variables with extended auditing (10g)
- Oracle introduced a useful auditing capability in Oracle
10g called extended auditing. With this feature enabled, Oracle writes all
statement information to the audit row, including the text of the SQL
statement and any bind variables used. This is a great help if you’re trying
to diagnose a security breach or application problem.
- To configure extended auditing, set the AUDIT_TRAIL
parameter to DB_EXTENDED. For instance, with a server parameter file, you can
change this so it takes effect after restarting the database, like so:
- ALTER SYSTEM SET AUDIT_TRAIL = DB_EXTENDED SCOPE=SPFILE;
- For example, consider a SQL statement that inserts data
into a table named EMP. With extended auditing turned on, configure auditing
on the EMP:
- AUDIT INSERT on SAMPLE.EMP BY ACCESS;
- Now, suppose someone issues the following INSERT statement:
- INSERT INTO sample.emp VALUES(:id, :lname, :fname);
- If the bind values are 1, 'SMITH', and 'KENN', this will
store both the SQL and bind variables into the audit trail. To see this, issue
the following SELECT statement:
- SELECT sqltext, sqlbind FROM sys.aud$;
- Oracle returns a row containing two values similar to the
- INSERT INTO sample.emp VALUES(:id, :lname, :fname)
#1(1):1 #2(5):Smith #3(5):Kenny
Avoid the hassle of escaping quotes with user-defined quotes
- You’re probably quite familiar with the practice of
escaping quotes and other special characters in code. But it can sure be a
pain if you or your program has to insert lengthy segments of text.
Fortunately, Oracle10g eliminated the necessity of escaping quotes in SQL
statements by introducing the ability to have user-defined quote characters.
Prior to Oracle10g, if you wanted to include quotes in text, you had to escape
the quote with another quote such as:
- SELECT 'Pinnacle''s p''s and q''s text' from dual;
- Now with Oracle10g, you can rewrite this as:
- SELECT Q'$Pinnacle's p's and q's text$' from dual;
- Note that the quoted strings starts with the letter Q,
followed by a single quote and the new quote character. It ends with the new
quote character and a single quote. We used a dollar sign ($) as our quote
character, but you can use other characters if you’d like. Now you can put any
quoted text in between your quote characters.
- CREATE or REPLACE PROCEDURE proc_test (pi_name VARCHAR2)
DBMS_OUTPUT.PUT_LINE('Name is: '||pi_name);
- Then invoke the procedure in an anonymous block such as:
l_var varchar2(100):= Q'$Pinnacle's p's and q's text$';
- The output is:
- Name is: Pinnacle's p's and q's text
Simplify client installs with Instant Client (10g)
- If you need to put an Oracle client on several
workstations--or if you simply want the Oracle client on a flash drive--Oracle
now has a solution to fit your needs. With 10g, Oracle has introduced Instant
Client. It’s the barebones software for making a connection to Oracle.
- In addition to the platform downloads, there are several
Instant Client packages you can download from Oracle, such as Basic, Basic
Light, JDBC, and SQL*Plus, among others (installing Instant Client requires at
least the Basic or Basic Light package). Each package is a small ZIP file and
doesn’t require installation itself. Instant Client and the packages are
- You can use Instant Client for a variety of purposes, such
as on Linux to allow Tora to connect to a database, or on Microsoft Windows as
a quick way to use SQL*Plus from a flash drive.
- To use Instant Client:
- 1. Unzip the files
- 2. Add the location of Instant Client to your path, along
with TNS_ADMIN, LD_LIBRARY_PATH, SQL_PATH and NLS_LANG.
- To utilize Instant Client on a flash drive, set up a quick
batch file for Windows in order to set the variables:
- SET PATH=%CD%;%PATH
- This is necessary because you typically don’t know which drive letter it will
map to from system to system.
- You should also modify the SQLPATH and NLS_LANG variables to fit your
environment. Then, simply open up a command prompt (cmd), cd to the client
directory, and run your batch file.
Tighten security by customizing the software owner account
- For new database installations, consider picking a user other than oracle
to own the software. Making the switch for existing installations can be a
major pain and probably isn’t worth the trouble if the rest of the security
strategy is sound. Discourage routine use of the account that owns the Oracle
Grant permissions without putting your
database in jeopardy
- If you're not careful, some workarounds for
granting privileges on schema objects you don't own can create dangerous
security holes. In the more recent Oracle releases, you don't have to use the
old workarounds, so you can grant privileges on other users' objects without
- To help you get the most out of your
database's new security features, we'll:
- Discuss why old-fashioned
permission-granting workarounds cause security problems.
- Present an example showing you how to
grant privileges properly without risky workarounds.
- Show you how to grant this special
privilege to someone else.
Decipher audit trail files more easily
by creating them in XML format (10.2)
- Oracle can write audit information to a
dictionary table or to a file. When it writes standard audit information to a
file, the output is difficult to read and decipher.
- For example, suppose you want to audit any
updates to the SCOTT.EMP table. To do so:
- 1. Change the database parameter named
audit_trail and bounce (restart) the database. The syntax is as follows:
- alter system set
audit_trail = OS scope = spfile;
- 2. Configure the database to audit the
update statements made on the table:
- AUDIT UPDATE ON SCOTT.EMP;
- Oracle stores the audit trail output in a
file with an aud extension that looks like this:
- Audit trail: SESSIONID: "859" ENTRYID: "2"
STATEMENT: "7" USERID: "SCOTT" USERHOST: "CNETICS\KENNY-DESK" TERMINAL:
"KENNY-DESK" ACTION: "103" RETURNCODE: "0" OBJ$CREATOR: "SCOTT" OBJ$NAME: "EMP"
SES$ACTIONS: "------S---------" SES$TID: "52556" OS$USERID: "CNETICS\ksmith"
- To change the operating system audit files
to an XML format, change the audit_trail parameter as before like this:
- alter system set
audit_trail = XML scope = spfile;
- Beginning in Oracle 10g Release 2, Oracle
now writes the audit files in an XML format that various tools can help you
read and manage much more easily.
Fine-tune private memory areas with
- As you may have heard some experienced DBAs
mention, one of the most important tuning parameters available for the
database is SORT_AREA_SIZE. This is how you can specify an area in memory for
a particular user process for sorting, updating bitmap indexes and other
functions. As you might imagine, this becomes particularly significant in data
warehouse environments. Unless you're using the shared server configuration,
formerly known at MTS (multi-threaded server), this memory is allocated in an
area called the PGA. This acronym may raise the pulse of you golf fans out
there, but this PGA stands for Program Global Area. In the dedicated server
configuration, the PGA is separate from the SGA, which is the memory space
Oracle uses for the shared pool and database buffer cache.
Save time by using Show Parameter for
just one parameter (SQL*Plus)
- To view an initialization parameter, it's
easy to get in the habit of issuing show parameters (or show parameter) in
SQL*Plus and then hunting through the whole list to find the one parameter you
- But there isn't really need for all that
extra effort. To save yourself from looking through all those parameters, just
follow the command with the parameter you're interested in, like so:
- show parameter USER_DUMP_DEST
SAMBA for Windows! Share files between
a Windows PC and Linux or UNIX machines
- It may be frustrating if you have to access
from Windows any files on the Linux or UNIX machine hosting your Oracle
database. Fortunately, Microsoft has offered a simple solution through the
program suite called Services For UNIX (SFU).
To help you provide file-sharing capabilities between Windows, Linux, and UNIX
- Explore the different file sharing systems
used by Linux/UNIX and Windows.
- Perform a custom install of SFU that
focuses strictly on gaining file-sharing access from a Windows computer to a
Linux or UNIX system.
- Connect to a UNIX file system from a
Windows computer using SFU.
- Discuss file and folder permissions.
Tip: Secure the infrastructure housing
- Make sure the Oracle host is in a locked
room with limited access, redundant power, and fire protection systems. Don't
just assume that this is the case. Check it out yourself or confirm it with
the administrator responsible for that machine.
- Also take a look at the hardware strategy
involving the database. Security isn't only about hackers; it's also about
availability of the database. If you aren't using RAID or some other
disk-mirroring solution, you should do so. Look at disaster recovery and
determine what would happen if the Oracle host crashed and was offline for
Tip: Keep unauthorized users from
viewing stored database code
- To tighten security on your Oracle database,
you may want to prevent users from seeing the stored source code in the
database. You can do this with the REVOKE command. By default, the ability to
select from the ALL_SOURCE dictionary view is granted to PUBLIC. To remove
this privilege, issue the following command:
- REVOKE SELECT ON ALL_SOURCE
- If you revoke SELECT on this view but want
to grant some users the ability to see database-stored code, grant those users
SELECT capability to some role name of your choice, using the GRANT command:
- CREATE ROLE VIEW_CODE_ROLE;
GRANT SELECT ON ALL_SOURCE
- The users with VIEW_CODE_ROLE will be able
to see any code that they or the VIEW_CODE_ROLE can execute. To provide the
ability to execute all code, you can grant the EXECUTE ANY PROCEDURE system
- GRANT EXECUTE ANY PROCEDURE
- Since EXECUTE ANY PROCEDURE is so powerful,
you may want to place a password on our VIEW_CODE_ROLE role and not make it
available by default to a user upon connection.
Tip: Keep your database safe from
intrusions at all network levels
- You’ve probably often heard that security is
a multi-layered or multi-level concept, and that protection at all layers is
essential for keeping your database secure. But beyond the database itself,
you may wonder what the specific layers are that you and the other IT staff
you work with should be concerned about.
- Moving from the most abstract to the most
specific, we define the major levels of network security as:
- Policy level. This is all about planning,
strategy, and decision-making. At this level, organizations define their
security needs and make rules designed to meet those needs.
- Physical level. This pertains to the
physical security of your network’s components. It includes the
obvious—physically controlling access to workstations, servers, and network
devices such as firewalls, routers, and switches. It also includes
preventing unauthorized persons from obtaining physical possession of or
access to the network cable (or, in the case of wireless networking,
airwaves). Finally, it involves protecting network information that’s in
hard copy form, such as written password lists and network diagrams, as well
as smart cards, tokens, keys to removable hard disks, backup tapes, CDs, and
anything else that pertains to your network.
- Perimeter level. This involves stopping
unauthorized persons or data from entering your local network from across
the internet, through the phone lines, or other outside locations. The most
obvious manifestation of perimeter security is the firewall, but it also
includes network access controls in the form of logon authentication,
intrusion detection systems, network-based anti-virus systems, network-based
spam filtering, remote access quarantine controls and similar methods of
keeping undesirable packets out of the network.
- Host level. This includes security
mechanisms designed to protect individual systems, such as host-based
firewalls, host-based IDS and anti-virus programs, operating system
hardening and security patches, and securing individual applications.
- Data level. This pertains to security of
the data stored in your database, the computer’s file system, or data sent
across the network, and includes encryption technologies, third-party
disk/partition encryption, and IPSec encryption to protect data in transit.
It also includes folder and file level access controls.
- By implementing security at multiple levels
(preferably at all of the levels listed), you avoid the dangers of a single
point of failure when it comes to protecting your data.
Tip: Don’t let hackers figure out
passwords from their hash values
- Your database is in deep trouble if a hacker
can simply reconstruct the passwords based on their hash values. That’s why
you should read Joshua Wright and Carlo Cid’s October 2005 paper “An
Assessment of the Oracle Password Hashing Algorithm,” which they presented at
the SANS Network Security 2005 conference.
- This paper details weaknesses and
vulnerabilities in Oracle’s hashing algorithm for storing database user
passwords. It also describes what you can do to guard your database’s password
values. On Metalink (https://metalink.oracle.com/), you can read a note where
Oracle responds to the issues the paper raises.
- To learn about weaknesses in Oracle database
password hashing algorithms and find out what you can do to prevent the
compromising of your databases:
- * Read the SANS paper at www.sans.org/rr/special/index.php?id=oracle_pass
* Read Oracle’s response on Metalink (Note:340240.1).
- Here’s a summary of Oracle’s recommendations
and other actions you should take right away to protect your database:
- 1. Force password complexity using a
password verification function and profiles.
- 2. Require users to change passwords
regularly via profile limits.
- 3. Limit access to dictionary tables that
display password hash values, such as SYS.USER$ and SYS.USER_HISTORY$.
- 4. Consider Oracle’s Advanced Security
Option or tunneling software for Oracle Net traffic encryption.
- 5. Limit privileges for application server
- 6. Turn on auditing for access to the
- 7. Consider Oracle’s Advanced Security
Option for alternative user authentication.
Tip: Quickly speed up huge, joined
table queries by letting Oracle swap in a materialized view
- Large data warehouse applications often
require queries against huge tables joined together. One way to speed up these
operations is to denormalize the data using a materialized view. Like a table,
a materialized view actually stores data (not just the SQL code to produce the
data, which is what a normal view contains). But unlike a table, Oracle
automatically manages the rebuilding of a materialized view (based on
parameters you set) so the data is up to date.
- Of course, the SQL may come from an
application that didn’t consider the use of materialized views. Since Oracle
8i, you can remedy that situation with the query rewrite feature, which lets
the optimizer decide to run your improved version of the query (i.e., with the
materialized view) instead of the original SQL.
- The following steps show an example of
creating a materialized view for query rewrite.
- 1. Ensure that the database parameter
QUERY_REWRITE_ENABLED is set to True. Restart the database if necessary.
- 2. Make sure the user creating the
materialized view has the Query Rewrite system privilege.
- 3. Create materialized view logs on the
tables that constitute the materialized view:
- Create Materialized View Log
on Sales with Rowid;
Create Materialized View Log
on Region with Rowid;
- 4. Create the materialized view. In the
code below, we create the view in the Region tablespace, and specify
reasonable storage parameters. Note that we’ve included the Rowid values for
the two tables that form the view. We need this to invoke the Fast (i.e.,
incremental) refresh method, which makes Oracle only add changed rows when
it updates the materialized view.
- Create Materialized View
Storage (Initial 1m next 5m pctincrease 0)
Refresh Fast on Demand
Enable query rewrite
Select Region_Name, Sales_Amt, S.Rowid, R.Rowid
>From Sales S, Region R
Where S.Region_id = R.Region_id
And S.Trans_Date > Sysdate -1;
- 5. Finally, analyze the tables that form
the materialized view.
Tip: One more use for DataGuard:
Hassle-free database cloning for reporting (9i+)
- You’re probably familiar with Data Guard
(i.e., Standby Databases) as a database recovery tool. The concept is simple:
Oracle applies redo data from the production database to one or more standby
databases, keeping them synchronized so they can take over if the main
database goes down.
- However, another potential use for Data
Guard is to capture data from a production database for use in a secondary
database for reporting, testing, or data warehousing. This approach eliminates
the need to create time-consuming scripts or programs to transport the data
from one database to another.
Tip: Four ways to get the most out of
- While materialized views--especially with
query rewrite--are a powerful way to speed up big queries, don’t assume that
you’re getting all the performance you could be getting just because you’ve
set them up. In some cases, you may not get any performance gain for your
efforts! Here are four easily overlooked points to make sure that your
investment in materialized views is paying off:
- Don’t forget to create indexes on large
materialized views. These indexes are created in exactly the same way as for
- For very large materialized views,
consider partitioning; you do this the same way as for tables.
- When initially trying the query rewrite
feature, consider using a query with the identical SQL that’s used to define
the materialized view. This ensures that query rewrite won’t be rejected
because of some minor difference in the SQL code.
- Consider using the initialization
parameter QUERY_REWRITE_INTEGRITY. If you set this parameter to
STALE_TOLERATED instead, the optimizer may invoke query rewrite without
considering whether the data in the materialized view is up to date.
Alternatively, setting the parameter to ENFORCED prevents query rewrite if
the materialized view has stale data.
Tip: Build a better case for
clustering by analyzing hardware upgrade costs
- It’s understandable that management may not
want to invest in a lot of changes when it perceives that the window ain’t
broke. Unfortunately, this attitude may cause your organization to miss out on
high availability and other advantages of a clustering solution, such as RAC.
- One consideration that may make RAC more
attractive to your business is if you look at machine upgrade costs over time.
With a cluster, you may be able to stay up to the latest hardware standards by
upgrading one or two boxes a year. Compare that with a mainframe solution
where the cost of upgrading happens all at once. Showing the benefits for the
hardware budget can sweeten the case for clustering.
Tip: Two powerful tools make queries
on past data available now (9i+)
- If you recently upgraded to 9i or 10g, you
have powerful new tools for taking snapshots of data from the past. The UNDO
tablespace and its associated UNDO_RETENTION setting give you and your users:
* The ability to run reports from a certain point in time.
* The ability to use a SQL script to re-create accidentally changed or deleted
- The syntax for performing a flashback query
is AS OF. You use this clause together with a data mask,
as in SELECT deptno, sum(Sal) FROM emp GROUP BY deptno AS OF TO_DATE('13-Jul-05'
09:00, date HH:MM);.
- To enable this feature:
- * Set the parameter UNDO_MANAGEMENT=AUTO.
- * Set the UNDO_RETENTION parameter to tell
Oracle how many days back you need it to store old data.
- * Create an UNDO tablespace with ample
room to store the past data.
- * Grant the FLASHBACK privilege on
specific tables, or FLASHBACK ANY TABLE privilege to users and roles who
need to use this feature.
- Note that if the undo data is no longer
available, you’ll receive the dreaded ORA-01555: Snapshot too old error. To
prevent this from happening, increase the setting of UNDO_RETENTION.
Tip: Getting weird errors when
attempting standby database recovery? Try this shortcut (8i)
- When you’re trying to perform a standby
database recovery, the last thing you need is the peculiar errors shown here:
- ORA-00283: recovery session canceled due
- ORA-01670: new datafile 901 needed for
standby database recovery
- ORA-01122: database file 901 failed
- ORA-01110: data file 901:
- ORA-01203: wrong incarnation of this file
- wrong creation SCN
- Unfortunately, such errors can occur
occasionally because the tablespace was dropped somehow and re-created using
- Although Metalink (in note 213864.1) reports
that you should refresh the standby database, we found an easier solution.
Just follow these steps:
- 1. Put the affected tablespace into backup
- 2. Copy the most current tablespace files
from the primary database instance (overwriting the standby).
- 3. Take the primary tablespace out of
- 4. Push the logs to the standby.
- In our case, this solved our problem. You
might need a refresh of the control file as well.
Tip: Easily connect to Oracle from
.NET with the OracleClient namespace (VB .NET/.NET Framework 1.1)
- Visual Basic 6 developers generally relied
on the Microsoft OLE DB provider for Oracle (MSDAORA) or Open Database
Connectivity (ODBC) to retrieve Oracle data using ADO. Well, .NET developers
have another option: the OracleClient namespace.
- The System.Data.OracleClient namespace is
the .NET Framework Data Provider for Oracle. However, you should know that VB
.NET doesn’t automatically expose the library when you create a new project.
- Note: Although you can manually create a
reference to the OracleClient library, you can also have VS .NET create it for
you. To do so, open the Toolbox, select the OracleDataAdapter from the Data
tab and add it to your form. If you don’t want to use the component, simply
click the Cancel button on the first form of the dialog wizard and delete the
component from your system tray. The reference remains intact.
- To manually reference the
- 1. Select Project | Add Reference from the
- 2. In the Reference dialog box, click on
the .NET tab.
- 3. In the component list, locate
System.Data.OracleClient.dll, click on it, click the Select button, and
- Once you create the reference, you can use
the OracleConnection, OracleCommand, and OracleDataAdapter classes to access
your Oracle data. The code below shows one way to accomplish this task:
- Dim con As
System.Data.OracleClient.OracleConnection = _
- "User ID = scott; Password = " & _
- "tiger; Data Source = oracle1;")
- Dim myCMD As New
- "SELECT * FROM Emp", con)
- Dim MyDA As New System.Data. _
- Dim ds As New DataSet
- DataGrid1.DataSource = ds.Tables(0)
- This code retrieves all the records from the
Emp table and loads them into a grid named DataGrid1. If you use this code, be
sure to modify the connection information so that it’s valid for your system.
Tip: Speed up your PL/SQL just by
- If you’ve inherited a lot of slow PL/SQL
code, you may be at a loss to figure out how you’re going to speed it all up.
One thing that can help is Oracle Database 10g’s optimizing compiler. In 10g,
Oracle’s PL/SQL compiler automatically performs a degree of optimization.
- So if you’re seeking another reason to
upgrade to Oracle Database 10g, the need to deal with that slow PL/SQL may be
- The compiler has three optimization levels,
as shown in here:
- Level Meaning
- 0 No optimization
- 1 Medium optimization and compile times
- 2 Best optimization; slowest compile times
- Level 2, which gives the most optimization,
is the default. If you need to set the optimization down for any reason (such
as to compile faster), you can use code similar to the following:
- alter session
- set PLSQL_OPTIMIZE_LEVEL = 1;
TO VISIT BUSINESS WEBSITE LINKS'
Home | Company Info | Pricing | Contacts |
Client Directory | Computer
Tips | News |
Business Website Links, LLC
• 8041 Via Hacienda
Palm Beach Gardens
Copyright ©2005 all rights reserved by
Business Website Links, LLC
Web Host and Design by Business Website Links, LLC