How do I see who is currently connected?

SELECT username, program FROM v$session WHERE username IS NOT NULL;

How to see the database version?

By Radoslav Rusinov

To view components and their versions, loaded into the database:

SELECT comp_name, status, version FROM dba_registry;

To view version numbers of core library components, including the database:

SELECT banner FROM v$version;

To display options that are installed (the value column is ‘FALSE’ if the option is not installed)

SELECT parameter, value FROM v$option;

How to set a table in read-only mode ?

ALTER TABLE emp
ADD CONSTRAINT read_only CHECK (1=1) DISABLE VALIDATE;

It is now, impossible to insert, update or delete anything with this table.

Showing Calendar in SQL*Plus

Similar to UNIX cal command

Thanks to Tony Davis for this tip

Thanks to Mr. Ammar too

Inconsistency between DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_SEGMENTS views

A question has been raised on Technet forum that inconsistent information is being returned from Oracle dictionary views. OP is trying to relate used/free bytes returned by these dictionary views.

Information provided by OP:

Total bytes occupied by data files is: 11,010,048,000
Total bytes available are: 220,200,960
Total bytes used by objects: 10,989,076,480


Free Space + Used Space = 220,200,960 + 10,989,076,480 = 11,209,277,440

So, the sum of “Free Space” and “Used Space” is more than the “Total Space” available to the datafiles. The question is “How can I get more space than the existing one?”

Answer to the question goes here:

I have TEST user in my database and the default tablespace of this user is TEST_TS. I create couple of tables in this schema:

SQL> conn test/test
Connected.
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMPDATA TABLE
TEST_ERRORS TABLE

SQL> create table test1 as select * from tab;

Table created.

SQL> create table test2 as select * from tab;

Table created.

SQL> create table test3 as select * from tab;

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TEST2 TABLE
TEST3 TABLE
EMPDATA TABLE
TEST_ERRORS TABLE

Now, I will drop these newly created tables:

SQL> drop table test1;

Table dropped.

SQL> drop table test2;

Table dropped.

SQL> drop table test3;

Table dropped.

Now, query the dictionary views for used, free and allocated bytes/blocks information:

SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';

BLOCKS
----------
3712

SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
3680

SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
48


Opps, we get more space (3680 + 48 = 3728) than being allocated to the datafiles. Probably, by now you might have arrived to the answer, but let me reveal it to you.

OP hasn’t mentioned the Oracle database version but I am pretty sure it’s Oracle 10g or above. With Oracle database 10g, Oracle has added a new feature of recycle bin. When you drop an object it goes and stays in your recycle bin and will occupy the same amount of space. You need to purge the object to reclaim the space.

This is what is happening in this case. Dropped objects are still lying in the TEST_TS tablespace and being counted by the DBA_SEGMENTS view, whereas, DBA_FREE_SPACE correctly report the free space available.

Let me purge the dropped objects out of the recycle bin and rerun the queries:

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------

TEST1 BIN$BEExbY8xS0aXH3U+e9XPDg==$0 TABLE 2007-12-18:13:42:36

TEST2 BIN$WGV0P4B4TaCUukiPyctTPg==$0 TABLE 2007-12-18:13:42:38

TEST3 BIN$1P4aTA1IR8ijw4btdRkmzw==$0 TABLE 2007-12-18:13:42:39

SQL> purge recyclebin;

Recyclebin purged.

SQL> select blocks from dba_data_files where tablespace_name = 'TEST_TS';

BLOCKS
----------
3712

SQL> select sum(blocks) from dba_free_space where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
3680

SQL> select sum(blocks) from dba_segments where tablespace_name = 'TEST_TS';

SUM(BLOCKS)
-----------
24

Wow, Oracle now reports correctly. The sum of “Free Space” and “Used Space” (3680 + 24 = 3704) is less than the “Total Space” (3712) available to the datafiles.

SQL Injection

Common Mistakes in Oracle PL/SQL Programming

SQL Injection.... WOW

With NO COMMENT

OR aggregate

you want to BIT_OR multiple rows. For example you have a table with 3 rows that you want to aggregate with BIT_OR

1010 (10)
1100 (12)
0110 (6)
=========
1110 (14)

Let’s try


with t as (
select 10 n from dual union all
select 12 from dual union all
select 6 from dual)
select
utl_raw.cast_to_binary_integer(
sys.mvaggrawbitor(
utl_raw.cast_from_binary_integer(
n
)
)
) N
from t;
N
---
14

It is that easy !

What is ROWNUM=1 ?

Is rownum=1 the first row returned? well, it depends :


select rownum, ename
from emp
order by ename;
ROWNUM ENAME
---------- ----------
11 ADAMS
2 ALLEN
6 BLAKE
7 CLARK
13 FORD
12 JAMES
4 JONES
9 KING
5 MARTIN
14 MILLER
8 SCOTT
1 SMITH
10 TURNER
3 WARD

The ROWNUM is evaluated before the order by

select rownum
from emp
group by rownum
having rownum between 3 and 6;
ROWNUM
----------
6
5
3
4

the rownum is selected before the GROUP BY and before the HAVING.

select
rownum,
sal,
median(sal) over ()
from emp;
ROWNUM SAL MEDIAN(SAL)OVER()
---------- ---------- -----------------
1 800 1550
12 950 1550
11 1100 1550
3 1250 1550
5 1250 1550
14 1300 1550
10 1500 1550
2 1600 1550
7 2450 1550
6 2850 1550
4 2975 1550
8 3000 1550
13 3000 1550
9 5000 1550

The optimiser may chose to resort the result, here as a WINDOW SORT operation for the MEDIAN analytic function

random statements

I read the ultimate excuse database and checked if I can use model to do generate random statements:


with t as (
select '%E and %E are travelling to %L.' fmt
from dual union all
select 'About %N employees live in %L.'
from dual)
select str from t
model
reference
dept on (
select loc,count(*) over () c,rownum r
from dept)
dimension by (r) measures (loc,c)
reference
emp on (
select ename,count(*) over () c, rownum r
from emp)
dimension by (r) measures (ename, c)
partition by (fmt)
dimension by (1 x)
measures (cast(fmt as varchar2(4000)) str)
rules
iterate (1000)
until str[1] not like '%\%%' escape '\'
(str[1]=substr(str[1],1,
decode(instr(str[1],'%'),0,4000,
instr(str[1],'%')-1))||
decode(substr(str[1],nullif(
instr(str[1],'%'),0)+1,1),
'N',to_char(trunc(
dbms_random.value(0,1+emp.c[1]))),
'E',initcap(emp.ename[trunc(
dbms_random.value(1,1+emp.c[1]))]),
'L',initcap(dept.loc[trunc(
dbms_random.value(1,1+dept.c[1]))]))||
substr(str[1],nullif(
instr(str[1],'%'),0)+2))
/
STR
----------------------------------------
James and Ward are travelling to Dallas.
About 1 employees live in Chicago.
/
STR
--------------------------------------------
King and Blake are travelling to New York.
About 3 employees live in Dallas.

Create edition

In 2006, I blogged about 11g new features part II. At that time 11g was in alpha or early beta stage.

One of the new feature announced at OpenWorld 2006 was multiversioning. Like you have multiple versions of your packages running at the same time.

Unfortunately, the feature has not been implemented in 11g Release 1.

As you can see, the CREATE EDITION command does not work :

SQL> create edition v1;
create edition v1
*
ERROR at line 1:
ORA-00901: invalid CREATE command

SQL> alter session set edition=v1;
ERROR:
ORA-38802: edition does not exist

The error messages ORA-38801 to ORA-38816 refer to EDITIONS.

SQL> select * from dba_editions;
EDITION_NAME PARENT_EDITION_NAME USABLE
------------ ------------------- ------
ORA$BASE YES

SQL> alter session set edition=ora$base;

Session altered.


Apparently the feature is implemented, but not ready to be used. Be patient …

predefined collections

If I need to generate 3 rows called AAA, BBB, CCC, I could use dual and union all. Another method is to use Extensibility Types

select * from table
(sys.ODCIVarchar2List('AAA','BBB','CCC'));

COLUMN_VALUE
------------
AAA
BBB
CCC

my query is not using my index

I read a user question on forums.oracle.com this morning :
As we know NOT EQUAL operations by pass indexes and cause full table scans in queries

I did not know that. Do I need to use hints?

set autotrace traceonly explain
create table t as
select
sign(rownum-1) r, rpad(rownum,4000,'.') t
from dual connect by level<10000/*00*/;
create index i on t(r);
exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

select /*+ INDEX(T,I) */ r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3993571787

--------------------------------------------------------------
----------------------

| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time |

--------------------------------------------------------------
----------------------

| 0 | SELECT STATEMENT | | 1 | 4004 |
1979 (2)| 00:00:24 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4004 |
1979 (2)| 00:00:24 |

|* 2 | INDEX FULL SCAN | I | 1 | |
1979 (2)| 00:00:24 |

--------------------------------------------------------------
----------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("R"<>1)

SQL> select r,t from t where r!=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------
------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |

--------------------------------------------------------------
------------

| 0 | SELECT STATEMENT | | 1 | 4004 | 271K (1)
| 00:54:18 |

|* 1 | TABLE ACCESS FULL| T | 1 | 4004 | 271K (1)
| 00:54:18 |

--------------------------------------------------------------
------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("R"<>1)