Andrew's Oracle Blog: Oracle 10

Showing posts with label Oracle 10. Show all posts

Monday, June 05, 2017

Password Expire

If a user forgets his password, he may ask you to reset it for him. You will then know his new password, which you may see as a security issue. By including the password expire clause in the alter user command, you can force the user to change his password the next time he logs in. After this, you will no longer know his password. The examples which follow show a DBA changing a password in red and a user logging in afterwards in green.

The first example shows a DBA using an Oracle 11 version of SQL*Plus to change a password in an Oracle 11 database:

TEST11 > sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:03:51 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user a identified by b

2 password expire

3 /

User altered.

SQL>

The user then logs in with the same Oracle 11 version of SQL*Plus and is prompted to change his password. After doing this, he reconnects to the database. This is not necessary, it is just to show that the password change has taken effect:

TEST11 > sqlplus a/b

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:11:51 2015

ERROR:

ORA-28001: the password has expired

Changing password for a

New password:

Retype new password:

Password changed

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn a/c

Connected.

SQL>

The DBA then resets and expires the password again using the same Oracle 11 version of SQL*Plus:

TEST11 > sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Aug 26 11:56:10 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user a identified by b

2 password expire

3 /

User altered.

SQL>

The user logs in using an Oracle 10 version of SQL*Plus this time. He is prompted to change his password but is unable to do so:

TEST10 > sqlplus a/b@test11

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 26 11:59:46 2015

ERROR:

ORA-28001: the password has expired

Changing password for a

New password:

Retype new password:

ERROR:

ORA-01017: invalid username/password; logon denied

Password unchanged

Enter user-name:

So, if you want to expire a password in an Oracle 11 database, you need to check that the person who will be logging in to that user afterwards is using an Oracle 11 version of SQL*Plus, not an Oracle 10 one.

Friday, April 15, 2016

%TYPE Declaration Gives PLS-00302

A developer reported a problem with a %TYPE declaration which was returning PLS-00302 in an Oracle 10 database. The cause turned out to be a variation on a problem which I have already reported. However, as it took me some time to work out, I have reproduced it below. First I created a user, called USER1, who would own a table:

SQL> conn / as sysdba

Connected.

SQL> create user user1

2 identified by user1

3 default tablespace users

4 quota 10m on users

5 /

User created.

SQL> grant create session, create table

2 to user1

3 /

Grant succeeded.

SQL>

Then I created a user, called USER2, to declare a variable using %TYPE, basing it on a column in the table created by USER1:

SQL> create user user2 identified by user2

2 /

User created.

SQL> grant create session, create synonym

2 to user2

3 /

Grant succeeded.

SQL>

USER1 created a table called TAB1 and allowed USER2 to see it:

SQL> conn user1/user1

Connected.

SQL> create table tab1

2 (col1 number)

3 /

Table created.

SQL> grant select on tab1 to user2

2 /

Grant succeeded.

SQL>

USER2 declared a variable called BLAH using %TYPE to base it on column COL1 in table TAB1. This was successful:

SQL> conn user2/user2

Connected.

SQL> declare

2 blah user1.tab1.col1%type;

3 begin

4 null;

5 end;

6 /

PL/SQL procedure successfully completed.

SQL>

USER2 created a synonym called USER1. N.B. It is not a good idea for an object in one schema to have the same name as a schema elsewhere in the database.

SQL> create synonym user1 for user_tables

2 /

Synonym created.

SQL>

USER2 tried to declare a variable called BLAH as before. This time, Oracle probably thought that USER1 referred to the synonym created in the previous step rather than the username created at the start of the post. The declaration therefore failed:

SQL> declare

2 blah user1.tab1.col1%type;

3 begin

4 null;

5 end;

6 /

blah user1.tab1.col1%type;

ERROR at line 2:

ORA-06550: line 2, column 12:

PLS-00302: component 'TAB1' must be declared

ORA-06550: line 2, column 6:

PL/SQL: Item ignored

SQL>

Friday, October 09, 2015

ORA-01166

We have some jobs which copy the datafiles from 1 database to another then recreate the control file to give the output database a new name. Some of these jobs are intelligent i.e. they query the input database to dynamically create the SQL to do the rename. This particular job is not. Part of it is shown below and you can see that the MAXDATAFILES parameter was set to 120:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "MRMDPT1" RESETLOGS NOARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 10

MAXLOGMEMBERS 3

MAXDATAFILES 120

MAXINSTANCES 1

MAXLOGHISTORY 452

Somebody added an extra datafile to the input database so it then had 121:

SQL> select count(*) from dba_data_files

2 /

COUNT(*)

----------

121

SQL>

However, he forgot to update the script so the rename failed as follows:

ORA-01503: CREATE CONTROLFILE failed

ORA-01166: file number 121 is larger than MAXDATAFILES (120)

ORA-01110: data file 121: '/cisdpt/mrmdpt1/mrm_tables3/sysauxMRMPROD_2.dbf'

I updated the script like this, changing MAXDATAFILES to 150:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET DATABASE "MRMDPT1" RESETLOGS NOARCHIVELOG

-- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 10

MAXLOGMEMBERS 3

MAXDATAFILES 150

MAXINSTANCES 1

MAXLOGHISTORY 452

... and when I ran the job again, it worked:

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Oct 8 12:16:47 2015

Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area 524288000 bytes

Fixed Size 2031416 bytes

Variable Size 171966664 bytes

Database Buffers 343932928 bytes

Redo Buffers 6356992 bytes

SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144

Control file created.

SQL>

Tuesday, August 12, 2014

DBA_FEATURE_USAGE_STATISTICS

Oracle introduced this view in version 10. It looks like this in version 11:

SQL> desc dba_feature_usage_statistics

Name Null? Type

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

DBID NOT NULL NUMBER

NAME NOT NULL VARCHAR2(64)

VERSION NOT NULL VARCHAR2(17)

DETECTED_USAGES NOT NULL NUMBER

TOTAL_SAMPLES NOT NULL NUMBER

CURRENTLY_USED VARCHAR2(5)

FIRST_USAGE_DATE DATE

LAST_USAGE_DATE DATE

AUX_COUNT NUMBER

FEATURE_INFO CLOB

LAST_SAMPLE_DATE DATE

LAST_SAMPLE_PERIOD NUMBER

SAMPLE_INTERVAL NUMBER

DESCRIPTION VARCHAR2(128)

SQL>

As its name suggests, it allows you to see if a database uses a particular Oracle feature or not. In Oracle 11, it has over 150 entries:

SQL> l

1 select count(*)

2* from dba_feature_usage_statistics

SQL> /

COUNT(*)

----------

152

SQL>

Some of the features reported are shown below:

SQL> l

1* select name from dba_feature_usage_statistics

SQL> /

NAME

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

Encrypted Tablespaces

MTTR Advisor

Multiple Block Sizes

OLAP - Analytic Workspaces

OLAP - Cubes

Oracle Managed Files

Oracle Secure Backup

Parallel SQL DDL Execution

Parallel SQL DML Execution

Parallel SQL Query Execution

Partitioning (system)

Partitioning (user)

Oracle Text

PL/SQL Native Compilation

Real Application Clusters (RAC)

Recovery Area

Recovery Manager (RMAN)

RMAN - Disk Backup

RMAN - Tape Backup

Etc

I checked in a database where I did not think that SQL Loader had been used with the Direct Path option. The value in the DETECTED_USAGES column was zero:

SQL> l

1 select detected_usages

2 from dba_feature_usage_statistics

3 where name =

4* 'Oracle Utility SQL Loader (Direct Path Load)'

SQL> /

DETECTED_USAGES

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

SQL>

I checked in a database where I had used SQL Loader with the Direct Path option a few times. The value in the DETECTED_USAGES column seemed far too high:

SQL> l

1 select detected_usages

2 from dba_feature_usage_statistics

3 where name =

4* 'Oracle Utility SQL Loader (Direct Path Load)'

SQL> /

DETECTED_USAGES

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

231

SQL>

I will do some research and once I know how this figure is calculated, I will return to this post and update it.

Thursday, June 26, 2014

1 + NULL is NULL

If you try to add a null to a number, the result is a null. You can see what I mean in the example below, which I tested in Oracle 10:

SQL> select 1 from dual
2 /

1
----------
1

SQL> select nvl(null,'null') from dual
2 /

NVL(NULL,'NULL')
----------------
null

SQL> select nvl(to_char(1+null),'null') from dual
2 /

NVL(TO_CHAR(1+NULL),'NULL')
---------------------------
null

SQL>

Wednesday, May 28, 2014

GROUP BY and ORDER BY

In 1997, I was sent on a 1 day Introduction to Oracle course. The lecturer said that if you used a GROUP BY, you could not assume that the results would be returned in order. If you wanted to make sure, you should include an ORDER BY as well. From a theoretical point of view, he was correct as an RDBMS is not obliged to return results in any particular order unless you tell it to do so. But from a practical point of view, a simple way to group data is to sort it first. Up to and including version 9, Oracle seems to have used this method:

TEST9 > sqlplus /

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Sep 2 18:04:16 2011

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> @group_by

SQL> set pages 100

SQL> col first_letter format a12

SQL> select substr(segment_name,1,1) first_letter,

2 count(*)

3 from dba_segments

4 group by substr(segment_name,1,1)

5 /

FIRST_LETTER COUNT(*)

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

1 1

A 118

B 92

C 98

D 43

E 17

F 51

G 22

H 39

I 293

J 5

K 7

L 115

M 116

N 2

O 29

P 52

Q 49

R 170

S 319

T 121

U 54

V 16

W 11

X 14

_ 119

26 rows selected.

SQL>

This is no longer the case and, starting with Oracle 10, if you want to see the output of a GROUP BY in order, you have to include an ORDER BY as well:

TEST10 > sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Sep 2 18:15:01 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> @group_by

SQL> set pages 100

SQL> col first_letter format a12

SQL> select substr(segment_name,1,1) first_letter,

2 count(*)

3 from dba_segments

4 group by substr(segment_name,1,1)

5 /

FIRST_LETTER COUNT(*)

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

P 161

O 37

V 83

D 157

_ 68

M 448

N 98

W 779

Q 40

Y 3

C 135

I 550

B 120

F 55

U 20

S 717

T 250

A 316

J 21

E 143

L 178

R 303

H 662

K 9

b 1

G 7

X 23

27 rows selected.

SQL> select substr(segment_name,1,1) first_letter,

2 count(*)

3 from dba_segments

4 group by substr(segment_name,1,1)

5 order by 1

6 /

FIRST_LETTER COUNT(*)

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

A 316

B 120

C 135

D 157

E 143

F 55

G 7

H 662

I 550

J 21

K 9

L 178

M 448

N 98

O 37

P 161

Q 40

R 303

S 717

T 250

U 20

V 83

W 779

X 23

Y 3

_ 68

b 1

27 rows selected.

SQL>