Oracle uses set to modify Database Operation Parameters

1. How to check whether a parameter is dynamic or static

We can find the parameter in v $ parameter.

Column value format a40;

Column name format a20;
Select name, VALUE, ISSES_MODIFIABLE, ISSYS _ modifiable from v $ SYSTEM_PARAMETER;

Here, the isses_modifiable and issys_modifiable columns correspond to the parameters modified at the SESSION level and the parameters modified at the SYSTEM level respectively.
If isses_modifiable = true, this parameter can be modified at the SESSION level and takes effect immediately. If it is false, it cannot be modified.
Alter session set SQL _trace = true;
If issys_modifiable = immediate, this parameter can be modified immediately in SYSTEM and takes effect immediately.
Alter system set SQL _trace = true;
If issys_modifiable = deferred, this parameter cannot be directly modified in the memory. You need to add scope = spfile to make it take effect after restart.
Alter system set asm_diskstring = 'oracle _ group' scope = spfile;
If issys_modifiable = false, the instance must be restarted to take effect ..

IMMEDIATE: dynamic parameter, effective immediately

DEFERRED

FALSE: static parameter. It takes effect only after the instance is restarted.

■ MMEDIATE-Parameter can be changed with alter system regardless of the type of parameter file used to start the instance. The change takes effect immediately.

■ DEFERRED-Parameter can be changed with alter system regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.

■ FALSE-Parameter cannot be changed with alter system unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.

2. Differences between alter database, alter system, and alter session;

Alter database: DATABASE-level

Alter system: instance-level

Alter session: SESSION level

Alter database data files, tablespaces, log files, and so on. Some changes related to physical files, that is, changes to the DATABASE, are generally physically changed, when you use alter database, the controlfile header and other physical file header information changes can be seen.

For example, ALTER dtabase rename datafile

Alter system dynamically changes the attributes of the data library routine, which is generally logically invisible.
For example, alter system set db_cache_size

Alter session changes the attributes of a SESSION that are logically invisible.
For example: alter session set nls_language

3. How to view the tablespace or table creation Structure

A. view the tablespace creation Structure

Sets long 2000

Select

Sys. dbms_metadata.get_ddl ('tablespace', 'tablespace _ name ')

From dual;

B. view the creation structure of the User table/Index

Select sys. dbms_metadata.get_ddl ('table', 'table _ name', 'username') from dual;

Select sys. dbms_metadata.get_ddl ('index', 'index _ name', 'username') from dual;

C. view the procedure structure created by the user

Select sys. dbms_metadata.get_ddl ('Procedure ', 'Procedure _ name') from dual;

1 2 Next Page