Problematic erase key in Oracle datafile running on unix

In Unix like shell command line, to erase a letter before current cursor, ^H (CTRL+H) or ^? (BACKSPACE) you'd like to use?

1. Introduction

"stty -a" helps to see current keyboard mapping the BACKSPACE key.

dev07:B.11:TESTDB:/software/oracle> stty -a                            
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = DEL; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3
dev07:B.11:TESTDB:/software/oracle> stty erase (here pressed CTRL+h)


dev07:B.11:TESTDB:/software/oracle> stty -a
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = ^H; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3



dev07:B.11:TESTDB:/software/oracle> lss^?    #attemp to use Backspace to delete extra "s" , instead shows as ^?
ksh: lss^?:  not found
dev07:B.11:TESTDB:/software/oracle> lss (followd by pressed CTRL+h)
10g                adhoc              jre                oradata
References:

2. Create a problem file

dev07:B.11:TESTDB:/software/oracle> touch test1^?.txt   #pressed CTRL+h after "1"
dev07:B.11:TESTDB:/software/oracle> ls -l test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt  #^? is invisible to "ls -l" command

dev07:B.11:TESTDB:/software/oracle> ls -ll test1^?.txt    # press escape key after "1", can auto-complete the filename, but still invisible
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt

dev07:B.11:TESTDB:/software/oracle> ls -lb test1^?.txt    #use "b" option to see its octal number is 177, which is DEL in ASCII code table
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1\177.txt

--now play with ^H to create another problematic file

dev07:B.11:TESTDB:/software/oracle> stty -a
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = DEL; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3

dev07:B.11:TESTDB:/software/oracle> touch test.txt   #type test2^H.txt
dev07:B.11:TESTDB:/software/oracle> ls -lb test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1\177.txt  #177 in octal in ASCII is "DEL"
-rw-r--r--   1 oracle     dba              0 Jul 31 17:40 test2\010.txt   #010 in octal in ASCII is "backspace"
dev07:B.11:TESTDB:/software/oracle>
ksh: ^H^H^H^H^H^H^H^H:  not found
dev07:B.11:TESTDB:/software/oracle> ll test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:40 test.txt




3. Make it more troublesome in oracle datafile now.

3.1 first set stty erase as ^H
SQL>  create tablespace testbs datafile '/oracle/UAT/sapdata1/system_1/test1.dbf' size 20m;

Tablespace created.


SQL> alter tablespace testbs  add datafile '/oracle/UAT/sapdata1/system_1/test1^?2.dbf' size 20m;

Tablespace altered.
--  type test1+(Backspace key)+2.dbf in order to create the problem.
SQL> select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/oracle/UAT/sapdata1/system_1/test1.dbf
        74 TESTBS                           20971520       2560 AVAILABLE
          74 NO           0          0            0   20905984        2552
ONLINE

/oracle/UAT/sapdata1/system_1/test1^?2.dbf
        75 TESTBS                           20971520       2560 AVAILABLE
          75 NO           0          0            0   20905984        2552
ONLINE
At OS, it shows :
m1sapuat:orauat 9> ls -lb
total 81952
-rw-r-----   1 orauat     dba        20979712 Jul 30 16:45 test1.dbf
-rw-r-----   1 orauat     dba        20979712 Jul 30 18:42 test1\1772.dbf

3.2 Next, we need to rename it correctly to avoid other impact may arise.

# for database running in ARCHIVELOG mode
alter database datafile 75 offline

 SQL> alter database datafile 75 offline;   
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


  # this testing database runs in NOARCHIVELOG mode.

SQL> alter database datafile 75 offline drop;

Database altered.
-- type the exactly datafile name : test1+(Backspace key)+2.dbf in order
SQL>  alter database rename file  '/oracle/UAT/sapdata1/system_1/test1^?2.dbf' to '/oracle/UAT/sapdata1/system_1/test2.dbf';

Database altered.

-- at OS level, back up the datafile and do the same way to rename it to test2.dbf

-- now recover and online it back

SQL> recover datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
Media recovery complete.
SQL>  alter database datafile  '/oracle/UAT/sapdata1/system_1/test2.dbf' online;

Database altered.

SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/oracle/UAT/sapdata1/system_1/test1.dbf
        74 TESTBS                           20971520       2560 AVAILABLE
          74 NO           0          0            0   20905984        2552
ONLINE

/oracle/UAT/sapdata1/system_1/test2.dbf
        75 TESTBS                           20971520       2560 AVAILABLE
          75 NO           0          0            0   20905984        2552
ONLINE


SQL> select * from v$recover_file;

no rows selected

-- Note that this RENAME approach is preferred than drop the datafile , as drop datafile requires it is a empty file.

References:

Setting terminal characteristics with stty

Unix Tip: Using stty to Your Advantage

ASCII Table and Description

How to Drop a Datafile from a Tablespace

 

 

drop a non-empty datafile

SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME                                                                      
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         
---------- ------------------------------ ---------- ---------- ---------      
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     
------------ --- ---------- ---------- ------------ ---------- -----------     
ONLINE_                                                                        
-------                                                                        
/oracle/UAT/sapdata1/system_1/test1.dbf                                        
        74 TESTBS                           20971520       2560 AVAILABLE      
          74 NO           0          0            0   20905984        2552     
ONLINE                                                                         
                                                                               
/oracle/UAT/sapdata1/system_1/test2.dbf                                        
        75 TESTBS                           20971520       2560 AVAILABLE      
          75 NO           0          0            0   20905984        2552     
ONLINE                                                                         
                                                                               
-- This is an empty tablespace, now create a table in it.
SQL> create user user1 identified by user1 default tablespace testbs;

User created.

SQL> grant dba to user1;

Grant succeeded.

SQL> conn user1/user1
Connected.
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> desc dba_extents;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

SQL> select distinct file_id from user_extents;
select distinct file_id from user_extents
                *
ERROR at line 1:
ORA-00904: "FILE_ID": invalid identifier


SQL> desc user_extents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
-- Interesting no FILE_ID in user_extents.

SQL> select file_id,count(*)  from dba_extents where owner='USER1' group by file_id;

   FILE_ID   COUNT(*)                                                          
---------- ----------                                                          
        74         24                                                          
        75          9                                                          

--attempt to drop the 2nd datafile

SQL> alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty


SQL> drop table t1;

Table dropped.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
recyclebin                           string      off                           

-- repeat the test with recyclebin is on

SQL> alter system set recyclebin=on;

System altered.

SQL>  create table t1 as select * from dba_objects;

Table created.

SQL>
SQL> select file_id,count(*)  from dba_extents where owner='USER1' group by file_id;

   FILE_ID   COUNT(*)                                                          
---------- ----------                                                          
        74         24                                                          
        75          9                                                          

SQL> drop table t1;

Table dropped.

SQL> select file_id,count(*)  from dba_extents where owner='USER1' group by file_id;

no rows selected

SQL> alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty


SQL> purge recyclebin;

Recyclebin purged.

SQL>  alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';

Tablespace altered.

SQL> select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME                                                                      
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         
---------- ------------------------------ ---------- ---------- ---------      
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     
------------ --- ---------- ---------- ------------ ---------- -----------     
ONLINE_                                                                        
-------                                                                        
/oracle/UAT/sapdata1/system_1/test1.dbf                                        
        74 TESTBS                           20971520       2560 AVAILABLE      
          74 NO           0          0            0   20905984        2552     
ONLINE                                                                         
                                                                               

-- continue to test what happens to DEFAULT_TABLESPACE in DBA_USERS when the tablespace is dropped.



SQL> prompt to see default tablespace change after the tablespace is dropped
to see default tablespace change after the tablespace is dropped
SQL> conn / as sysdba
Connected.
SQL> select default_tablespace from dba_users where username='USER1';

DEFAULT_TABLESPACE                                                             
------------------------------                                                 
TESTBS                                                                         

SQL> drop tablespace testbs including contents and datafiles;

Tablespace dropped.

SQL> select default_tablespace from dba_users where username='USER1';

DEFAULT_TABLESPACE                                                             
------------------------------                                                 
TESTBS                                                                         

SQL> conn user1/user1
Connected.
SQL>  create table t1 as select * from dba_users;
 create table t1 as select * from dba_users
                                  *
ERROR at line 1:
ORA-00959: tablespace 'TESTBS' does not exist



SQL>  alter user user1 default tablespace system;

User altered.

SQL> create table t1 as select * from dba_users;

Table created.

SQL> show user;
USER is "USER1"
SQL> conn  / as sysdba
Connected.
SQL> drop user user1 cascade;

User dropped.

SQL> spool off

Oracle offline drop datafile

ALTER DATABASE DATAFILE  OFFLINE DROP

Below is my understanding and testing.

This command will not drop the datafile (from both OS and database ) , it will simply take the datafile offline and Oracle will no longer attempt to access it. Offlining the datafile is typically used when your intention is to drop the tablespace. This will also give you the ability to recover the datafile at a later date, e.g recover a datafile (within short period, assuming no REDO log group being recycled yet) in noarchivelog mode.

 SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME                                                                     
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS        
---------- ------------------------------ ---------- ---------- ---------     
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS    
------------ --- ---------- ---------- ------------ ---------- -----------    
ONLINE_                                                                       
-------                                                                       
/oracle/UAT/sapdata1/system_1/test1.dbf                                       
        74 TESTBS                           20971520       2560 AVAILABLE     
          74 NO           0          0            0   20905984        2552    
ONLINE                                                                        
                                                                              
/oracle/UAT/sapdata1/system_1/test2.dbf                                       
        75 TESTBS                           20971520       2560 AVAILABLE     
          75 NO           0          0            0   20905984        2552    
ONLINE                                                                        
                                                                              
                                                 

SQL> alter database datafile 75 offline drop;

Database altered.

SQL> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-00376: file 75 cannot be read at this time
ORA-01110: data file 75: '/oracle/UAT/sapdata1/system_1/test2.dbf'


SQL> recover datafile 75;
Media recovery complete.

SQL>  alter database datafile 75 online;

Database altered.




 References:

"How to drop a datafile ?" 

 Oracle Drop Datafile

some links about ROWNUM

http://www.dbforums.com/oracle/988716-rownum-order.html

http://www.orafaq.com/wiki/ROWNUM

http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

one useful use I learned from Tom Kytes' article is pagination.  e.g the follow sql returns 2nd and 3rd rows.

 select *
  from ( select /*+ FIRST_ROWS(5) */
  a.*, ROWNUM rnum
      from ( select * from t order by b) a
      where ROWNUM <4 )
where rnum  >1 ;