I’ve posted a new thread on OTN forum. Please share your opinions …
http://forums.oracle.com/forums/message.jspa?messageID=3661528
Archive for July, 2009
How to prevent DBAs from using dumps for passing Exams
Posted by Kamran Agayev A. on July 31, 2009
Posted in Administration | Leave a Comment »
User access to database within limits of time
Posted by Kamran Agayev A. on July 28, 2009
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>
Posted in Administration, DBA scripts | 8 Comments »
Recovering Dropped tablespace using Flashback Database
Posted by Kamran Agayev A. on July 27, 2009
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>
Posted in Administration | 13 Comments »
Comparison between Oracle and Others – Tom Kyte’s interesting discussion
Posted by Kamran Agayev A. on July 18, 2009
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
Posted in Administration | Leave a Comment »
Send your favorite “Exclusive” Interview Questions and Interviewees name
Posted by Kamran Agayev A. on July 13, 2009
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
Posted in Administration | 12 Comments »
Using rlwrap to get command line history in SQL*Plus – Video Tutorial
Posted by Kamran Agayev A. on July 4, 2009
By viewing .bashrc file, it wasn’t recorded the “alias line” which I’ve added
The line is as below:
The line is as below:
alias rlsqlplus=’rlwrap sqlplus’
Additionaly, you can download rlwrap software from below link:
Posted in Administration, Video Tutorials | 15 Comments »
Kamran Agayev's Oracle Blog 