“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.
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.
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.
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.
On 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.
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)
Hereby a short post at the end of 2015 that has the presentation used during…
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:
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)
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
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 9 weeks ago
2 years 10 weeks ago
2 years 15 weeks ago
2 years 36 weeks ago
3 years 4 weeks ago
3 years 34 weeks ago
4 years 18 weeks ago
4 years 18 weeks ago