Skip navigation.

DBA Blogs

OCP 12C – In Database Archiving and Temporal Validity

DBA Scripts and Articles - Wed, 2014-10-01 13:14

In Database Archiving In Database Archiving is a new feature of Oracle 12c meant to solve management of historical data inside the database. Like its name says, this functionnality leaves data into the database, so it remains accessible if you need it. With In Database Archiving, historical data remains in the database but is invisible to [...]

The post OCP 12C – In Database Archiving and Temporal Validity appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Information Lifecycle Management and Storage Enhancements

DBA Scripts and Articles - Wed, 2014-10-01 10:16

Information Lifecycle Management Before reading this flashcard I recommend you to read my article on ILM. Automatic Data Optimization (ADO) Policy based data management Let you define policies at : Tablespace level Segment level Row level Policies can either compress data or move data to a different tablespace Policies let you define when, what and [...]

The post OCP 12C – Information Lifecycle Management and Storage Enhancements appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Join me in a FREE live webcast about Real-Time Query!

The Oracle Instructor - Wed, 2014-10-01 08:50

On Thursday, 2nd October, 12:30 CET I will be doing a Live Webcast with many demonstrations about Data Guard Real-Time Query.

The shown features all work with 11g already.

Register here.

805-banner-dataguardrealtime-v1-2294812Addendum: The webcast was done already.


Tagged: Active Data Guard, Data Guard, OU Streams
Categories: DBA Blogs

OCP 12C – Backup, Recovery and Flashback for a CDB/PDB

DBA Scripts and Articles - Wed, 2014-10-01 07:43

Backup a CDB/PDB To make a database backup you need the SYSBACKUP or SYSDBA privilege. You can backup the CDB and all the PDBs independantly, all together, or by specifying a list. You can backup a PDB by connecting directly to it and use: RMAN> BACKUP DATABASE: You can backup a PDB by connecting to [...]

The post OCP 12C – Backup, Recovery and Flashback for a CDB/PDB appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Oracle #GoldenGate Parameter File Templates

DBASolved - Wed, 2014-10-01 01:27

This week I’ve been enjoying spending some time at Oracle Open World in San Francisco, CA.  While here, I’ve been talking with everyone, friends old and new, and it came to my attention that it would be a good idea to have some useful templates for Oracle GoldenGate parameter files.  With this in mind, I decided to create some generic templates with comments for Oracle GoldenGate processes.  These templates can be found on my Scripts page under “Oracle GoldenGate Parameter Templates”.  These files are in a small zip file that can be downloaded, unzipped and used in creating a basic uni-directional configuration.

By using these templates, you should be able to do:

  1. Review useful examples for each Oracle GoldenGate process (Manager, Extract, Pump, Replicat)
  2. With minor changes, quickly get uni-directional replication going
  3. Gain a base understanding of what how simple Oracle GoldenGate parameter files work

Enjoy!

about.me: http://about.me/dbasolved

 


Filed under: Golden Gate
Categories: DBA Blogs

2016 Mazda6 Interior Review

Ameed Taylor - Wed, 2014-10-01 01:15
If there's an ethical to the absolutely redesigned 2016 Mazda6 Interior Review  story, it's that there may be extra to lifestyles than "zoom-zoom." The previous-generation Mazda 6 had a whole lot of it, yet Mazda had a satan of a time getting the shopping for public to notice. which is as a result of American drivers usually do not care a lot about how their domestic sedans behave on winding united states of america roads. What they want is space, security, reliability, energy and magnificence -- and while the outdated 6 looked nice sufficient, its tight dimensions and lackluster acceleration averted it from succeeding in an increasingly more competitive marketplace.

the new 6 demonstrates that Mazda wasn't going to make the identical mistake twice. want room to chill out? It bargains one of the vital accommodating cabins of any midsize sedan, with Texas-sized front seats and a backseat match for 6-footers. acquired baggage, or in all probability 4 units of golf golf equipment? The midsize Mazda now options an impressively massive 16.6 cubic toes of trunk space. need energy? Mazda's received you covered with the biggest and strongest V6 on this worth bracket, though its gasoline financial system is disappointing. need to make a way commentary? then you definately shouldn't be shopping for a family sedan within the first position. but as such cars go, we think the brand new 6 manages to be tautly horny, in particular from the front, despite its additional 6.1 inches of length and 2.three inches of width. in contrast to the previous model, the 2016 Mazda6 Interior Review  used to be engineered namely for the North American market -- and it shows.

It additionally method introduced weight, as the enlarged 6 is roughly a hundred and fifty pounds heavier than its predecessor. yet you can nonetheless discover the sporty reflexes that consumers have come to expect from this efficiency-oriented brand. physique roll is minimal through domestic-sedan requirements, and the steerage is light however responsive. The 6 by no means allows you to omit its really extensive dimension, but its smartly-sorted chassis and light-on-its-ft character at pace places it 2nd most effective to the Nissan Altima within the fun-to-force category.

Wait a minute, you might be announcing -- how might the "zoom-zoom" household sedan fail to be the sportiest automobile in its class? Three causes: The Altima's significantly lighter, its physique motions are higher managed and its steering is tighter and more communicative. however so what? because the previous 6 validated (and the Toyota Camry continues to reveal), prime-notch riding dynamics don't a very best-promoting household sedan make. What the brand new 6 offers is the coveted combination of american-fashion measurement and energy, along with above-average handling for individuals who care about that kind of thing. authentic fans won't in finding the 6 to their liking -- however hiya, that is what the Mazdaspeed three is for.

in short, the 2009 Mazda 6 is at or close to the top of its class in most respects. As universal, although, we suggest check-riding as many rival models as conceivable earlier than making your resolution, including the Chevrolet Malibu, Honda Accord, Hyundai Sonata, Nissan Altima, Subaru Legacy and Toyota Camry. each has its own strengths and weaknesses, but few can suit the 6's all-round competence and attraction.
battery for mazda 6 key fob
The 2009 Mazda 6 is a entrance-wheel-pressure midsize sedan. There are seven trim ranges: i SV, i activity, i journeying, i Grand visiting, s recreation, s journeying and s Grand visiting. fashions with the i prefix include the 4-cylinder engine, whereas fashions with the s prefix come with the V6.

the worth leader i SV comes usual with sixteen-inch metal wheels, a manually top-adjustable driver seat, full power accessories, a six-speaker CD stereo machine with guidance-wheel-installed audio controls, air-conditioning and a tilt/telescoping steerage column. The i recreation adds cruise keep an eye on, an auxiliary enter jack and keyless entry. The i traveling edition steps up the function content material with 17-inch alloy wheels, foglights, a commute pc, a power driver seat, keyless ignition/entry, electroluminescent gauges, black patterned accent pieces and a leather-based-wrapped steerage wheel and shift knob. The i Grand travelling model provides xenon headlights, heated leather seats with a reminiscence perform for the driving force seat, Bluetooth connectivity, twin-zone automatic climate regulate, auto-dimming mirrors and an auditory blind-spot monitoring device.

The s fashions feature the corresponding i models' usual gear together with just a few enhancements -- the s sport comes with 17-inch alloy wheels and dual exhaust retailers, and the s traveling and s Grand travelling add 18-inch alloy wheels.

A comfort package for visiting models tacks on most of the Grand visiting's standard luxuries, while the Moonroof and Bose bundle adds a sunroof and an upgraded audio system to journeying and Grand journeying fashions. A navigation system is to be had most effective on the Grand journeying. other choices include faraway start, an in-sprint six-CD changer and satellite tv for pc radio.
mazda 6 check engine light
The front-wheel-pressure 2009 Mazda 6 is powered through either a 2.5-liter 4-cylinder engine or a three.7-liter V6. The 4 generates 170 horsepower and 167 pound-feet of torque, while the V6 pumps out 272 hp and 269 lb-ft. A six-velocity manual transmission is standard on 4-cylinder fashions, with a five-velocity automated non-compulsory on all however the i SV. A six-velocity computerized is necessary on V6-powered models.

gasoline-economy estimates stand at 20 mpg metropolis/29 freeway and 23 mixed for 4-cylinder fashions with the manual transmission, while the five-speed auto improves the 4's numbers to 21 mpg metropolis/30 freeway and 24 blended. These are classification-competitive numbers. however, if you go for the V6, estimates drop to 17 mpg city/25 freeway and 20 mixed, which is ready as bad because it gets on this phase.
common problems with mazda 6
Antilock disc brakes, balance control, front-seat facet airbags and entire-length facet curtain airbags are all same old on the aftermarket mazda 6 parts.
difference between mazda 3 and mazda 6
The mazda 6 bluetooth issues control structure is in most cases intuitive, with all major knobs and buttons naturally labeled and simply manipulated. it's sexy, too, with crimson backlighting for the gauges and a swish middle stack sweeping ahead towards the windshield, despite the fact that the atypical black-and-silver patterned plastic trim in journeying models and above is not going to strike everyone's fancy. materials high quality is hit-or-pass over, as the wealthy-feeling soft-contact subject material on the passenger side of the dashboard contrasts with low-cost arduous stuff on the driving force aspect. The emergency brake additionally feels a bit chintzy for this value point. The generously proportioned seats are somewhat at ease, then again, with abundant leg- and headroom all around. On the downside, energy-adjustable lumbar beef up is unavailable, and the not obligatory manually adjustable driver-facet lumbar toughen operates by the use of a labor-intensive knob.

in the audio department, the 6's usual stereo is just enough, and while the optional Bose gadget sounds markedly better, it lacks the clarity and wealthy bass response of the best stereos on this category. there may be better information on the cargo-carrying entrance, the place the sixteen.6-cubic-foot trunk units a new same old for domestic sedans. furthermore, it can be more desirable with the aid of upscale strut helps that do not impinge on the cargo area, and the 60/40-cut up-folding rear seats add to the 6's impressive hauling capabilities.
mazda 6 extended warranty
A awesome quantity of highway noise filters into the 6's cabin at velocity. Pavement imperfections barely ruffle the 6's composure, although, even when it is equipped with the non-compulsory 18-inch wheels. the bottom 2.5-liter engine produces wheezy noises and tepid acceleration, though the slick-shifting six-speed handbook shifter livens things up a little. The 5-pace automated is much less engaging but offers remarkably refined shifts. the large 3.7-liter V6 feels and sounds muscular, but it's a clean operator, even at greater engine speeds. unluckily, the six-pace computerized is not tuned for enthusiastic using -- downshifts are delayed, even in guide mode. handling is spectacular for a big household sedan, however the 6 would not really feel as tossable in corners as the Altima, and its guidance is lighter and looser than the nimble Nissan's. there may be most likely sufficient zoom on this chassis to placate folks that like to pressure, whereas the average client will relish the 6's reasonably compliant journey.
Categories: DBA Blogs

Microsoft Hadoop: Taming the Big Challenge of Big Data – Part One

Pythian Group - Tue, 2014-09-30 11:12

Today’s blog post is the first in a three-part series with excerpts from our latest white paper, Microsoft Hadoop: Taming the Big Challenge of Big Data.

As companies increasingly rely on big data to steer decisions, they also find themselves looking for ways to simplify its storage, management, and analysis. The need to quickly access large amounts of data and use them competitively poses a technological challenge to organizations of all sizes.

Every minute, about two terabytes of data are being generated globally. That’s twice the amount from three years ago and half the amount predicted for three years from now.

Volume aside, the sources of data and the shape they take vary broadly. From government records, business transactions and social media, to scientific research and weather tracking, today’s data come in text, graphics, audio, video, and maps.

Download our full white paper which explores the impact of big data on today’s organizations and its challenges.

Categories: DBA Blogs

Packt Publishing - ALL eBooks and Videos are just $10 each or less until the 2nd of October

Surachart Opun - Tue, 2014-09-30 10:36
Just spread good campaign from Packt Publishing - It's a good news for people who love to learn something new - ALL eBooks and Videos are just $10 or less -- the more you choose to learn, the more you save:
  • Any 1 or 2 eBooks/Videos -- $10 each
  • Any 3-5 eBooks/Videos -- $8 each
  • Any 6 or more eBooks/Videos -- $6 each


Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs

OCP 12C – Managing CDBs and PDBs

DBA Scripts and Articles - Tue, 2014-09-30 10:13

Connecting to a CDB or PDB Connecting to a CDB or PDB is not different of connecting to a standard database. When you create a PDB, it is automatically assigned a service which can be used to reach this specific PDB. You can either use the EZ connect syntax, the Oracle Net Service syntax or the [...]

The post OCP 12C – Managing CDBs and PDBs appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Basics of Multitenant Container Database (CDB)

DBA Scripts and Articles - Mon, 2014-09-29 14:42

The multitenant architecture Needs enterprise edition and the multitenant option. Consists of a CDB (Container database) and zero, one or up to 252 PDBs (pluggable databases). Has a root container (the CDB itself) and a seed container (template to create PDBs) There is only one instance per CDB. A PDB doesn’t have : background processes [...]

The post OCP 12C – Basics of Multitenant Container Database (CDB) appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

OCP 12C – Enterprise Manager and Other Tools

DBA Scripts and Articles - Mon, 2014-09-29 11:20

EM Database Express The old Enterprise Manager database control is replaced by Enterprise Manager database express in Oracle 12c. It contains only basic administration capabilities as the advanced ones are included in Enterprise Manager cloud control With EM database Express you can manage Security (Users, roles, profiles), Configuration (Instance Parameters, memory, database features), Storage (tablespaces, [...]

The post OCP 12C – Enterprise Manager and Other Tools appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Partner Webcast – Oracle NoSQL key-value database

The Oracle NoSQL Database is a horizontally scaled, Key-Value database for Web Services and Cloud, designed specifically to provide highly reliable, scalable and available data storage across a...

We share our skills to maximize your revenue!
Categories: DBA Blogs

The Hitchhiker’s Guide to the EXPLAIN PLAN: The story so far (Part 11–22)

Iggy Fernandez - Sat, 2014-09-27 17:20
On the Toad World site, I’m writing a series of blog posts and Wiki articles on the subject of EXPLAIN PLAN. I’m using EXPLAIN PLAN as a motif to teach not just SQL tuning but also relational theory, logical database design, and physical database design. In a year’s time, I hope to have enough material for […]
Categories: DBA Blogs

I Heart Logs - Event Data, Stream Processing, and Data Integration by Jay Kreps; O'Reilly Media

Surachart Opun - Fri, 2014-09-26 23:01
As I have worked in server-side a long time as System Administrator. I must spend with logs. To use it for checking and investigation in issue. As some policies in some Companies, they want to keep logs over year or over ten years. So, it is not unusual to find out idea to store, integrate logs and do something.
A book tittle "I Heart Logs - Event Data, Stream Processing, and Data Integration" by Jay Kreps. It's very interesting. I'd like to know what I can learn from it, how logs work in distributed systems and learn from author who works at LinkedIn. A book! Not much for the number of pages. However, it gives much more for data flow idea, how logs work and author still shows readers why logs are worthy of reader's attention. In a book, that has only 4 chapters, but readers will get concept and idea about Data integration (Making all of an organization’s data easily available in all its storage and processing systems), Real-time data processing (Computing derived data streams) and Distributed system design (How practical systems can by simplified with a log-centric design). In addition, I like it. because author wrote from his experience at LinkedIn.

After reviewing: A book refers a lot of information(It's easy on ebook to click links) that's useful. Readers can use them and find out more on the Internet and use. For Data integration, It's focused to Kafka software that is a distributed, partitioned, replicated commit log service. It provides the functionality of a messaging system. Additional, It gave why the Big Data Lambda Architecture is good for batch system and a stream processing system and point about things a log can do.

So, Readers will be able to learn:
  • Learn how logs are used for programmatic access in databases and distributed systems
  • Discover solutions to the huge data integration problem when more data of more varieties meet more systems
  • Understand why logs are at the heart of real-time stream processing
  • Learn the role of a log in the internals of online data systems
  • Explore how Jay Kreps applies these ideas to his own work on data infrastructure systems at LinkedIn
Book - I Heart Logs - Event Data, Stream Processing, and Data Integration
Author: Jay KrepsWritten By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs

Patch 19183482 resolves ORA-01403 getting plan with baseline

Bobby Durrett's DBA Blog - Fri, 2014-09-26 14:51

I was testing SQL plan baselines on a base 11.2.0.3 release of Oracle on a 64 bit Linux virtual machine.  I ran DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to create a SQL plan baseline for a test query after running that query once to get its plan in the cursor cache.  When I ran the test query after creating the SQL plan baseline and called dbms_xplan.display_cursor to see its new plan I got an ORA-01403 error:

ORCL:SYSTEM>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  4mu5a860ardpz, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 4mu5a860ardpz, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

I applied patch 19183482 to my test system and the ORA-01403 error went away:

ORCL:SYSTEM>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  4mu5a860ardpz, child number 1
-------------------------------------
select sum(blocks) from test

Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    29 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|   2 |   TABLE ACCESS FULL| TEST |  2844 |  8532 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / TEST@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) SUM("BLOCKS")[22]
   2 - "BLOCKS"[NUMBER,22]

Note
-----
   - SQL plan baseline SQL_PLAN_dscatqjvyk4qy6b581ab9 used for this statement

Here is a zip of the script that I ran to create the ORA-01403 error and the output that it generated with and without the patch applied on my test database: zip

Here is a list of the bugs that patch 19183482’s readme says it will fix:

14512308: SPM BASELINE PLAN CANNOT BE REPRODUCED
15858022: ‘LIBRARY CACHE: MUTEX X’ AND LIBRARY CACHE LOCKS  PURGED_CURSOR
16400122: SPIKES IN LIBRARY CACHE
16625010: SPM BASELINE NOT WORKING FOR SQL CALLED FROM PL/SQL

I haven’t gotten very far into my investigation of SQL plan baselines but it looks like it would be a good idea to apply 19183482 before using SQL plan baselines on 11.2.0.3.  I barely got started using SQL plan baselines and I immediately hit this bug.

– Bobby









Categories: DBA Blogs

Log Buffer #390, A Carnival of the Vanities for DBAs

Pythian Group - Fri, 2014-09-26 10:50

All eyes are on the Oracle Open World. Thousands of sessions, demos, and labs topped up with the countless networking opportunities, Moscone Center is the place to be.

Oracle:

Oracle OpenWorld 2014 – Bloggers Meetup.

Query to show sql_ids related to SQL Profiles.

Oracle OpenWorld is nearly upon us and the agenda is packed with interesting sessions. Prominent among these are several sessions by customers who are able to share their stories of success with Oracle Multitenant.

The biggest challenge for people coming to such huge events as Oracle OpenWorld is to navigate through all of the events that simultaneously happen at various locations.

Pythian at Oracle OpenWorld 2014.

SQL Server:

Monitoring Longest Running Transaction using SQL Server Agent Alerts .

Free PDF Booklet: 119 SQL Code Smells.

Level 1: What is “SQL Server AlwaysOn“?

10 things I learned about rapidly scaling websites with Azure.

Importing Excel Data into SQL Server Via SSIS: Questions You Were Too Shy to Ask

MySQL:

How to do reverse engineering with MySQL Workbench.

MySQL 5.5.40 Overview and Highlights.

MySQL 5.7.5: GROUP BY respects functional dependencies!

JSON UDF functions version 0.3.2 have been released.

Importing related MySQL tables into an Excel Data Model using MySQL for Excel.

Categories: DBA Blogs

On the Road with Luan

Pythian Group - Fri, 2014-09-26 08:59

For the months of September, October, and November, Microsoft SQL Server MVP Luan Moreno will be touring Brazil, Europe, and EUA for various speaking engagements. He’ll be sharing his SQL Server knowledge and insights during SQL Saturdays, conferences, and virtual conferences.

“Pythian is a company that values and truly supports employees in sharing their knowledge at community events,” Luan says. “It’s a pleasure to be a part of this amazing company.”

 

Date Location Event Topic Notes 27-Sep-14 São Paulo, Brazil SQL Saturday #325 In-Memory OLTP a.k.a Hekaton Speaking schedule 28-Sep-14 São Paulo, Brazil SQL Saturday #325 MythBusters – Caçadores de Mitos Speaking schedule 6-Oct-14 Online MVP ShowCast 2014 In-Memory OLTP – Cenários de Aplicação Register here 7-Oct-14 Online 24 Hours of Pass Troubleshooting SQL Server Extended Events Register here 7-Oct-14 Online 24 Hours of Pass In-Memory OLAP a.k.a ColumnStore Index Internals Register here 17-Oct-14 Porto Alegre, Brazil TDC 2014 ORM e Consultas x Performance Speaking schedule 25-Oct-14 Rio de Janerio, Brazil SQL Saturday #329 In-Memory OLAP a.k.a ColumnStore Index Speaking schedule Oct 28-31 Barcelona, Spain TechED 2014 Europe Subject Matter Expert (SME) – SQL Server Event schedule Nov 3-6 Redmond, Seattle MVP Summit 2014 TBD Event schedule Nov 4-7 Redmond, Seattle Pass Summit 2014 TBD Event schedule

 

Will you be attending any of these events? If so, Luan extends the invite to chat SQL Server over a coffee! Reach out to him at moreno@pythian.com or follow him on Twitter at @luansql.

Categories: DBA Blogs

12c: Does PDB Have An SPfile?

Oracle in Action - Fri, 2014-09-26 01:39

RSS content

In a multi-tenant container database, since there are many PDB’s per CDB, it is possible for set some parameters for each individual PDB. The SPFILE for CDB stores parameter values associated with the root which apply to the root, and serve as default values for all other containers. Different values can be set in PDBs for those parameters where the column ISPDB_MODIFIABLE in V$PARAMETER is TRUE. The parameters are set for a PDB and  are stored in table PDB_SPFILE$ remembered across PDB close/open and across restart of the CDB.

– Currently  I have a CDB called CDB1 having one PDB – PDB1.

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> sho pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED                       READ ONLY  NO
3 PDB1                           READ WRITE NO

– There is a table pdb_spfile for each of the containers (cdb$root and PDB1)

SQL>  select con_id,  table_name from cdb_tables  where table_name = 'PDB_SPFILE$';

CON_ID TABLE_NAME
---------- --------------------
3 PDB_SPFILE$
1 PDB_SPFILE$

– pdb_spfile contains only those parameters which have been specifically   set for a container hence currently there are  no records

SQL>   col container_name for a10
col parameter for a20
col value$ for a30

select container.name container_name, par.name PARAMETER,
par.value$
from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
and par.name = 'cursor_sharing';

no rows selected

– Let’s explicitly set cursor_sharing = ‘exact’ in root and check if   it is reflected in pdb_spfile$

SQL> alter system set cursor_sharing='similar';

col container_name for a10
col parameter for a20
col value$ for a30
select container.name container_name, par.name PARAMETER,
par.value$
from pdb_spfile$ par, v$containers container
where par.pdb_uid = container.con_uid
and par.name = 'cursor_sharing';

no rows selected

– It does not show any results but v$spparameter has been updated    probably implying that spfile for the root is maintained in the    operating system only and pdb_spfile does not contain info about parameters in cdb$root.

SQL> select name, value from v$spparameter where name='cursor_sharing';

NAME                           VALUE
------------------------------ -------
cursor_sharing                 similar

-- v$parameter shows the value of parameter for root

SQL> col name for a30
col value for a30

select con_id, name, value from v$parameter
where name = 'cursor_sharing';


CON_ID NAME                           VALUE
---------- ------------------------------ --------
1 cursor_sharing                 similar

– To see parameters for the CDB and all the PDB’s (except PDB$SEED),  v$system_parameter can be accessed. It can be seen that currently it shows only the value for the CDB which will be inherited by all the PDB’s.

SQL>select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ---------
0 cursor_sharing                 similar

– Change container to PDB1 and verify that PDB has inherited the value from CDB

SQL> alter session set container=pdb1;

sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ -------     ---------------
cursor_sharing                       string      similar

– Since parameter has not been explicitly specified    in PDB ,  v$spparameter shows record  for con_id = 0 and null in value column

SQL> select con_id, name, value from v$spparameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
0 cursor_sharing

– Let’s check if the parameter can be modified for the PDB

SQL> col ispdb_modifiable for a17
select con_id, name, value, ispdb_modifiable

from v$parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE      ISPDB_MODIFIABLE
---------- ------------------------------ ---------- -----------------
3 cursor_sharing                 similar    TRUE

– Since the parameter can be modified in PDB, let us modify its value in PDB to ‘FORCE’

SQL> alter system set cursor_sharing = 'FORCE';

sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
cursor_sharing                       string      FORCE

SQL> select con_id, name, value from v$parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
3 cursor_sharing                 FORCE

– v$spparameter shows updated value but con_id is still 0 (bug??)

SQL> select con_id, name, value from v$spparameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
 0 cursor_sharing                 FORCE

– Current value of the parameter  for PDB can be viewed from root using v$system_parameter

SQL> alter session set container=cdb$root;
select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

– Current value in spfile of PDB can be seen from pdb_spfile$

SQL> col value$ for a30
select pdb.name PDB_NAME, par.name PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- ------------------------------
PDB1       cursor_sharing       'FORCE'

– The parameter still has earlier value of similar for cdb$root

SQL> sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
cursor_sharing                       string      similar

SQL> col name for a30
col value for a30

select con_id, name, value from v$parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ --------
1 cursor_sharing                 similar

– Let’s check if modified value persists across closing / opening of the PDB

SQL> alter pluggable database pdb1 close;

– After PDB is closed, entry in its spfile is still visible    but current value cannot be seen as PDB is closed

SQL> col value$ for a30
select pdb.name PDB_NAME, par.name PARAMETER, par.value$
from pdb_spfile$ par, v$pdbs pdb
where par.pdb_uid = pdb.con_uid
and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- -------------
PDB1       cursor_sharing       'FORCE'

SQL> select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ------------
0 cursor_sharing                 similar

– It can be seen that after PDB is re-opened, the updated
   value still persists

SQL>  alter pluggable database pdb1 open;

select con_id, name, value from v$system_parameter
where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -------------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

– Let’s verify that parameter change persists across CDB shutdown

SQL> shu immediate;
     startup
     alter pluggable Database  pdb1 open;

     select con_id, name, value 
     from   v$system_parameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -----------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

SQL> col value$ for a30
     select pdb.name PDB_NAME, par.name 
            PARAMETER, par.value$
     from pdb_spfile$ par, v$pdbs pdb
     where par.pdb_uid = pdb.con_uid
      and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- ------------------------------
PDB1       cursor_sharing       'FORCE'

– Now we will change the parameter in PDB spfile only

SQL> alter session set container=pdb1;

     alter system set cursor_sharing = 'EXACT' scope=spfile;

– Current value still remains FORCE

sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
cursor_sharing                       string      FORCE

–Value has been changed to EXACT in SPfile only

SQL> select con_id, name, value
     from     v$spparameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -----------
0 cursor_sharing                 EXACT

– The above changes can be seen from root as well

SQL> alter session set container=cdb$root;

-- The current value is shown as FORCE

SQL> select con_id, name, value 
      from  v$system_parameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ ----------
0 cursor_sharing                 similar
3 cursor_sharing                 FORCE

– The value in SPFILE is ‘EXACT’ as set

SQL> col value$ for a30
     select pdb.name PDB_NAME, par.name 
            PARAMETER, par.value$
     from pdb_spfile$ par, v$pdbs pdb
      where par.pdb_uid = pdb.con_uid
     and par.name = 'cursor_sharing';

PDB_NAME   PARAMETER            VALUE$
---------- -------------------- ------------------------------
PDB1       cursor_sharing       'EXACT'

– Let’s close and re-open PDB to vefify that value in spfile is
   applied

SQL> alter pluggable database pdb1 close;

    alter pluggable database pdb1 open;
  
     select con_id, name, value 
     from   v$system_parameter
     where name = 'cursor_sharing';  2

CON_ID NAME                           VALUE
---------- ------------------------------ ---------------
0 cursor_sharing                 similar
3 cursor_sharing                 EXACT

– Since the value in spfile is same as default, we can remove this
entry by resetting the value of the parameter.

SQL> alter session set container=pdb1;
     alter system reset cursor_sharing;
     sho parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
cursor_sharing                       string      EXACT

– The entry has been deleted from spfile

SQL> select con_id, name, value 
     from v$spparameter
     where name = 'cursor_sharing';

CON_ID NAME                           VALUE
---------- ------------------------------ -------------
0 cursor_sharing

SQL> alter session set container=cdb$root;

     col value$ for a30
     select pdb.name PDB_NAME, par.name 
            PARAMETER, par.value$
     from pdb_spfile$ par, v$pdbs pdb
     where par.pdb_uid = pdb.con_uid
     and par.name = 'cursor_sharing';

no rows selected

I hope this post was useful. Your comments and suggestions are always welcome!!

References:

Oracle documentation

——————————————————————————————-

Related Links:

Home

Oracle 12c Index

 

 



Tags:  

Del.icio.us
Digg

Comments:  0 (Zero), Be the first to leave a reply!
You might be interested in this:  
Copyright © ORACLE IN ACTION [12c: Does PDB Have An SPfile?], All Right Reserved. 2014.

The post 12c: Does PDB Have An SPfile? appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Query to show sql_ids related to SQL Profiles

Bobby Durrett's DBA Blog - Thu, 2014-09-25 17:58

I have a number 0f SQL Profiles on a database I’m working on and I wanted to know the sql_id of the SQL statements that they relate to.  Here is what I came up with:

select distinct 
p.name sql_profile_name,
s.sql_id
from 
dba_sql_profiles p,
DBA_HIST_SQLSTAT s
where
p.name=s.sql_profile;

Here is the output on my system:

SQL_PROFILE_NAME               SQL_ID
------------------------------ -------------
coe_5up1944u1zb7r_1979920644   5up1944u1zb7r
coe_b9ad7h2rvtxwf_3569562598   b9ad7h2rvtxwf
coe_9f21udzrfcygh_2815756405   9f21udzrfcygh
coe_366brswtxxuct_10194574     366brswtxxuct
coe_2261y37rp45py_2815756405   2261y37rp45py

These are all profiles I created manually with the SQLT scripts so the profile name includes the sql_id but not all sql profiles are like this.  I have more entries in dba_sql_profiles than these five but these are the only rows that matched a row in dba_hist_sqlstat so I guess this won’t work for queries that are not used very often or are so fast that they don’t get pulled into the tables that are behind dba_hist_sqlstat.

– Bobby



Categories: DBA Blogs