Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

December 2015

How big is my offline datafile ?

Submitted by connor_mc_d on Fri, 2015-12-18 08:26

“Strange” things happen when you take a tablespace or its datafile(s) offline.


SQL> create tablespace TS datafile 'C:\ORACLE\ORADATA\NP12\TS.DBF' size 20m;

Tablespace created.

SQL> alter tablespace TS offline;

Tablespace altered.

SQL> select bytes from dba_data_files where file_name = 'C:\ORACLE\ORADATA\NP12\TS.DBF';

     BYTES
----------

We lose access to the file size. So how can you tell how large that file is, without jumping into the OS or bringing the tablespace online again ?

We can treat the file as a blob.

How do I stop the use of TRUNCATE

Submitted by connor_mc_d on Fri, 2015-12-18 04:10

Truncate is one of those commands that cannot be undone trivially…In fact, you might well be looking at a database recovery in order to get a truncated table back to a “full” state.  So often we’d like to bar the use of it.  Obviously, to truncate a table in another schema you need DROP ANY TABLE which one would hope very very very few people, aka none :-) would be granted, so really this is all about connections as the owner of a table.

Ideally, the solution is part of the application implementation, namely, that people never connect as the schema owner – that’s just good security practice for applications even without considering truncate.

All I Want for Christmas

Submitted by dbakevlar on Thu, 2015-12-17 20:48

I love working for Oracle.  I haven’t found a company with better work/life balance, better benefits or wide variety of technical products for one to learn from.  As we move into the world of cloud, there is one thing that I would like to ask Santa for-  access to the Oracle cloud.

Also Extensions Created Because of SQL Plan Directives Can Invalidate Packages

Submitted by Chris Antognini on Thu, 2015-12-17 17:08
UPDATE 2015-12-18: As metioned by Bryn Llewellyn in this tweet, the invalidation I describe below is caused by bug 19450314. A patch for it is available here for several releases (e.g. it is available for 12.1.0.1.0, 12.1.0.2.1 or 12.1.0.2.13, but not for 12.1.0.2.0). I successfully tested it on 12.1.0.1.0.

The fact that an extension explicitly created by a user through DBMS_STATS can invalidate objects like packages is not new in 12c. It has been like that since the introduction of extensions in 11g. In my opinion, since such an invalidation takes place only when a developer or DBA triggers it, I do not consider it a major problem.

Birmingham City University (BCU) Talk #4

Submitted by oraclebase on Wed, 2015-12-16 11:15

bcuOn Thursday last week, the day after UKOUG Tech15, I did my 4th talk at Birmingham City University (BCU).

In my previous visit I did a talk about community and employability to the staff. This time I did a quicker version of the same talk, but to the students. I’ll be going back a few times this year to do technical sessions.

The Twelve Days of SQLT: Day Two: Miles To Go Before We Sleep

Submitted by iggy_fernandez on Wed, 2015-12-16 04:00

SQLT adoption has been limited even though it is the best tool for investigating SQL performance: It is only available as a download from Oracle Support. It creates tables and PL/SQL procedures in the database. It does not support Statspack, only AWR. (read more)

Going Full Circle – Building a native JSON Database API

Submitted by marco on Tue, 2015-12-15 15:17

Hereby a short post at the end of 2015 that has the presentation used during…

Indexing

Submitted by Jonathan Lewis on Tue, 2015-12-15 11:22

A recent question on the OTN database forum asked:

I have a table with a,b,c,d,e,f,g,h,i,j,k. columns and I have an index on (a,b) columns. There is a sql statement now with “where a= ?” and we are wondering if it could also be good to add a single index on just (a).

Does it help at all? Does it help in some cases?

This is one of those questions where the answer for a perfectly designed and managed system could easily contradict the pragmatic answer for a live system in its current state. That may mean you have to do the wrong thing in the short term while working (possibly very slowly) towards the right thing.  I gave the following (slightly edited) answer on the forum:

The Twelve Days of SQLT: Day One: Do What You Do Do Well

Submitted by iggy_fernandez on Tue, 2015-12-15 04:00

In his fifteen years with Oracle Support, Carlos Sierra created and perfected a tool called SQLTXPLAIN (SQL Tuning and Explain Plan; SQLT for short) for SQL performance investigations. SQLT collects all the information that could possibly be required to investigate SQL performance including environment information, execution plan history, statistics history (system statistics, table statistics, index statistics), and object definitions.(read more)

Two New Oracle Security Presentations Available

Submitted by pete on Mon, 2015-12-14 20:54

I attended the UKOUG conference last week Monday to Wednesday in Birmingham. This is the first year for three years that it has been back at the ICC in the center of Birmingham. The last two years have seen the....[Read More]

Posted by Pete On 14/12/15 At 08:54 PM