Dec-13

19-Dec

In Oracle 12c parameters, we can alter parameter for a PDB and new column  ISPDB_MODIFIABLE available in V$PARAMETER to list PDB modifiable parameters


SQL> select name from v$parameter where ispdb_modifiable ='TRUE';

There are 171 parameters  out of 367 are modifiable from a PDB.

You can’t create log file group  while connected to a PDB, you must be in CDB.


alter database

add logfile group 4 ('/n01/oradata1/12cDB/cdb/redo04b.log','/n01/oradata1/12cDB/cdb/redo04a.log') size 100m;

SQL> alter database

add logfile group 4 ('/n01/oradata1/12cDB/cdb/redo04b.log','/n01/oradata1/12cDB/cdb/redo04a.log') size 100m;  2

alter database

*

ERROR at line 1:

ORA-65040: operation not allowed from within a pluggable database

SQL> show user

USER is "SYS"

SQL> show con_name

CON_NAME

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

SALESDB

SQL> conn / as sysdba

Connected.

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> alter database

add logfile group 4 ('/n01/oradata1/12cDB/cdb/redo04b.log','/n01/oradata1/12cDB/cdb/redo04a.log') size 100m;  2

Database altered.

In V$PARAMETER it is indicated that, LOG_ARCHIVE_DEST_1 parameter is modifiable from a PDB.


SQL>  select name from v$parameter where ISPDB_MODIFIABLE='TRUE' and name='log_archive_dest_1';

NAME

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

log_archive_dest_1

But, when I tried to change the value, I got an error – not modifiable from pluggable database and was able to do it from CDB.


SQL> alter system set log_archive_dest_1= 'location=/n01/oradata1/12cDB/arch1' container=current ;

alter system set log_archive_dest_1= 'location=/n01/oradata1/12cDB/arch1' container=current

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-65040: operation not allowed from within a pluggable database

 

 

SQL> conn / as sysdba

Connected.

SQL> alter system set log_archive_dest_1= 'location=/n01/oradata1/12cDB/arch1' scope=both;

System altered.

It is a bug – 17266585.

18-Dec

Some good notes on DB compression Vs Storage compression

http://flashdba.com/2013/12/17/storage-myths-storage-compression-has-no-downside/

 

To connect to a PDB using EAZY connect – use the format

Sqlplus username/password@hostname:port/service name

 

Usage of FUNCTIONS in SQLs – a 12c new feature


SQL> with

with

FUNCTION fixstring

(p_owner VARCHAR2,

p_object VARCHAR2)

RETURN VARCHAR2 is

l_owner_object VARCHAR2(100);

BEGIN

l_owner_object := 'The object '||p_object||' belongs to '||p_owner;

RETURN l_owner_object;

END;

SELECT fixstring(owner,object_name) from test where rownum < 10;

/

FIXSTRING(OWNER,OBJECT_NAME)

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

The object DBMS_STANDARD belongs to ABBB

The object V$MAP_FILE belongs to ABBB

The object V$DLM_TRAFFIC_CONTROLLER belongs to ABBB

The object V$TRANSACTION belongs to ABBB

The object V$ENABLEDPRIVS belongs to ABBB

The object V$RECOVER_FILE belongs to ABBB

The object V$REDO_DEST_RESP_HISTOGRAM belongs to ABBB

The object V$RECOVERY_FILE_STATUS belongs to ABBB

The object V$RSRC_CONSUMER_GROUP belongs to ABBB

9 rows selected.

http://blog.mclaughlinsoftware.com/2013/12/17/with-clause-functions/

 

 

12-Dec

Adaptive plans – the dynamic plans at run time.    12c CBO is intelligent enough to change the execution plans at run time known as adaptive plans.

If IS_RESOLVED_ADAPTIVE_PLAN column in V$SQLAREA (or in V$SQL) is ‘Y’ – plan is fully resolved, ‘N’ is not fully resolved and NULL is not an adaptive plan.

You can see the adaptive plan using the DBMS_XPLAN.DISPLAY_CUROSR


SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(format=>’+adaptive’));

Or

SLQ> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(format=>’ +adaptive  +note’));

You can disable adaptive plans by setting the parameter


SQL> Alter session set optimizer_adaptive_features=FALSE;

https://blogs.oracle.com/optimizer/resource/CON8622_Oracle_Optimizer_What_to_expect_in_12c_part1.pdf

http://www.antognini.ch/2013/12/adaptive-plans-in-active-session-history/

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: