CODEWITHSUNDEEP
oracle-databasealter
Stanley Mungai
Stanley Mungai

Reputation: 4150

Unable to alter Oracle Parameters

I am unable to add more than 200 datafiles in my database because of these parameters:

select records_total from v$controlfile_record_section where type = 'DATAFILE';
select value from v$parameter where name = 'db_files';

Both of these give me an output of 200. I need to increase this to 400 so I have tried:

alter system set records_total = 400 where name = 'db_files';
alter system set value= 400 where type = 'DATAFILE';

but I am getting S

QL Error: ORA-02065: illegal option for ALTER SYSTEM
02065. 00000 -  "illegal option for ALTER SYSTEM"
*Cause:    The option specified for ALTER SYSTEM is not supported
*Action:   refer to the user manual for option supported

Am I able to change these parameters and how?

Upvotes: 0

Views: 4126

Answers (1)

Jon Heller
Jon Heller

Reputation: 36817

You probably want to use commands like this:

C:\Users\jonearles>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 10 13:07:16 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200
SQL> alter system set db_files=400 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1048576000 bytes
Fixed Size                  3053584 bytes
Variable Size             662702064 bytes
Database Buffers          377487360 bytes
Redo Buffers                5332992 bytes
Database mounted.
Database opened.
SQL> show parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     400
SQL>

This assumes you are using an SPFILE (or else you will need to manually edit the init.ora file and restart) and you are not using RAC (or else you will need to use a command like srvctl stop database -d my_sid).

As ditto mentioned, it can help to look at the ALTER syntax. It may also help to look at the Oracle Database Reference, which will tell you if the command is dynamic (meaning it can be run without restarting the database).

Upvotes: 1

Related Questions