ORA-13505

I tested this on an Oracle 10 database running on Linux. If you try to make the SYSAUX tablespace READ ONLY, you get an error. There is no way round this, you cannot do it: 

SQL> alter tablespace sysaux read only

  2  /
alter tablespace sysaux read only
*
ERROR at line 1:
ORA-13505: SYSAUX tablespace can not be made read only

SQL>

ORA-02000 and ORA-02379

This was tested on Oracle 10 on Linux. You need to include a LIMIT clause when you create a profile. If you don't, you get an ORA-02000: 
 
SQL> create profile andrews_profile
  2  /
create profile andrews_profile
                             *
ERROR at line 1:
ORA-02000: missing LIMIT keyword

SQL>

You can specify a LIMIT as follows:

SQL> create profile andrews_profile
  2  limit password_life_time 60
  3  /

Profile created.

SQL>

In the example above, the LIMIT is on the number of days you are allowed to use the same password. There are various other LIMITs you can set. I will be looking at them in other posts.

You cannot have more than one profile with a given name. If you try to do so, you get an ORA-02379:

SQL> create profile andrews_profile
  2  limit failed_login_attempts 5
  3  /
create profile andrews_profile
*
ERROR at line 1:
ORA-02379: profile ANDREWS_PROFILE already exists

SQL>

DBMS_FILE_TRANSFER

I read about this command, which allows you to copy OS files in SQL*Plus, and decided to try it out on Oracle 11.2. First I created a file:

 

Solaris > pwd

/export/home/oracle/andrew/dir1

Solaris > echo "Andrew was here" > file1

Solaris >

 

Then I tried to use the command but it failed:

 

SQL> create or replace directory source_dir as

  2  '/export/home/oracle/andrew/dir1'

  3  /

 

Directory created.

 

SQL> create or replace directory target_dir as

  2  '/export/home/oracle/andrew/dir2'

  3  /

 

Directory created.

 

SQL> begin

  2  dbms_file_transfer.copy_file(

  3  source_directory_object=>'source_dir',

  4  source_file_name=>'file1',

  5  destination_directory_object=>'target_dir',

  6  destination_file_name=>'file2');

  7  end;

  8  /

begin

*

ERROR at line 1:

ORA-19505: failed to identify file

"/export/home/oracle/andrew/dir1/file1"

ORA-27046: file size is not a multiple of logical

block size

Additional information: 1

ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84

ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193

ORA-06512: at line 2

 

SQL>

 

I found some Oracle 10.2 documentation for the command and it said:

The size of the copied file must be a multiple of 512 bytes. I checked the size of my test file but it was only 16 bytes:

 

Solaris > pwd

/export/home/oracle/andrew/dir1

Solaris > ls -l

total 2

-rw-r--r--   1 oracle   dba           16 Aug 13 12:17 file1

Solaris >

 

I recreated the file with a size of 512 bytes:

 

Solaris > pwd

/export/home/oracle/andrew/dir1

Solaris > dd if=/dev/zero of=file1 bs=1 count=512

512+0 records in

512+0 records out

Solaris > ls -l

total 2

-rw-r--r--   1 oracle   dba          512 Aug 13 12:28 file1

Solaris >

 

When I tried the command again, it worked:

 

SQL> begin

  2  dbms_file_transfer.copy_file(

  3  source_directory_object=>'source_dir',

  4  source_file_name=>'file1',

  5  destination_directory_object=>'target_dir',

  6  destination_file_name=>'file2');

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL>

 

... and UNIX could see no difference between the input and output files:

 

Solaris > pwd

/export/home/oracle/andrew/dir1

Solaris > diff file1 \

> /export/home/oracle/andrew/dir2/file2

Solaris >

Reply to: Reply to Andrew Reid

Send

ORA-01643 etc

I have used ALTER TABLESPACE to change the status of a tablespace in several other posts. However, in some situations, you cannot change a tablespace's status. You can see what I mean in the examples below, which I tried out in an Oracle 10 database running on Linux.

You cannot make the SYSTEM, UNDO or TEMPORARY tablespaces READ ONLY:

SQL> alter tablespace system read only

  2  /
alter tablespace system read only
*
ERROR at line 1:
ORA-01643: system tablespace can not be made read only

SQL> alter tablespace undotbs1 read only
  2  /
alter tablespace undotbs1 read only
*
ERROR at line 1:
ORA-30021: Operation not allowed on undo tablespace

SQL> alter tablespace temp read only
  2  /
alter tablespace temp read only
*
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY
TABLESPACE

You cannot make a tablespace READ WRITE if is already ONLINE (READ WRITE and ONLINE have the same meaning):

SQL> select tablespace_name, status
  2  from dba_tablespaces
  3  /

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE

SQL> alter tablespace users read write
  2  /
alter tablespace users read write
*
ERROR at line 1:
ORA-01646: tablespace 'USERS' is not read only -
cannot make read write

SQL> alter tablespace users read only
  2  /

Tablespace altered.

SQL>

... and once a tablespace is READ ONLY, you cannot make it READ ONLY again!

SQL> alter tablespace users read only
  2  /
alter tablespace users read only
*
ERROR at line 1:
ORA-01644: tablespace 'USERS' is already read only

SQL>

ORA-00376

This was tested on an Oracle 10 database running on Linux. If you get an ORA-00376, your DBA may have taken a tablespace offline. If that is the case, the problem should go away once the tablespace is back on line again:

SQL> create table andrew
  2  tablespace users
  3  as select * from dba_tables
  4  /

Table created.

SQL> alter tablespace users offline
  2  /

Tablespace altered.

SQL> select count(*) from andrew
  2  /
select count(*) from andrew
                     *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '/home/oracle/DB1/users01.dbf'

SQL> alter tablespace users online
  2  /

Tablespace altered.

SQL> select count(*) from andrew
  2  /

  COUNT(*)
----------
      1521

SQL>

P.S. I forgot to add that you can check the status of a tablespace (and change its status if you wish) as shown below:

SQL> select status from dba_tablespaces

  2  where tablespace_name = 'USER_DATA';

STATUS

---------

ONLINE

SQL> alter tablespace user_data offline;

Tablespace altered.

SQL> select status from dba_tablespaces

  2  where tablespace_name = 'USER_DATA';

STATUS

---------

OFFLINE

SQL> alter tablespace user_data online;

Tablespace altered.

SQL> select status from dba_tablespaces

  2  where tablespace_name = 'USER_DATA';

STATUS

---------

ONLINE

SQL>

SQL*Plus ASCII and CHR Functions

This example was run on Oracle 10 on Linux. The chr function takes a number and displays (if it can) the character whose binary equivalent corresponds to that number. If you supply a negative number you get an error:

SQL> select chr(-1) from dual
  2  /
select chr(-1) from dual
       *
ERROR at line 1:
ORA-01426: numeric overflow

SQL>

chr(0) is not null, at least not in an Oracle database:

SQL> select 'CHR(0) is null' from dual
  2  where chr(0) is null
  3  /

no rows selected

SQL> select 'CHR(0) is not null' from dual
  2  where chr(0) is not null
  3  /

'CHR(0)ISNOTNULL'
------------------
CHR(0) is not null

SQL>

chr(9) is the tab delimiter:

SQL> select 'X'||'Y'||'Z' from dual
  2  /

'X'
---
XYZ

SQL> select 'X'||chr(9)||'Y'||chr(9)||'Z' from dual
  2  /

'X'||
-----
X    Y    Z

SQL>

The character with a binary value of 65 is the letter A:

SQL> select chr(65) from dual
  2  /

C
-
A

SQL>

The ascii function takes a character and displays its ASCII representation.
So, if we check the ASCII value of A, it should be 65:

SQL> select ascii('A') from dual
  2  /

ASCII('A')
----------
        65

SQL>

The PL/SQL below sets a variable i to each of the numbers from 0 through 255. It passes each value of i as a parameter to chr and tries to display the result. Then it checks that ascii(chr(i)) = i. Note the use of chr(10) to provide a line feed at the end of the 2nd dbms_output.put_line statement. Also note how chr(10) creates an extra line feed in the output:

SQL> set serveroutput on
SQL> begin
  2  for i in 0..255 loop
  3  dbms_output.put_line('chr('||i||') = '||chr(i));
  4  dbms_output.put_line('ascii(chr('||i||')) = '||ascii(chr(i))||chr(10));
  5  end loop;
  6  end;
  7  /
chr(0) =
ascii(chr(0)) = 0

chr(1) =
ascii(chr(1)) = 1

chr(2) =
ascii(chr(2)) = 2

chr(3) =
ascii(chr(3)) = 3

chr(4) =
ascii(chr(4)) = 4

chr(5) =
ascii(chr(5)) = 5

chr(6) =
ascii(chr(6)) = 6

chr(7) = 
ascii(chr(7)) = 7

chr(8) =
ascii(chr(8)) = 8

chr(9) =    
ascii(chr(9)) = 9

chr(10) =

ascii(chr(10)) = 10

chr(11) =

ascii(chr(11)) = 11

chr(12) =
ascii(chr(12)) = 12

chr(13) =
ascii(chr(13)) = 13

chr(14) =
ascii(chr(14)) = 14

chr(15) =
ascii(chr(15)) = 15

chr(16) =
ascii(chr(16)) = 16

chr(17) =
ascii(chr(17)) = 17

chr(18) =
ascii(chr(18)) = 18

chr(19) =
ascii(chr(19)) = 19

chr(20) =
ascii(chr(20)) = 20

chr(21) =
ascii(chr(21)) = 21

chr(22) =
ascii(chr(22)) = 22

chr(23) =
ascii(chr(23)) = 23

chr(24) =
ascii(chr(24)) = 24

chr(25) =
ascii(chr(25)) = 25

chr(26) =
ascii(chr(26)) = 26

chr(27) =
ascii(chr(27)) = 27

chr(28) =
ascii(chr(28)) = 28

chr(29) =
ascii(chr(29)) = 29

chr(30) =
ascii(chr(30)) = 30

chr(31) =
ascii(chr(31)) = 31

chr(32) =
ascii(chr(32)) = 32

chr(33) = !
ascii(chr(33)) = 33

chr(34) = "
ascii(chr(34)) = 34

chr(35) = #
ascii(chr(35)) = 35

chr(36) = $
ascii(chr(36)) = 36

chr(37) = %
ascii(chr(37)) = 37

chr(38) = &
ascii(chr(38)) = 38

chr(39) = '
ascii(chr(39)) = 39

chr(40) = (
ascii(chr(40)) = 40

chr(41) = )
ascii(chr(41)) = 41

chr(42) = *
ascii(chr(42)) = 42

chr(43) = +
ascii(chr(43)) = 43

chr(44) = ,
ascii(chr(44)) = 44

chr(45) = -
ascii(chr(45)) = 45

chr(46) = .
ascii(chr(46)) = 46

chr(47) = /
ascii(chr(47)) = 47

chr(48) = 0
ascii(chr(48)) = 48

chr(49) = 1
ascii(chr(49)) = 49

chr(50) = 2
ascii(chr(50)) = 50

chr(51) = 3
ascii(chr(51)) = 51

chr(52) = 4
ascii(chr(52)) = 52

chr(53) = 5
ascii(chr(53)) = 53

chr(54) = 6
ascii(chr(54)) = 54

chr(55) = 7
ascii(chr(55)) = 55

chr(56) = 8
ascii(chr(56)) = 56

chr(57) = 9
ascii(chr(57)) = 57

chr(58) = :
ascii(chr(58)) = 58

chr(59) = ;
ascii(chr(59)) = 59

chr(60) = <
ascii(chr(60)) = 60

chr(61) = =
ascii(chr(61)) = 61

chr(62) = >
ascii(chr(62)) = 62

chr(63) = ?
ascii(chr(63)) = 63

chr(64) = @
ascii(chr(64)) = 64

chr(65) = A
ascii(chr(65)) = 65

chr(66) = B
ascii(chr(66)) = 66

chr(67) = C
ascii(chr(67)) = 67

chr(68) = D
ascii(chr(68)) = 68

chr(69) = E
ascii(chr(69)) = 69

chr(70) = F
ascii(chr(70)) = 70

chr(71) = G
ascii(chr(71)) = 71

chr(72) = H
ascii(chr(72)) = 72

chr(73) = I
ascii(chr(73)) = 73

chr(74) = J
ascii(chr(74)) = 74

chr(75) = K
ascii(chr(75)) = 75

chr(76) = L
ascii(chr(76)) = 76

chr(77) = M
ascii(chr(77)) = 77

chr(78) = N
ascii(chr(78)) = 78

chr(79) = O
ascii(chr(79)) = 79

chr(80) = P
ascii(chr(80)) = 80

chr(81) = Q
ascii(chr(81)) = 81

chr(82) = R
ascii(chr(82)) = 82

chr(83) = S
ascii(chr(83)) = 83

chr(84) = T
ascii(chr(84)) = 84

chr(85) = U
ascii(chr(85)) = 85

chr(86) = V
ascii(chr(86)) = 86

chr(87) = W
ascii(chr(87)) = 87

chr(88) = X
ascii(chr(88)) = 88

chr(89) = Y
ascii(chr(89)) = 89

chr(90) = Z
ascii(chr(90)) = 90

chr(91) = [
ascii(chr(91)) = 91

chr(92) = \
ascii(chr(92)) = 92

chr(93) = ]
ascii(chr(93)) = 93

chr(94) = ^
ascii(chr(94)) = 94

chr(95) = _
ascii(chr(95)) = 95

chr(96) = `
ascii(chr(96)) = 96

chr(97) = a
ascii(chr(97)) = 97

chr(98) = b
ascii(chr(98)) = 98

chr(99) = c
ascii(chr(99)) = 99

chr(100) = d
ascii(chr(100)) = 100

chr(101) = e
ascii(chr(101)) = 101

chr(102) = f
ascii(chr(102)) = 102

chr(103) = g
ascii(chr(103)) = 103

chr(104) = h
ascii(chr(104)) = 104

chr(105) = i
ascii(chr(105)) = 105

chr(106) = j
ascii(chr(106)) = 106

chr(107) = k
ascii(chr(107)) = 107

chr(108) = l
ascii(chr(108)) = 108

chr(109) = m
ascii(chr(109)) = 109

chr(110) = n
ascii(chr(110)) = 110

chr(111) = o
ascii(chr(111)) = 111

chr(112) = p
ascii(chr(112)) = 112

chr(113) = q
ascii(chr(113)) = 113

chr(114) = r
ascii(chr(114)) = 114

chr(115) = s
ascii(chr(115)) = 115

chr(116) = t
ascii(chr(116)) = 116

chr(117) = u
ascii(chr(117)) = 117

chr(118) = v
ascii(chr(118)) = 118

chr(119) = w
ascii(chr(119)) = 119

chr(120) = x
ascii(chr(120)) = 120

chr(121) = y
ascii(chr(121)) = 121

chr(122) = z
ascii(chr(122)) = 122

chr(123) = {
ascii(chr(123)) = 123

chr(124) = |
ascii(chr(124)) = 124

chr(125) = }
ascii(chr(125)) = 125

chr(126) = ~
ascii(chr(126)) = 126

chr(127) = 
ascii(chr(127)) = 127

chr(128) = ?
ascii(chr(128)) = 128

chr(129) = ?
ascii(chr(129)) = 129

chr(130) = ?
ascii(chr(130)) = 130

chr(131) = ?
ascii(chr(131)) = 131

chr(132) = ?
ascii(chr(132)) = 132

chr(133) = ?
ascii(chr(133)) = 133

chr(134) = ?
ascii(chr(134)) = 134

chr(135) = ?
ascii(chr(135)) = 135

chr(136) = ?
ascii(chr(136)) = 136

chr(137) = ?
ascii(chr(137)) = 137

chr(138) = ?
ascii(chr(138)) = 138

chr(139) = ?
ascii(chr(139)) = 139

chr(140) = ?
ascii(chr(140)) = 140

chr(141) = ?
ascii(chr(141)) = 141

chr(142) = ?
ascii(chr(142)) = 142

chr(143) = ?
ascii(chr(143)) = 143

chr(144) = ?
ascii(chr(144)) = 144

chr(145) = ?
ascii(chr(145)) = 145

chr(146) = ?
ascii(chr(146)) = 146

chr(147) = ?
ascii(chr(147)) = 147

chr(148) = ?
ascii(chr(148)) = 148

chr(149) = ?
ascii(chr(149)) = 149

chr(150) = ?
ascii(chr(150)) = 150

chr(151) = ?
ascii(chr(151)) = 151

chr(152) = ?
ascii(chr(152)) = 152

chr(153) = ?
ascii(chr(153)) = 153

chr(154) = ?
ascii(chr(154)) = 154

chr(155) = ?
ascii(chr(155)) = 155

chr(156) = ?
ascii(chr(156)) = 156

chr(157) = ?
ascii(chr(157)) = 157

chr(158) = ?
ascii(chr(158)) = 158

chr(159) = ?
ascii(chr(159)) = 159

chr(160) =
ascii(chr(160)) = 160

chr(161) = !
ascii(chr(161)) = 161

chr(162) = ?
ascii(chr(162)) = 162

chr(163) = #
ascii(chr(163)) = 163

chr(164) = ?
ascii(chr(164)) = 164

chr(165) = Y
ascii(chr(165)) = 165

chr(166) = |
ascii(chr(166)) = 166

chr(167) = ?
ascii(chr(167)) = 167

chr(168) = ?
ascii(chr(168)) = 168

chr(169) = ?
ascii(chr(169)) = 169

chr(170) = ?
ascii(chr(170)) = 170

chr(171) = <
ascii(chr(171)) = 171

chr(172) = ?
ascii(chr(172)) = 172

chr(173) = -
ascii(chr(173)) = 173

chr(174) = ?
ascii(chr(174)) = 174

chr(175) = ?
ascii(chr(175)) = 175

chr(176) = ?
ascii(chr(176)) = 176

chr(177) = ?
ascii(chr(177)) = 177

chr(178) = ?
ascii(chr(178)) = 178

chr(179) = ?
ascii(chr(179)) = 179

chr(180) = '
ascii(chr(180)) = 180

chr(181) = ?
ascii(chr(181)) = 181

chr(182) = ?
ascii(chr(182)) = 182

chr(183) = ?
ascii(chr(183)) = 183

chr(184) = ?
ascii(chr(184)) = 184

chr(185) = ?
ascii(chr(185)) = 185

chr(186) = ?
ascii(chr(186)) = 186

chr(187) = >
ascii(chr(187)) = 187

chr(188) = ?
ascii(chr(188)) = 188

chr(189) = ?
ascii(chr(189)) = 189

chr(190) = ?
ascii(chr(190)) = 190

chr(191) = ?
ascii(chr(191)) = 191

chr(192) = A
ascii(chr(192)) = 192

chr(193) = A
ascii(chr(193)) = 193

chr(194) = A
ascii(chr(194)) = 194

chr(195) = ?
ascii(chr(195)) = 195

chr(196) = A
ascii(chr(196)) = 196

chr(197) = ?
ascii(chr(197)) = 197

chr(198) = ?
ascii(chr(198)) = 198

chr(199) = C
ascii(chr(199)) = 199

chr(200) = E
ascii(chr(200)) = 200

chr(201) = E
ascii(chr(201)) = 201

chr(202) = E
ascii(chr(202)) = 202

chr(203) = E
ascii(chr(203)) = 203

chr(204) = I
ascii(chr(204)) = 204

chr(205) = I
ascii(chr(205)) = 205

chr(206) = I
ascii(chr(206)) = 206

chr(207) = I
ascii(chr(207)) = 207

chr(208) = ?
ascii(chr(208)) = 208

chr(209) = ?
ascii(chr(209)) = 209

chr(210) = O
ascii(chr(210)) = 210

chr(211) = O
ascii(chr(211)) = 211

chr(212) = O
ascii(chr(212)) = 212

chr(213) = ?
ascii(chr(213)) = 213

chr(214) = O
ascii(chr(214)) = 214

chr(215) = ?
ascii(chr(215)) = 215

chr(216) = ?
ascii(chr(216)) = 216

chr(217) = U
ascii(chr(217)) = 217

chr(218) = U
ascii(chr(218)) = 218

chr(219) = U
ascii(chr(219)) = 219

chr(220) = U
ascii(chr(220)) = 220

chr(221) = Y
ascii(chr(221)) = 221

chr(222) = ?
ascii(chr(222)) = 222

chr(223) = ?
ascii(chr(223)) = 223

chr(224) = a
ascii(chr(224)) = 224

chr(225) = a
ascii(chr(225)) = 225

chr(226) = a
ascii(chr(226)) = 226

chr(227) = ?
ascii(chr(227)) = 227

chr(228) = a
ascii(chr(228)) = 228

chr(229) = ?
ascii(chr(229)) = 229

chr(230) = ?
ascii(chr(230)) = 230

chr(231) = c
ascii(chr(231)) = 231

chr(232) = e
ascii(chr(232)) = 232

chr(233) = e
ascii(chr(233)) = 233

chr(234) = e
ascii(chr(234)) = 234

chr(235) = e
ascii(chr(235)) = 235

chr(236) = i
ascii(chr(236)) = 236

chr(237) = i
ascii(chr(237)) = 237

chr(238) = i
ascii(chr(238)) = 238

chr(239) = i
ascii(chr(239)) = 239

chr(240) = ?
ascii(chr(240)) = 240

chr(241) = ?
ascii(chr(241)) = 241

chr(242) = o
ascii(chr(242)) = 242

chr(243) = o
ascii(chr(243)) = 243

chr(244) = o
ascii(chr(244)) = 244

chr(245) = ?
ascii(chr(245)) = 245

chr(246) = o
ascii(chr(246)) = 246

chr(247) = ?
ascii(chr(247)) = 247

chr(248) = ?
ascii(chr(248)) = 248

chr(249) = u
ascii(chr(249)) = 249

chr(250) = u
ascii(chr(250)) = 250

chr(251) = u
ascii(chr(251)) = 251

chr(252) = u
ascii(chr(252)) = 252

chr(253) = y
ascii(chr(253)) = 253

chr(254) = ?
ascii(chr(254)) = 254

chr(255) = y
ascii(chr(255)) = 255


PL/SQL procedure successfully completed.

SQL>