Changing or Deleting Oracle DB Parameter Using BRTOOLS

[former_member184628]

Hi All,

We wanted to change and delete certain parameter in Oracle DB parameter using SQL stmt.

SQL is :

ALTER SYSTEM set parameter = value SCOPE = spfile|both|memory;

And not sure if we have to use below command to delete few parameters:

SQL:

alter system reset parameter SCOPE = spfile|both|memory;

Correct me if I am wrong.

Also, we wanted to take system restart immediately after changing or reseting parameter, so shall we take scope as "spfile"?

Thanks in Advance,

Sharib

[kishore_soma]

Hi Tasneem,

The Best way to do it is like told by ashish.

- shut down db

- Take profile pfile backup

- create pfile from spfile;

- go to pfile and make changes.. Delete or update or change as required.

- create spfile from pfile

- startup db and check. it should be default values.

some parameters may still be in pfile even though its default.

To check the parameter whether it is default or not, execute below sql query.

select name, isdefault from v$parameter where name = <parameter name>;

if it isdefault is true, that means its default value.

Hope it helps.

Regards,

Kishore Soma

[Reagan]

Hello

The subject line says "Using BrTools" and in the content "Using SQL"

There is no delete option for alter system.

You may use SET and RESET options to set and remove parameters.

Check these links to understand how it works

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2014.htm#SQLRF00902

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2014.htm#SQLRF53152

If you want to use BRTools then follow this:

http://help.sap.com/saphelp_nw04/helpdata/en/f1/0d01b42a4aa84fbe2be415d23bf023/content.htm

Regards

RB

[former_member206552]

Hi Sharib,

for changing parameter you can use

ALTER SYSTEM set parameter = value SCOPE = spfile|both|memory;

memory - only active for you session (only if the parameter is dynamic)

both - for you session and restart (only if the parameter is dynamic)

spfile - restart is required

if you want to set the parameter to defualt value you can use the  reset option

•   By issuing an ALTER SYSTEM RESET ... SCOPE=SPFILE statement to remove a parameter from a server parameter file, causing the default value to take effect the next time you start an instance of the database.

source : http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams004.htm

you have to use it with the "scope=spfile" option, once this is done you have to restart the database

Hope this answer your question

Best Regards

Marius

[ashish_vikas]

ALTER SYSTEM set parameter = value SCOPE = spfile|both|memory; is correct.

You can use the ALTER SYSTEM RESET command to clear (remove) the setting of any initialization parameter in the SPFILE that was used to start the instance. Neither SCOPE=MEMORY nor SCOPE=BOTH are allowed. The SCOPE = SPFILE clause is not required, but can be included.

you can read :

http://docs.oracle.com/cd/E18283_01/server.112/e17120/create006.htm#i101004

Also, we wanted to take system restart immediately after changing or reseting parameter, so shall we take scope as "spfile"?

>> you can. no problem.

best regards

ashish

[former_member184628]

Hi Ashish,

Thanks for your reply.

But command

alter system reset parameter is only resetting the values to the default not deleting it.

Can anyone suggest how to remove/delete obsolete parameters from Oracle DB.

Thanks,

Sharib

[ashish_vikas]

SQL> create pfile from spfile;

Edit the pfile according to your needs

and after this:

sql> shutdown immediate

sql> create spfile from pfile;

sql> startup

Also, there will be many parameter which will take default value if you do not set it.