If you want to know EVERYTHING related to DUAL table in Oracle, please refer to Tom Kyte’s discussion

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

It’s a wonderful demonstration of Oracle 10gR2 Architecture. As a DBA, you HAVE to learn it by heart 🙂 And draw it minimum 3-4 times 🙂

I’ve posted a new thread on OTN forum. Please share your opinions …

http://forums.oracle.com/forums/message.jspa?messageID=3661528

Today, my Turkish friend Emre Baransel asked a question in OTN Forum related to limiting access of a user to a database based on time interval. He want to prevent a specific user to access to a database between 08 and 22.  I’ve created a logon script and showed how it’s working:

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 28 14:56:58 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> create user kam identified by kam;
 
User created.
 
SQL> grant connect, resource to kam;
 
Grant succeeded.
 
SQL> conn kam/kam
Connected.
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> CREATE OR REPLACE TRIGGER limit_connection
  2     AFTER LOGON ON DATABASE
  3  BEGIN
  4     IF USER = 'KAM' THEN
  5        IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 8 AND 22
  6        THEN
  7           RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 08 and 22');
  8        END IF;
  9     END IF;
10  END limit_connection;
11  / 
 
Trigger created.
 
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
23
 
SQL> conn kam/kam
Connected.
SQL> select to_char(sysdate,'hh24') from dual;
 
TO
--
18
 
SQL> conn kam/kam
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998:  Dear user KAM! You can't login between 08 and 22
ORA-06512: at line 5
 
 
Warning: You are no longer connected to ORACLE.
SQL>

Today, in OTN forum, there was a question relating the recovery of dropped tablespace using Flashback Database. I did a little scenario where you can see the recovery process

C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
 
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 1 14:20:34 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area  293601280 bytes
Fixed Size                  1248624 bytes
Variable Size              96469648 bytes
Database Buffers          192937984 bytes
Redo Buffers                2945024 bytes
Database mounted.
 
SQL> alter database archivelog;
 
Database altered.
 
SQL> alter database flashback on;
 
Database altered.
 
SQL> alter database open;
 
Database altered.
 
SQL> create tablespace tb datafile 'c:\tb.df' size 1m;
 
Tablespace created.
 
SQL> create user tb identified by tb;
 
User created.
 
SQL> grant dba to tb;
 
Grant succeeded.
 
SQL> alter user tb default tablespace tb;
 
User altered.
 
SQL> create table tb (id number);
 
Table created.
 
SQL> select current_scn from v$database;
 
CURRENT_SCN
-----------
     547292
 
SQL> drop tablespace tb including contents and datafiles;
 
Tablespace dropped.
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
Total System Global Area  293601280 bytes
Fixed Size                  1248624 bytes
Variable Size              96469648 bytes
Database Buffers          192937984 bytes
Redo Buffers                2945024 bytes
Database mounted.
 
SQL> flashback database to scn 547292;
flashback database to scn 547292
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
 
 
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
 
 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
 
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\USERS01.DBF
C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005
 
SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'c:\tb.dbf';
 
Database altered.
 
SQL> flashback database to scn 547292;
 
Flashback complete.
 
SQL> alter database open resetlogs;
 
Database altered.
 
SQL>
 
 
SQL> select * from tb;
 
no rows selected
 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
 
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\USERS01.DBF
C:\TB.DBF
 
SQL> select name from v$tablespace;
 
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TB
 
6 rows selected.
 
SQL>
 


A very interesting discussion make by Tom Kyte on comparison between Oracle and other database systems

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1886476148373

Hi all

I’m planning to take an “Exclusive Interview” from world-known Oracle Professional DBAs. So, I want you to send me your favourite Interviewees name and your favourite Interview questions you want to ask. Deadline is 20.07.2009

You can do it by adding a comment to this post or sending an e-mail to one of my email addresses written in my Profile

[youtube=http://www.youtube.com/watch?v=BWzosrI8NQ4&hl=en&fs=1&border=1]

alias rlsqlplus=’rlwrap sqlplus’

Here, you can find a good discussion and suggestion from Tom Kyte about “Storing files in an Oracle Database”.

Before reading this article, I was thinking to store OS files on another OS location. But Tom’s approach is different (as usual) and logical 🙂

I assume that this article will be useful for you, thus sharing it with you

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1011065100346196442

A good research made by Oracle Master – Laurent Schneider on “Committing Transaction”