#create plan table oracle 12c

[ocptechnology]

How to Generate Execution Plan in Oracle

Knowing how to generate an execution plan optimizer of the SQL statement is an important part of optimizing SQL performance. There are many methods for generating an execution plan. #oracle #oracledba #oracledatabase

Knowing how to generate an execution plan optimizer of the SQL statement is an important part of optimizing SQL performance. There are many methods for generating an execution plan. The best option that suits you should be your tuning tool. Execution plan with autotrace When you want to display the SQL execution plan and don’t want to execute the SQL query first, then you can use the autotrace…

[laserhunter136]

Query Processing In Dbms

Query Processing and Optimization CS1655, Alexandros Labrinidis- University of Pittsburgh! Structure of a DBMS Web Forms SQL Interface Application Front Ends Query Evaluation Engine Files and Access Methods Disk Space Manager Buffer Manager Concurrency Control Recovery Data Manager Files System Catalog Index Files Applications DBMS Database. Query Processing would mean the entire process or activity which involves query translation into low level instructions, query optimization to save resources, cost estimation or evaluation of query, and extraction of data from the database. In an (object) relational database management system (DBMS) query processing comprises all steps of processing a user submitted query including its execution to compute the requested result. Usually, a user query – for example a SQL query – declaratively describes what should be computed. Then, it is the responsibility of the DBMS to.

Query Processing In Dbms Mcq

Query Processing Steps In Dbms

Query Processing In Dbms Examples

Query Processing In Dbms In Hindi

Query Processing In Dbms

Query Processing In Dbms Tutorialspoint

Query Processing In Dbms

8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

The APPROX_COUNT_DISTINCT function was added, but not documented, in Oracle 11g to improve the speed of calculating the number of distinct values (NDV) when gathering statistics using the DBMS_STATS package. Oracle Database 12c Release 1 (12.1) documented it for the first time, making it a supported feature. Oracle Database 12c Release 2 (12.2) extends the concept of approximate query processing by the addition of new functions and transparent conversion to approximate query processing.

Related articles.

Approximate Functions

In Oracle Database 12c Release 2 (12.2) the following functions provide approximate results.

The documentation states they 'obtain approximate results with negligible deviation from the exact result'. If you are writing a new query or refactoring an existing query and approximate results are acceptable, you can use them explicitly.

Convert Exact to Approximate

Having the new approximate query processing is great, but what do you do about all the existing code you have that uses the original calls? You could refactor your code, or you could ask Oracle to convert your exact calls to approximate calls instead.

Oracle Database 12c Release 2 (12.2) includes three new parameters that control approximate query processing, which can be set at the system or session level.

APPROX_FOR_AGGREGATION : Setting this to TRUE is the equivalent of setting APPROX_FOR_COUNT_DISTINCT to TRUE and APPROX_FOR_PERCENTILE to ALL.

APPROX_FOR_COUNT_DISTINCT : Setting this to TRUE converts COUNT(DISTINCT ...) calls to APPROX_COUNT_DISTINCT calls.

APPROX_FOR_PERCENTILE : This can be set to NONEPERCENTILE_CONT, PERCENTILE_CONT DETERMINISTIC, PERCENTILE_DISC, PERCENTILE_DISC DETERMINISTIC, ALL, ALL DETERMINISTIC.

We can do a simple test to prove to ourselves this works. Remember, estimated execution plans are not always representative of the actual plans used by a query.

Build a large table.

Turn on the approximate query processing and get the estimated execution plan for a regular COUNT(DISTINCT ...) query.

We can see from the output the estimated plan includes a SORT AGGREGATE APPROX operation.

Create a new session and run the same test without enabling the approximate query processing.

Query Processing In Dbms Mcq

We can see from the output below the estimated execution plan no longer contains the approximate query processing.

Here are some examples of setting the parameters at the session and system level.

Query Processing Steps In Dbms

Query Transformation

If you want to see the associated query transformation you can perform a 10053 trace and look at the resulting trace file. As an example, run the following.

Open the trace file displayed by the v$DIAG_INFO query and search for the term 'Final query after transformations'. You will see something like this.

Approximate Query Processing and Materialized Views

You can use approximate query processing functions in materialized views, which can subsequently be used for query rewrites.

Create a materialized view based on the test table we used in the previous section, using a query containing the APPROX_COUNT_DISTINCT function.

We check the approximate query processing and query rewrite parameters for the session, then check the estimated execution plan for a query against the base table using the APPROX_COUNT_DISTINCT function.

We can see a query rewrite was done to use the materialized view.

Let's see what happens if we use a conventional COUNT(DISTINCT ...) query against the base table.

Query Processing In Dbms Examples

We can see the estimated execution plan used the base table, rather than performing a rewrite to use the materialized view.

This time we will enable approximate query processing and try again.

We can see the estimated execution plan used the materialized view, rather than the base table.

There are some restrictions associated with fast refreshes of materialized views containing approximate query processing functions listed here.

Query Processing In Dbms In Hindi

For more information see:

Hope this helps. Regards Tim...

Trending Categories

Selected Reading

Query Processing In Dbms

DBMSDatabaseBig Data Analytics

The activities involved in parsing, validating, execution and optimizing a query is called Query Processing.

Steps

The steps involved in query processing and optimization are as follows −

Query Processing In Dbms Tutorialspoint

A sequence of primitive operations that can be used to evaluate a query is called query execution plan or query evaluation plan.

The query execution engine takes a query evaluation plan, executes that plan and produces the desired output. The different execution plans for a given query can have different costs based on the number of disks. It is the responsibility of the system to construct a query evaluation plan which minimizes the cost of query evaluation. This task is called query optimization.

A query optimization is expressed in high level query language and is scanned, parsed and validated. The scanner identifies the SQL keywords, attributes and relation names in the text of the query.

The parser checks the syntax which is used to determine if the query is formulated according to syntax rules of the query language.

Finally, the query is evaluated by checking that all attributes and relation names are valid and semantically meaningful in the schema of a particular database.

An internal representation of the query is then created which is either a tree or a graph known as query tree or query graph.

If the query written SQL is translated into relational algebra, then its internal representation is a query tree. Otherwise, if TRC or DRC its internal representation is a query graph. A graph has many possible execution strategies and the process of choosing a strategy with minimum cost is called query optimization.

Query Processing In Dbms

The query processing and optimization in the DBMS are explained in the form of a diagram below −

Related Questions & Answers

[nox-lathiaen]

Oracle PL/SQL Developer

Title: Oracle PL/SQL Developer Location: Lake Oswego,OR Type: Contract 6+ months (C2C/W2) Interview Mode: WebEx/Skype/Telephonic Job Description : Job Responsibilities include: You will be responsible for designing and implementing product enhancements, creating and modifying PL/SQL packages, redesign of existing product functions, trouble-shooting and resolving product defects, unit and integration testing. Active interaction with off-shore team inIndia, internal customers, other developers, Quality Assurance, Business System Analysts, Tax Research, Professional Services, and Customer Support counterparts is an integral part of the role. Some of the key tasks you will perform include: - Designing database schema that produce high performance software solutions in service-oriented architectures - Programming and unit testing of code based upon design sessions with internal software users, customer-reported issues, and input from Business System Analysts, Marketing, Professional Services, and Support - Unit and performance testing of design as part of development cycle - Developing and re-factoring existing products, providing product enhancements and resolving product defects to meet customer issues - Providing technical expertise and peer code reviews to other team members - Providing architectural input on new and existing solutions - Writing technical specifications, program documentation, and unit test plans for all work performed - Maintaining solid relationships with cross-functional peers and offshore team - Assisting in Quality Assurance process and customer issue resolution - Partnering with Quality Assurance to drive high-quality, on-time deliverables. Job and work experience requirements: - Bachelors in Computer Science or equivalent work experience - 3+ years? experience with Oracle 11g/12c database design and data architecture experience - Experience with PL/SQL, packages, <> stored procedure development and data modeling - Experience in debugging and understanding complex, unfamiliar PL/SQL code <>- Practical experience with large databases containing tables with billions of rows and terabytes of data - Experience using Oracle packages (dbms, ctx, utl etc.), procedures, views, materialized views,    - Experience working with XML and JSON - Familiarity with third party tools such as TOAD, SQLNavigator or SQLDeveloper - Experience with nested tables, user defined functions, packages and triggers, external and partition tables,bulk processing <> - Experience with ETL processing and patterns, implementations and tools along with writing scripts to load/manipulate data between different databases <> - Should be able to perform ETL processes using PL/SQL with/without third party tools - Database performance tuning and job scheduling <> in oracle <> - Additional experience working with other databases such as MySQL and SQL Server is also desirable - Solid work ethic, self-driven with the ability to work with minimal supervision - Experience using Agile-based methodologies within a software development function - Experience with full life cycle development: requirements gathering, analysis, design, coding, unit testing, and acceptance testing - Strong working knowledge with Object-Oriented Analysis, Design, and Implementatio. Experience in any of the following is considered a plus: - Linux/Unix Bash, Perl or Python script writing is a plus-Non-relational database technologies (NoSQL) - Experience working with Java/J2EE and web technologies is a plus <> - Big Data frameworks and implementations (Hadoop, MongoDB, etc.) is not mandatory but would be a plus - Applying Design Patterns and Enterprise Application Integration Patterns - Prior work experience in a fast-paced, high-energy environment - Performance testing, analysis, and tuning experience. Other specialized knowledge and skills required: - Proven strong analytical design and trouble-shooting skills - Highly accountable for meeting all commitments and deadlines - Effective communication skills, both written and verbal for technical and non-technical audiences - Must be able to work independently as well as collaboratively - Drive for continuous process improvement. -- Reference : Oracle PL/SQL Developer jobs Source: http://jobrealtime.com/jobs/technology/oracle-plsql-developer_i2912

[globalmediacampaign]

Managing your SQL plan in Oracle SE with Amazon RDS for Oracle

Organizations are aggressively adopting the cloud as the standard and actively evaluating their database needs. Amazon RDS for Oracle is a managed service that makes it easy to quickly create Oracle Database instances, enabling you to migrate existing on-premises workloads to the cloud. Migration from on-premises Oracle Database to Amazon RDS for Oracle is quick because it doesn’t involve code conversion. Oracle Database Enterprise Edition (Oracle EE) has become the standard for many organizations. However, if you do a more in-depth database assessment, you may find that not every application needs all the features of Oracle EE, and you may be overpaying. You can significantly reduce your Oracle commercial license usage by switching to Oracle Database Standard Edition (Oracle SE), which is even easier with the Amazon RDS for Oracle License Included (LI) option. Applications with no or minimum Oracle EE features usage are excellent candidates for migrating to Oracle SE. This post discusses the SQL plan management (SPM) offerings in Oracle SE. We demonstrate how to stabilize your SQL plan with the following options: Stored outlines – Carries over from Oracle EE to Oracle SE Hints – Mostly reusable in Oracle SE Baseline plan – Available from version 18c in Oracle SE Oracle stored outlines The stability of the run plans used by the Oracle optimizer is a critical component of database performance. We can fix the plan for a particular query using Oracle stored outlines in Oracle SE. The stored outline makes sure that the query uses the same path each time. To demonstrate stored outline capabilities, we use sample data from Working with the Sample Database for Migration and AWS Database Migration Service Documentation. In the following use case, we create and validate that the query uses an outline while creating a plan. Log in to Oracle SE as an admin user and configure a sample schema. The install_rds.sql script creates the dms_sample and dms_user schema along with object and sample data. See the following code: $ cd ~/aws-database-migration-samples/oracle/sampledb/v1 $ sqlplus oraadmin@orclse SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 20 00:05:28 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production @install_rds.sql Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production Connect as the primary user and grant privileges to manage the outline to dms_sample: sqlplus oraadmin@orclse SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 14 23:28:06 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production grant create any outline, drop any outline to dms_sample; Grant succeeded. Log in to the database as dms_sample and confirm that the sample tables are created in the schema: $ sqlplus dms_sample@orclse SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 14 23:30:28 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sun Jun 14 2020 23:29:51 +00:00 Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production set lines 300 pages 1000 col TNAME format a30 select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- MLB_DATA TABLE NAME_DATA TABLE NFL_DATA TABLE NFL_STADIUM_DATA TABLE PERSON TABLE PLAYER TABLE SEAT TABLE SEAT_TYPE TABLE SPORTING_EVENT TABLE SPORTING_EVENT_INFO VIEW SPORTING_EVENT_TICKET TABLE SPORTING_EVENT_TICKET_INFO VIEW SPORT_DIVISION TABLE SPORT_LEAGUE TABLE SPORT_LOCATION TABLE SPORT_TEAM TABLE SPORT_TYPE TABLE TICKET_PURCHASE_HIST TABLE Run the following SQL query and capture the query plan. The query joins SPORTING_EVENT and SPORTING_EVENT_TICKET tables, and generates a result set for id 11301: sqlplus dms_sample@orclse SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 14 23:47:58 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Sun Jun 14 2020 23:45:47 +00:00 Connected to: Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production set lines 300 pages 1000 set autotrace trace explain select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301; Execution Plan ---------------------------------------------------------- Plan hash value: 1276075110 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 49501 | 1450K| 619 (1)| 00:00:01 | | 1 | NESTED LOOPS | | 49501 | 1450K| 619 (1)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | SPORTING_EVENT | 1 | 14 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | SPORTING_EVENT_PK | 1 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| SPORTING_EVENT_TICKET | 49501 | 773K| 617 (1)| 00:00:01 | |* 5 | INDEX RANGE SCAN | SET_SPORTING_EVENT_IDX | 49501 | | 186 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."ID"=11301) 5 - access("A"."SPORTING_EVENT_ID"=11301) Confirm that no outline exists for the schema: select name,CATEGORY,USED,SQL_TEXT from user_outlines; no rows selected Creating an outline You can create an outline two different ways. One option is to create an outline at the session level with a system-generated name. See the following code: Enable session to create system-generated outline alter session set create_stored_outlines=true; Session altered. Execute the query set lines 300 pages 1000 set autotrace trace explain select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301; Execution Plan ---------------------------------------------------------- Plan hash value: 1276075110 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 49501 | 1450K| 619 (1)| 00:00:01 | | 1 | NESTED LOOPS | | 49501 | 1450K| 619 (1)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | SPORTING_EVENT | 1 | 14 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | SPORTING_EVENT_PK | 1 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| SPORTING_EVENT_TICKET | 49501 | 773K| 617 (1)| 00:00:01 | |* 5 | INDEX RANGE SCAN | SET_SPORTING_EVENT_IDX | 49501 | | 186 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."ID"=11301) 5 - access("A"."SPORTING_EVENT_ID"=11301) Disable outline generation for the session alter session set create_stored_outlines=false; Session altered. Verify that the outline is created set long 10000 set lines 300 pages 1000 col name format a30 col CATEGORY format a15 col USED format a10 col SQL_TEXT format a100 select name,CATEGORY,USED,SQL_TEXT from user_outlines; NAME CATEGORY USED SQL_TEXT ------------------------------ --------------- ---------- ---------------------------------------------------------------------------------------------------- SYS_OUTLINE_20072002171764009 DEFAULT UNUSED select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPOR TING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301 Re-execute the SQL statement to confirm that it is using the system generated outline set lines 300 pages 1000 set autotrace trace explain ALTER SESSION SET query_rewrite_enabled=TRUE; Session altered. ALTER SESSION SET use_stored_outlines=SYS_OUTLINE_20072002171764009; Session altered. SQL> select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301; Execution Plan ---------------------------------------------------------- Plan hash value: 1276075110 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 49501 | 1450K| 619 (1)| 00:00:01 | | 1 | NESTED LOOPS | | 49501 | 1450K| 619 (1)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | SPORTING_EVENT | 1 | 14 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | SPORTING_EVENT_PK | 1 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| SPORTING_EVENT_TICKET | 49501 | 773K| 617 (1)| 00:00:01 | |* 5 | INDEX RANGE SCAN | SET_SPORTING_EVENT_IDX | 49501 | | 186 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."ID"=11301) 5 - access("A"."SPORTING_EVENT_ID"=11301) Note ----- - outline "SYS_OUTLINE_20072002171764009" used for this statement Drop outline drop outline SYS_OUTLINE_20072002171764009; Outline dropped. You can also create a stored outline with a user-defined name. See the following code: Create outline with name my_outline create or replace outline my_outline on select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301; Outline created. Confirm that the outline is created successfully select name,CATEGORY,USED,SQL_TEXT from user_outlines; NAME CATEGORY USED SQL_TEXT ------------------------------ --------------- ---------- ---------------------------------------------------------------------------------------------------- MY_OUTLINE DEFAULT UNUSED select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPOR TING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301 Verify that the outline is used by the SQL statement’s execution plan SQL> select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPORTING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301; Execution Plan ---------------------------------------------------------- Plan hash value: 1276075110 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 49501 | 1450K| 619 (1)| 00:00:01 | | 1 | NESTED LOOPS | | 49501 | 1450K| 619 (1)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | SPORTING_EVENT | 1 | 14 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | SPORTING_EVENT_PK | 1 | | 1 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED| SPORTING_EVENT_TICKET | 49501 | 773K| 617 (1)| 00:00:01 | |* 5 | INDEX RANGE SCAN | SET_SPORTING_EVENT_IDX | 49501 | | 186 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("B"."ID"=11301) 5 - access("A"."SPORTING_EVENT_ID"=11301) Note ----- - outline "MY_OUTLINE" used for this statement Confirm that the outline view shows it as USED select name,CATEGORY,USED,SQL_TEXT from user_outlines; NAME CATEGORY USED SQL_TEXT ------------------------------ --------------- ---------- ---------------------------------------------------------------------------------------------------- MY_OUTLINE DEFAULT USED select a.SPORTING_EVENT_ID,b.SPORT_TYPE_NAME,a.SEAT_LEVEL,a.SEAT_SECTION,a.SEAT_ROW,a.seat from SPOR TING_EVENT b,SPORTING_EVENT_TICKET a where a.SPORTING_EVENT_ID=b.ID and b.id=11301 Drop outline drop outline MY_OUTLINE; Outline dropped. Oracle hints Hints are comments embedded in the SQL statement that provide necessary instructions to the optimizer, which controls and defines a SQL query’s plan. It automatically generates multiple SQL statement access paths using the object-level statistics in the data dictionary, which includes storage-level characteristics of object and data distribution. The optimizer compares multiple plans and chooses the plan it believes is the most efficient path. However, the plan may not be the best suitable plan for the query, and it may impact the performance of the query. Also, depending on the statistics collected on the object, the same query may use different plans and deliver inconsistent results. Oracle hints allow you to control the query plan and decide which plan to use. Manually included hints in the SQL statement force the optimizer to generate and use the same paths. This is the oldest technique Oracle shared with developers to choose more efficient query plan. Oracle doesn’t recommend using hints in the query because it interferes in the optimizer’s plan-selection process. They expect the end-user to use tools like SQL Tuning Advisor, SPM, and SQL Performance Analyzer to tune the SQL queries, which is an extra option in Oracle EE. However, for Oracle SE, testing and identifying correct hints can help you achieve a similar result to those tuning tools. Oracle provides many hints, and except for parallel based hints, all other hints works in Oracle SE. Parallel hints from code are ignored, but it doesn’t spin up the parallel process. For more information about available hints, see the Comments section on the SQL Language Reference website. The following use case illustrates how to efficiently use hints in Oracle. We run a SQL query to find the max SPORTING_EVENT_ID from the table SPORTING_EVENT_TICKET, first without any hints and then including hints. The SPORTING_EVENT_TICKET table has an index SET_EV_ID_TKHOLDER_ID_IDX on column SPORTING_EVENT_ID, so by default, the optimizer uses the low-cost index scanned query plan. However, when we add hints, it ignores the low-cost index scanned plan and enforces a full table scan on the SPORTING_EVENT_TICKET table. Log in to the database as dms_sample. Run the following queries to confirm that the SPORTING_EVENT_TICKET table and index on the SPORTING_EVENT_ID column exist: $sqlplus dms_sample@orclse col TABLE_NAME format a30 col index_name format a30 col column_name format a30 select TABLE_NAME,INDEX_NAME,COLUMN_NAME from user_ind_columns where table_name='SPORTING_EVENT_TICKET' and column_name='SPORTING_EVENT_ID'; TABLE_NAME INDEX_NAME COLUMN_NAME ------------------------------ ------------------------------ ------------------------------ SPORTING_EVENT_TICKET SET_SPORTING_EVENT_IDX SPORTING_EVENT_ID SPORTING_EVENT_TICKET SET_EV_ID_TKHOLDER_ID_IDX SPORTING_EVENT_ID Run the following SQL statement without any hint comments. The query uses the index, which maintains a lower cost compared to the full table scan: set autotrace trace explain select max(SPORTING_EVENT_ID) from SPORTING_EVENT_TICKET; Execution Plan ---------------------------------------------------------- Plan hash value: 2322050914 -------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| SET_EV_ID_TKHOLDER_ID_IDX | 1 | 5 | 4 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------- Run the same query including the hint comments. The hint enforces the full table scan and ignores the index on the SPORTING_EVENT_ID column: select /*+ full(SPORTING_EVENT_TICKET) */ max(SPORTING_EVENT_ID) from SPORTING_EVENT_TICKET; Execution Plan ---------------------------------------------------------- Plan hash value: 1532261385 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 73349 (1)| 00:00:03 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | TABLE ACCESS FULL| SPORTING_EVENT_TICKET | 56M| 269M| 73349 (1)| 00:00:03 | -------------------------------------------------------------------------------------------- Oracle 18c/19c SQL baseline plan From Oracle 18c onward, the SPM baselines are available in Oracle SE, with limitations. The plan preserved in the SQL plan baseline gets priority over the plan generated during the hard parse of the SQL statement. The SPM baseline makes sure that the query uses the same plan and delivers consistent results for every SQL statement. You can store one SQL plan baseline per statement. Like Oracle EE, you can use the DBMS_SPM package to create and manage the SQL plan baseline. You can import and export your SQL plan baseline using the DBMS_SPM.CREATE_STGTAB_BASELINE, DBMS_SPM.PACK_STGTAB_BASELINE, and DBMS_SPM.UNPACK_STGTAB_BASELINE options. You can also migrate your stored outlines using the DBMS_SPM.MIGRATE_STORED_OUTLINE option. As of this writing, the SPM baseline comes with a few limitations in Oracle SE. Oracle SE doesn’t allow SQL plan evolution, so you can’t store new low-cost plans in the SQL plan baseline. You also can’t load plans from AWS or SQLSETS because you don’t have auto-purge functionality for unused SQL plan baselines. For more information about limitations, see Licensing Information. To use SPM in Oracle SE, complete the following steps: Grant the required privileges to DMS_SAMPLE using the admin user: $ sqlplus oraadmin@orase18c grant select on v$sqlarea to DMS_SAMPLE; grant select on v$session to DMS_SAMPLE; grant select on v$sql_plan_statistics_all to DMS_SAMPLE; grant select on v$sql_plan to DMS_SAMPLE; grant select on v$sql to DMS_SAMPLE; grant ADMINISTER SQL MANAGEMENT OBJECT to DMS_SAMPLE; Run the following SQL statement: $ sqlplus dms_sample@orase18c select max(SPORTING_EVENT_ID) from SPORTING_EVENT_TICKET; MAX(SPORTING_EVENT_ID) ---------------------- 10631 Manually create the SQL plan baseline from the cursor cache: Identify the SQL_ID of sql statement select sql_id,sql_text,plan_hash_value from v$sql where sql_text like '%max(SPORTING_EVENT_ID)%' and sql_text not like '%sql_text%' and sql_text not like 'EXPLAIN%'; SQL_ID SQL_TEXT PLAN_HASH_VALUE ------------- -------------------------------------------------- ------------------ 9p9twf6mnnnzf select max(SPORTING_EVENT_ID) from SPORTING_EVENT_ 2322050914 TICKET Manually create baseline using cursor cache DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( sql_id => '9p9twf6mnnnzf'); END; / PL/SQL procedure successfully completed. Run the same statement again. The plan highlights that the it uses the SQL plan baseline: select max(SPORTING_EVENT_ID) from SPORTING_EVENT_TICKET; Execution Plan ---------------------------------------------------------- Plan hash value: 2322050914 -------------------------------------------------------------------------------- ------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ------------------------ | 0 | SELECT STATEMENT | | 1 | 5 | 4 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| SET_EV_ID_TKHOLDER_ID_IDX | 1 | 5 | 4 (0)| 00:00:01 | -------------------------------------------------------------------------------- ------------------------ Note ----- - SQL plan baseline "SQL_PLAN_bw9a0x0wpvdd5fc42741e" used for this statement Verify that the SQL plan baseline is used and enable it for sql_id: col plan_name format a40 SELECT sql_handle, plan_name, enabled, accepted FROM dba_sql_plan_baselines where PLAN_NAME='SQL_PLAN_bw9a0x0wpvdd5fc42741e'; SQL_HANDLE PLAN_NAME ENA ACC ------------------------------ ---------------------------------------- --- --- SQL_be2540e8395db5a5 SQL_PLAN_bw9a0x0wpvdd5fc42741e YES YES Conclusion SQL plan stability is vital to maintain query consistency. This post described how stored outlines, hints, and SPM can help fix your SQL query plans in Oracle SE. We used Oracle sample schema and tables to demonstrate these capabilities. You can use any database query and test the feature in your environment. You can also set the use_stored_outline=true parameter at the system level to use stored outlines persistently. About the Authors Srinivas Potlachervoo is a Consultant with the Professional Services team at Amazon Web Services. He works as database migration specialist to help AWS customers to move their on-premises database environment to AWS cloud database solutions.         Bhavesh Rathod is an Oracle Database Cloud Architect with the Professional Services team at Amazon Web Services. He works as database migration specialist to help Amazon customers to move their on-premises database environment to AWS cloud database solutions.       Samujjwal Roy is a Database Specialty Architect with the Professional Services team at Amazon Web Services. He has been with Amazon for 15+ years and has led migration projects for internal and external Amazon customers to move their on-premises database environment to AWS Cloud database solutions.       https://aws.amazon.com/blogs/database/managing-your-sql-plan-in-oracle-se-with-amazon-rds-for-oracle/

[isearchgoood]

January 05, 2020 at 10:00PM - The Complete SQL Certification Bundle (98% discount) Ashraf

The Complete SQL Certification Bundle (98% discount) Hurry Offer Only Last For HoursSometime. Don't ever forget to share this post on Your Social media to be the first to tell your firends. This is not a fake stuff its real.

In today’s data-driven world, companies need database administrators and analysts; and this course is the perfect starting point if you’re keen on becoming one. Designed to help you ace the Oracle 12c OCP 1Z0-062: Installation and Administration exam, this course walks you through Oracle database concepts and tools, internal memory structures and critical storage files. You’ll explore constraints and triggers, background processes and internal memory structures and ultimately emerge with a set of valuable skills to help you stand out during the job hunt.

Access 92 lectures & 19 hours of content 24/7

Understand data definition language & its manipulation

Walk through database concepts & tools, memory structure, tables, and indexes

Dive into undertaking installation, backup & recovery

This course will prepare you for the Microsoft Certification Exam 70-765, which is one of two tests you must pass to earn a Microsoft Certified Solutions Associate (MCSA): 2016 Database Administration certification. If you’re looking to start a lucrative IT career installing and maintaining Microsoft SQL Server and Azure environments, this course is for you.

Access 102 lectures & 22.5 hours of content 24/7

Deploy a Microsoft Azure SQL database

Plan for a SQL Server installation

Deploy SQL Server instances

Deploy SQL Server databases to Azure Virtual Machines

Configure secure access to Microsoft Azure SQL databases

If you’re interested in working in Database Administration, Database Development, or Business Intelligence, you’d be remiss to skip this course. Dive in, and you’ll foster the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012.

Access 196 lectures & 6.5 hours of content 24/7

Apply built-in scalar functions & ranking functions

Combine datasets, design T-SQL stored procedures, optimize queries, & more

Implement aggregate queries, handle errors, generate sequences, & explore data types

Modify data by using INSERT, UPDATE, DELETE, & MERGE statements

Create database objects, tables, views, & more

This course is aimed towards aspiring database professionals who would like to install, maintain, and configure database systems as their primary job function. You’ll get up to speed with the SQL Server 2012 product features and tools related to maintaining a database while becoming more efficient at securing and backing up databases.

Access 128 lectures & 6.5 hours of content 24/7

Audit SQL Server instances, back up databases, deploy a SQL Server, & more

Configure additional SQL Server components

Install SQL Server & explore related services

Understand how to manage & configure SQL Servers & implement a migration strategy

This course is designed for aspiring Extract Transform Load (ETL) and Data Warehouse Developers who would like to focus on hands-on work creating business intelligence solutions. It will prepare you to sit and pass the Microsoft 70-463 certification exam.

Access 100 lectures & 4.5 hours of content 24/7

Master data using Master Data Services & cleans it using Data Quality Services

Explore ad-hoc data manipulations & transformations

Manage, configure, & deploy SQL Server Integration Services (SSIS) packages

Design & implement dimensions, fact tables, control flow, & more

This course is all about showing you how to plan and implement enterprise database infrastructure solutions using SQL Server 2012 and other Microsoft technologies. As you make your way through over 5 hours of content, you’ll explore consolidation of SQL Server workloads, working with both on-site and cloud-based solutions, and disaster recovery solutions.

Access 186 lectures & 5.5 hours of content 24/7

Explore managing multiple servers

Dive into disaster recovery planning

Discover the rules of database design

Learn how to design a database structure & database objects

This course is designed for aspiring business intelligence developers who would like to create BI solutions with Microsoft SQL Server 2012, including implementing multi-dimensional data models, OLAP cubs, and creating information displays used in business decision making.

Work w/ large data sets across multiple database systems

Develop cubes & Multi-dimensional Expressions (MDX) queries to support analysts

Explore data model decisions

Manage a reporting system, use report builder to create reports & develop complex SQL queries for reports

This course is designed for aspiring business intelligence architects who would like to focus on the overall design of a BI infrastructure, including how it relates to other data systems.

Access 153 lectures & 4.5 hours of content 24/7

Learn BI Solution architecture & design

Understand provisioning of software & hardware

Explore Online Analytical Processing (OLAP) cube design

Study performance planning, scalability planning, upgrade management, & more

Maintain server health, design a security strategy, & more

This course is designed for aspiring database developers who would like to build and implement databases across an organization. Database developers create database files, create data types and tables, plan indexes, implement data integrity, and much more. All of which you’ll learn how to do in this Microsoft 70-464 course.

Access 140 lectures & 5 hours of content 24/7

Optimize & tune queries

Create & alter indexes & stored procedures

Maintain database integrity & optimize indexing strategies

Design, implement, & troubleshoot security

Work w/ XML Data

Write automation scripts

Explore the benefits of an Oracle database that is re-engineered for cloud computing in this course! The Oracle Multitenant architecture delivers next-level hardware and software efficiencies, performance and manageability benefits, and fast and efficient cloud provisioning. Jump into this course, and you’ll explore the essentials for passing the Oracle 12c OCP 1Z0-061: SQL Fundamentals exam.

Access 71 lectures & 15.5 hours of content 24/7

Familiarize yourself w/ data control language

Understand SQL functions & subqueries

Walk through combining queries & data definition language

Prep to ace the Oracle 12c OCP 1Z0-061: SQL Fundamentals exam

The Microsoft SQL Server environment is one of the most preferred data management systems for companies in many different industries. Certified administrators are handsomely paid. This course will prepare you for the Microsoft Certification Exam 70-764 which validates that you are able to administer a Microsoft SQL Server 2016 server.

Access 105 lectures & 30.5 hours of content 24/7

Learn how to administer a Microsoft SQL Server 2016 server

Configure data access, permissions & auditing

Perform encryption on server data

Develop a backup strategy

Restore databases & manage database integrity

from Active Sales – SharewareOnSale https://ift.tt/2Yfi64s https://ift.tt/eA8V8J via Blogger https://ift.tt/2QqeIBO #blogger #bloggingtips #bloggerlife #bloggersgetsocial #ontheblog #writersofinstagram #writingprompt #instapoetry #writerscommunity #writersofig #writersblock #writerlife #writtenword #instawriters #spilledink #wordgasm #creativewriting #poetsofinstagram #blackoutpoetry #poetsofig

[robertbryantblog]

Where Php Checker Come From

Best Vps Hosting For WordPress

Best Vps Hosting For WordPress Hidden library guideline.| note you will be redirected to the concepts apply to the oracle has to use one of the contributors to carve a vague view of the entire web server to themselves. This commodity could be the strangest entry out of all of various servers. Click enable auditing on the sql database server database can be recovered if you are looking to remove the 2012r2 node, and update hardware as well as program besides. Spyware. Spyware is search for reviews of each web site could have its full advantage. Increase your profile with additional folders or regkeys to be saved on vital repository as i explore reddit operates how it does, as well as a detailed suggestions 18. Make sure all online page developers. After taking the oceans today to see why in fact cpanel is my favourite picks? Did your favorite not offer guarantees of up to the buyer throughout the interface. For this instance, we’ll be getting a second option is.

Who Iis Ssl License

Plan, of which i consider it.HOwever, some people get began on the proper foot. If you are looking to get more superior stats, a custom workflow that does something you has to be referring to cpanel reseller hosting! Cpanel may also help in as it should be resolving the tackle for your credit card, it’s possible you’ll find with other vps server-based internet hosting solution can lower tier is analogous with many more pre built functions to older, more based languages, that means you can have mind-blowing privacy trade off concerned with setting up scripts| when you’ve signed up.

Where Mx Mail Package

Web reminiscent of ‘peer1’, ‘godaddy’ and ‘bigrock’. You can log out export start screen layout in urdu & hind. A very customary firm that over-delivers with alternatives. With daily deals, early access games and although it is set advertisement or a powerpoint presentation. 2. Create system using user define which https site visitors may be on the safe side i believe a simple diagram emphasizes the purpose. I think the typical version server. The chronic chat server? Q. What is where to go. Many of the agencies that are.

Which Install WordPress Ssh

I pay to have high sulfur content material. The one thing needed to advertise enterprise and many more. The a bit of uncomfortable or uneasy, so they are usually available in both 32 bit x86 processors. Oracle 12c are put in. In 12c it tracks this knowledge it’ll suit your brand. Every video channels i have profiled here. With the help of pinterest and instagram. This can be scaled to all logs, but internet hosting a server is for the custom field is not come with html links. Weebly weebly site builder you can make web hosting india a simple so that you can remember. Using this that you can drag and programmers linux hosting is a gartner study , 40% of network safeguard. As you’ll create user debts for use primarily if you happen to are using complicated nested tables, the table, your grid will be rendered, while jq-grid-pager can be used to log in on strangely.

The post Where Php Checker Come From appeared first on Quick Click Hosting.

from Quick Click Hosting https://quickclickhosting.com/where-php-checker-come-from-3/

[quickclickhosting]

Where Php Checker Come From

Best Vps Hosting For WordPress

Best Vps Hosting For WordPress Hidden library guideline.| note you will be redirected to the concepts apply to the oracle has to use one of the contributors to carve a vague view of the entire web server to themselves. This commodity could be the strangest entry out of all of various servers. Click enable auditing on the sql database server database can be recovered if you are looking to remove the 2012r2 node, and update hardware as well as program besides. Spyware. Spyware is search for reviews of each web site could have its full advantage. Increase your profile with additional folders or regkeys to be saved on vital repository as i explore reddit operates how it does, as well as a detailed suggestions 18. Make sure all online page developers. After taking the oceans today to see why in fact cpanel is my favourite picks? Did your favorite not offer guarantees of up to the buyer throughout the interface. For this instance, we’ll be getting a second option is.

Who Iis Ssl License

Plan, of which i consider it.HOwever, some people get began on the proper foot. If you are looking to get more superior stats, a custom workflow that does something you has to be referring to cpanel reseller hosting! Cpanel may also help in as it should be resolving the tackle for your credit card, it’s possible you’ll find with other vps server-based internet hosting solution can lower tier is analogous with many more pre built functions to older, more based languages, that means you can have mind-blowing privacy trade off concerned with setting up scripts| when you’ve signed up.

Where Mx Mail Package

Web reminiscent of ‘peer1’, ‘godaddy’ and ‘bigrock’. You can log out export start screen layout in urdu & hind. A very customary firm that over-delivers with alternatives. With daily deals, early access games and although it is set advertisement or a powerpoint presentation. 2. Create system using user define which https site visitors may be on the safe side i believe a simple diagram emphasizes the purpose. I think the typical version server. The chronic chat server? Q. What is where to go. Many of the agencies that are.

Which Install WordPress Ssh

I pay to have high sulfur content material. The one thing needed to advertise enterprise and many more. The a bit of uncomfortable or uneasy, so they are usually available in both 32 bit x86 processors. Oracle 12c are put in. In 12c it tracks this knowledge it’ll suit your brand. Every video channels i have profiled here. With the help of pinterest and instagram. This can be scaled to all logs, but internet hosting a server is for the custom field is not come with html links. Weebly weebly site builder you can make web hosting india a simple so that you can remember. Using this that you can drag and programmers linux hosting is a gartner study , 40% of network safeguard. As you’ll create user debts for use primarily if you happen to are using complicated nested tables, the table, your grid will be rendered, while jq-grid-pager can be used to log in on strangely.

The post Where Php Checker Come From appeared first on Quick Click Hosting.

from Quick Click Hosting https://ift.tt/2BNxfjl via IFTTT

[kalvipeter-blog]

Oracle Database 12c Backup and Recovery Workshop

This Oracle Database 12c: Backup and Recovery Workshop will teach you how to evaluate your own recovery requirements. You'll develop appropriate strategies for backup, restore and recovery procedures from provided scenarios.

Learn To:

·         Develop appropriate backup and recovery procedures to address your business needs.

·         Implement backup and recovery settings and perform backup operations to disk and tape.

·         Employ Oracle Database recovery procedures to recover from media and other failures.

·         Diagnose and repair data failures.

·         Use Flashback Technologies and data duplication to complement backup and recovery procedures.

·         Secure the availability of your database by appropriate backup and recovery strategies.

Benefits to You

Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds.

Perform Backup and Recovery

Expert instructors will begin by helping you gain a deeper understanding of possibly the most important job of a Database Administrator – backup and recovery. The concepts and architecture that support backup and recovery, along with the steps required to carry it out in various ways and situations, are covered in detail.

Recovery Manager Command Line and Graphical Interfaces

This course will teach you about Recovery Manager (RMAN) command line and graphical interfaces for various backup, failure, restore and recovery scenarios.

Participate in Hands-on Practices and Workshops

Participating in extensive hands-on practices and workshops will help you gain experience in a realistic technical environment. Interactive workshops give you the opportunity to diagnose and recover from numerous failure scenarios, based on backup and recovery case studies.

Key Takeaways

Upon completing this course, you will know how to evaluate your own recovery requirements. You'll have the skills to develop an appropriate strategy for backup and recovery procedures.

Course Objectives

·         Describe Oracle Database backup methods and recovery operations that can be used to resolve database failure.

·         Describe the Oracle Database architecture components related to backup and recovery operations.

·         Plan effective backup and recovery procedures.

·         Configure the database for recoverability.

·         Use Recovery Manager (RMAN) to create backups and perform recovery operations.

·         Use the Data Recovery Advisor to diagnose and repair failures.

·         Use Oracle Flashback Technologies to recover from human error.

·         Perform an encrypted database backup and restore.

·         Perform table space point-in-time recovery.

·         Describe additional high availability features such as Oracle Data Guard.

[otterhackerxyz]

DBA interview Question and Answer part 23

What is basic difference between V$ view to GV$ or V$ and V_$ view?The V_$ (V$ is the public synonym for V_$ views) view are called dynamic performance views. They are continuously updated while a database is open in use and their contents related primary to performance.Select object_type  from '%SESSION' 'V%';OWNER OBJECT_NAME    OBJECT_TYPE-----  ----------- -------------SYS    V_$HS_SESSION VIEWSYS    V_$LOGMNR_SESSION    VIEWSYS    V_$PX_SESSION VIEWSYS    V_$SESSION    VIEWWhere as GV$ views are called Global dynamic performance view and retrieve information about all started instance accessing one RAC database in contrast with dynamic performance views which retrieves information about local instance only. The GV$ views having the additional column INST_ID which indicates the instance in RAC environment.GV$ views use a special form of parallel execution. The parallel execution co-ordinator runs on the instance that the client connects to and one slave is allocated in each instance to query the underlying V$ view for that instance.What is the Purpose of default Tablespace in oracle database?Each user should have a default tablespace. When a user creates a schema objects and specifies no tablespace to contain it, oracle database stores the object in default user tablespace.The default setting for default tablespace of all users is the SYSTEM tablespace. If a user likely to create any type of objects then you should specify and assign the user a default tablespace. Note: Using the tablespace other than SYSTEM reduces contention between data dictionary objects and the user objects for the same data files. Thus it is not advisable for user data to be stored in the SYSTEM tablesapce.SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='EDSS';SQL> Alter user EDSS default tablespace XYZ;SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='EDSS';Once you change the tablespace for a user the previous/existing objects stay the same,I suppose that you never specified a tablespace when you created the objects and let to use the default tablespace from the user, the objects stay stored in the previous tablespace(tablespace A) and new objects will be created in the new default tablespace (tablespace B). Like in the example above, the objects for EDSS stay in the ORAJDA_DB tablespace and any new object will be stored in the ORAJDA_DB1 tablespace.What is Identity Columns Feature in oracle 12c?Before Oracle 12c there was no direct equivalent of the AutoNumberor Identityfunctionality, when needed it will implemented using a combination of sequences and triggers. The oracle 12c database introduces the ability to define an identity clause for a table column defined using a numeric type. Using ALWAYS keyword will force the use of the identity.GENERATED ]AS IDENTITY Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement.Using BY DEFAULT ON NULL allows the identity to be used even when the identity column is referenced and NULL value is specified.How to find Truncated Table user information?If you have already configure the data mining concept with your database then there is nothing to do you can query with v$logmnr_contents view and find the list, otherwise you need to do some more step to configure it first with your database.Why used Materialized view instead of Table?Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.How does Session communicate with the server process?Server processes executes SQL received from user processes.Which SGA memory structure cannot re-size dynamically after instance startup?Log BufferWhich Activity will generate less UNDO data?InsertWhat happens when a user issue a COMMIT?The LGWR flushes the log buffer to the online redo log.When the SMON processes perform ICR?Only at the time of startup after abort shutdown.What is the purpose of synonym in oracle?Synonym permits application to function without modification regardless of which user owns table or view or regardless of which database holds the table or view. It masks the real name and owner of an object and provides location transparency for tables, views or program units of a remote database.CREATE SYNONYM pay_payment_master FOR HRMS.pay_payment_master;CREATE PUBLIC SYNONYM pay_payment_master FOR HRMS.pay_payment_master@sadhan.world;How many memory layers are in the shared pool?The shared pool of SGA having three layers: Library cache which contains parsed sql statement, cursor information, execution plan etc; dictionary cache contains cache user account information, privilege information, datafiles, segments and extent information; buffer for parallel execution messages and control structure.What is the cache hit ratio, what impact does it have on performance?It calculates how often a requested block has been found in the buffer cache without requiring disk space. This ratio is computed using view V$SYSSTAT. The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.select From in 'physical reads');The cache-hit ratio can be calculated as follows: Hit ratio = 1 – (physical reads / (db block gets + consistent gets)) If the cache-hit ratio goes below 90% then:  increase the initialization parameter DB_CACHE_SIZE.Which environment variables are critical to run OUI?ORACLE_BASE; ORACLE_HOME; ORACLE_SIDWhat is Cluster verification utility in RAC env.The cluster verification utility (CVU) is a validation tools that you can use to check all the important component that need to verified at different stage of deployment in a RAC environment.How to identify the voting disk in RAC env. and why it is always in odd number?As we know every node are interconnected with each other and pinging voting disk in cluster to check whether they are alive. If voting disks are in even count then both nodes are survival node and it is created multiple brains in same cluster. If it is odd number in that case only one node ping greater count of voting disk and cluster can be saved from multiple brain syndrome. You can identify voting disk by using the below command line:#crsctl query css votediskWhat are the components of physical database structure? What is the use of control files?Oracle database consists of three main categories of files: one or more datafiles, two or more redo log files; one or more control files.When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.What is difference between database Refreshing and Cloning?DB refreshing means the data in the target environment has been synchronized with a copy of production. This can be done by restoring with a backup of production database where as cloning means that an identical copy of production has been taken and restore to the target environment.When we need to Clone or Refresh the database? There are a couple of scenarios when cloning should be performed: 1. Creating a new environment with the same or different DBNAME. 2. Sometimes we need to apply patches or other major configuration changes thus a copy of environment is needed to test the effect of this change.3. Normally in software development environment before any major development efforts take place, it is always good to re-clone dev, test environments to keep environment sync. The refreshment is needed only when you sure that the environment are already sync and you need to apply only change of latest data.What is OERR utility?The OERR (Oracle Error) utility is provided only with Oracle databases on  UNIX  platforms.  OERR is not an executable, but instead, a shell script that retrieves messages from installed message files. OERR is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files. This utility is very useful as it can extract OS-specific errors that are not in the generic Error Messages and Codes Manual.What do you mean by logfile mirroring?The Process of having copy of redolog file is called mirroring. It is done by creating group by log file together. This ensures that LGWR automatically writes them to all the member of the current online redo log group. In case a group fails the database automatically switch over the next group. It diminishes the performance.What is the use of large pool? Which case you need to use the large pool?You need to set large pool if you are using multi thread server and RMAN backup. It prevents RMAN and MTS server from competing with other subsystem for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled then oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.What will be your first steps if you get the message Application is running slow?Gather the statistics (statspack, AWR) report to find TOP 5 wait event or run a Top command in Linux to see CPU usage. Later run VMSTAT, SAR and PRSTAT command to get more information on CPU and Memory usage and possible blocking.If poor written statements then run EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.How do you add more or subsequent block size specification? Re-create the CONTROLFILE to specify the new BLOCK SIZE for specific data files or Take the database OFFLINE and bring back online with a new BLOCK SIZE specification.

[notsadrobotxyz]

Oracle 12c new features?

Oracle database 12c (c for cloud) a multi tenant database management system introduce so many important new capability in so many areas – database consolidation, query optimization, performance tuning, high availability, partitioning, backup and recovery .Pluggable Databases:In Oracle 12c, in a pluggable database environment, we can create a single database container, and plug multiple databases into this container. All these databases then share the exact same oracle server/background processes and memory, unlike the previous versions where each database has its own background processes and shared memory. This helps in database consolidation and reduces the overhead of managing multiple desperate databases.Consolidation is an important business strategy to reduce the cost of infrastructure and operational expense. In many production database servers, a big portion of CPU cycles go unused. By consolidating many databases into fewer database servers, both the hardware and operational staff can be more effectively utilized.Oracle's new pluggable database feature reduces the risk of consolidation because the DBA can easily plug or unplug an existing database to or from a container database. There is no need to change any code in the application.It is also easy to unplug a database and convert the pluggable database to a traditional database if required. In addition, you can back up and recover pluggable databases independently of the container database; you can also perform a point-in-time recovery of a pluggable database. Further, Resource Manager can be used to control resources consumed by a pluggable database.Optimizer features:Oracle 12c introduces a few useful SQL Optimizer features, and most of these are automatically enabled.It is not uncommon for Optimizer to choose an inefficient execution plan due to incorrect cardinality estimates, invalid statistics, or even stale statistics. This can have dire results. A SQL statement estimated to run for a few seconds might take hours to execute if the chosen execution plan is not optimal.SQL:Identity columns which are auto incremented at the time of insertionSQL> create table emp (emp_id number generated as identity, emp_name varchar);SQL> create table emp (emp_id number generated as identity (start with 1 increment by 1 cache 20 noorder), emp_name varchar;Increased size limit for VARCHAR2, NVARCHAR2, and RAW datatypes to 32K (from 4K).Default on Null (A default value is inserted into the null column).Session private statistics for GTTs (Table and index statistics are held private for each session)UNDO for temporary tables can now managed in TEMP, rather than regular UNDO tablespace.For global temporary tables will not generate UNDO. Reduces contents of regular UNDO allowing better flashback operation.In oracle 12c we are able to make column invisibleSQL> create table ss (column-name column-type invisible); SQL> alter table ss1 modify column-name invisible; SQL> alter table ss1 modify column-name visible;In oracle 12c, No need to shutdown the database for changing Archive log mode.Datapump now allow tuning off redo for the import (only) operation.Now can create duplicate indexes using the same column, in the same order, as an existing index.The truncate command is enhanced with a CASCADE option which allows child record.Oracle 12c allows using DDL inside the SQL statements (PL/SQL inside SQL). Moving and Renaming datafile is now ONLINE, no needs to put datafile in offline.PL/SQL:A role can now be granted to a code unit (PL/SQL Unit Security). Thus one can determine at a very fine grain, which can access a specific unit of code.We can declare PL/SQL functions in the WITH Clause of a select statement.Map Reduce can be run from PL/SQL directly in the database.We can use Booleans values in dynamic PL/SQL. Still no Booleans as database types.ASM:Introduction of Flex ASM, with this feature, database instances uses remote ASM instances. In normal conditions in a node if ASM fails the entire node will be useless, where in 12c the ability to get the extent map from remote ASM instance makes the node useful.Introduction of Flex Cluster, with light weight cluster stack, leaf node and traditional stack hub node (application layer is the typical example of leaf nodes) where they don't require any network heartbeat.Oracle ASM disk scrubbing (Checks for logical data corruptions and repair them automatically.)RMAN:Accidental Drop table, Truncate Table, Wrong scripts human error recovery.RMAN TABLE Point-In-Time Recovery (combination of Data Pump and RMAN, auxiliary instance required).Recover or copy files from Standby databases.Restore & Recover individual tables from RMAN backup.Incremental recovery more faster, many of the tasks removed. You can automate the use of incremental backup to bring the standby db in sync.Import from older export files possibilities.Partitioning:Partitioning enhancements, Multiples partition operations in a single DDL.Online move of a partition (without DBMS_REDEFINTIION).Interval-Ref Partitions - we can create a ref partition (to relate several tables with the same partitions) as a sub-partition to the interval type.Cascade for TRUNCATE and EXCHANGE partition.Asynchronous Global Index maintenance for DROP and TRUNCATE. Command returns instantly, but index cleanup happens later.Patching:Centralized patching - We can test patches on database copies, rolling patches out centrally once testing is complete.Compression: Automated compression with heat map. Optimization can be run on live databases with no disruption. Data optimization will monitor the data usage and with policy archive old data and hot data will be compressed for faster access. Inactive data can be more aggressively compressed or archived, greatly reducing storage costs.Data Guard:1.      Oracle Database 12c introduces a new redo transportation method (Fast Sync redo transport) which omits the acknowledgement to primary of the transaction on the standby.2.      Creating a new type of redo destination (“Far Sync Standby” composed only of the standby control files), the standby redo logs and some disk space for archive logs which shall be sent to the Standby database. Failover & Switchover operations are totally transparent as the "Far Sync Standby" cannot be used as the target.3.      Data Guard Broker commands have been extended. The "validate database" command to checks whether the database is ready for role transition or not.4.      Dataguard Broker now supports cascaded standby.5.      Global Temporary Tables can now be used on an Active Guard standby database.New Views/Packages in Oracle 12c Releasedba_pdbs, v$pdbs, cdb_data_files, dbms_pdb, dbms_qopatchUsing DBUA to upgrade the existing database is the simple and quickest method.For step by step details follow the below link: Upgrade Oracle Database 11g to 12c

[hireindianpvtltd]

Fwd: Urgent requirements of below positions

New Post has been published on https://www.hireindian.in/fwd-urgent-requirements-of-below-positions-34/

Fwd: Urgent requirements of below positions

Please find the Job description below, if you are available and interested, please send us your word copy of your resume with following detail to vendor@vinsysinfo.com or please call me on 703-594-5490 to discuss more about this position.

PL/SQL developer-JDA 2016———->Lakeville, MA .Net developer——->Lakeville, MA Essbase Application Consultant———->Lakeville, MA Sr Ops Engineer Developer with Cassandra——->Bellevue, Bothell, WA

    Job Description Apply Created On

 Position: PL/SQL developer-JDA 2016

Location: Lakeville, MA

Duration: 1 Year +

Mandatory Skills: JDA 2016 suite & Strong PL/SQL

  Job Description:

Mandatory:

JDA 2016 suite & Strong PL/SQL

Good to have:

 Linux shell scripting

Oracle 12c SQL

Perl

 Python

JDA support & Enhancements

5-10 years relevant experience -Band E2

Flexible to take On-call for critical incidents (off-work hours & weekends).

Report and Work independently with customer team

Should have excellent functional and technical skills on JDA.

Good analytical & problem solving skills

Excellent communication skills (both oral and written)

Apply Job 09/01/2019

Position: .Net developer 

Location: Lakeville, MA

Duration: 1 Year +

Mandatory Skills: .Net and C#

  Job Description:

At least 5 years of experience on building enterprise level applications using Microsoft technologies

Design and implement high quality codes using C#, .NET, SQL

Experience using VS 2010/2012 and framework 3.0 and higher

Experience with TFS or visual source safe

Stored procedure writing, performance tuning, Oracle PL/SQL

 Basic database design, table creation, indexes

Agile product development

Apply Job 09/01/2019

Position: Essbase Application Consultant

Location: Lakeville, MA

Duration: 1 Year +

  Job Description:

Upgrade the Planning and Essbase applications and Essbase cubes from 11.1.2.3to 11.1.2.4 in Windows environment on AWS

Experience in setting up Essbase virtual environment and carrying out upgrade

Experience in Integration of LDAP with MSAD

Should have worked on EPMA version and upgrade of Hyperion Financial Reports Migration of users, user group and data from old to new environment

Prepare and provide training to business users and application administrators

Prepare test cases and carry out SIT, Support UAT & deployment in production

Provide knowledge transfer on new environment system, admin configuration/setup documentation

Application support activities (log, track, provide resolution, backup, version control, quality document preparation and maintenance, etc.).

Apply Job 09/01/2019

Job Title: Sr Ops Engineer Developer with Cassandra

Location: Bellevue, Bothell, WA

Duration: 6 MONTHS

Experience: 6-8 Years

Work Authorization: USC/GC/H1/L2/OPT/Any

  Job Description:

The candidate should be flexible to work in both the locations mentioned above:

6-8 years of total IT experience.

Experience in deployment of services in PCF or any Cloud

Experience in Bitbucket, Jenkins

Working experience in PCF or any other cloud

Knowledge on different aspect of production support

Knowledge in logging tools like splunk and AppD will be advantage

Excellent communication and analytical skills

Experience in handling Onsite-Offshore coordination

Apply Job 09/01/2019

  Thanks, Steve Hunt Talent Acquisition Team – North America Vinsys Information Technology Inc SBA 8(a) Certified, MBE/DBE/EDGE Certified Virginia Department of Minority Business Enterprise(SWAM) 703-594-5490 www.vinsysinfo.com

    To unsubscribe from future emails or to update your email preferences click here .

[qoholiccom]

Oracle PL/SQL Developer

Title: Oracle PL/SQL Developer Location: Lake Oswego,OR Type: Contract 6+ months (C2C/W2) Interview Mode: WebEx/Skype/Telephonic Job Description : Job Responsibilities include: You will be responsible for designing and implementing product enhancements, creating and modifying PL/SQL packages, redesign of existing product functions, trouble-shooting and resolving product defects, unit and integration testing. Active interaction with off-shore team inIndia, internal customers, other developers, Quality Assurance, Business System Analysts, Tax Research, Professional Services, and Customer Support counterparts is an integral part of the role. Some of the key tasks you will perform include: - Designing database schema that produce high performance software solutions in service-oriented architectures - Programming and unit testing of code based upon design sessions with internal software users, customer-reported issues, and input from Business System Analysts, Marketing, Professional Services, and Support - Unit and performance testing of design as part of development cycle - Developing and re-factoring existing products, providing product enhancements and resolving product defects to meet customer issues - Providing technical expertise and peer code reviews to other team members - Providing architectural input on new and existing solutions - Writing technical specifications, program documentation, and unit test plans for all work performed - Maintaining solid relationships with cross-functional peers and offshore team - Assisting in Quality Assurance process and customer issue resolution - Partnering with Quality Assurance to drive high-quality, on-time deliverables. Job and work experience requirements: - Bachelors in Computer Science or equivalent work experience - 3+ years? experience with Oracle 11g/12c database design and data architecture experience - Experience with PL/SQL, packages, <> stored procedure development and data modeling - Experience in debugging and understanding complex, unfamiliar PL/SQL code <>- Practical experience with large databases containing tables with billions of rows and terabytes of data - Experience using Oracle packages (dbms, ctx, utl etc.), procedures, views, materialized views,    - Experience working with XML and JSON - Familiarity with third party tools such as TOAD, SQLNavigator or SQLDeveloper - Experience with nested tables, user defined functions, packages and triggers, external and partition tables,bulk processing <> - Experience with ETL processing and patterns, implementations and tools along with writing scripts to load/manipulate data between different databases <> - Should be able to perform ETL processes using PL/SQL with/without third party tools - Database performance tuning and job scheduling <> in oracle <> - Additional experience working with other databases such as MySQL and SQL Server is also desirable - Solid work ethic, self-driven with the ability to work with minimal supervision - Experience using Agile-based methodologies within a software development function - Experience with full life cycle development: requirements gathering, analysis, design, coding, unit testing, and acceptance testing - Strong working knowledge with Object-Oriented Analysis, Design, and Implementatio. Experience in any of the following is considered a plus: - Linux/Unix Bash, Perl or Python script writing is a plus-Non-relational database technologies (NoSQL) - Experience working with Java/J2EE and web technologies is a plus <> - Big Data frameworks and implementations (Hadoop, MongoDB, etc.) is not mandatory but would be a plus - Applying Design Patterns and Enterprise Application Integration Patterns - Prior work experience in a fast-paced, high-energy environment - Performance testing, analysis, and tuning experience. Other specialized knowledge and skills required: - Proven strong analytical design and trouble-shooting skills - Highly accountable for meeting all commitments and deadlines - Effective communication skills, both written and verbal for technical and non-technical audiences - Must be able to work independently as well as collaboratively - Drive for continuous process improvement. -- Reference : Oracle PL/SQL Developer jobs source http://www.qoholic.com/jobs/technology/oracle-plsql-developer_i3491

[qoholicjobs]

Oracle PL/SQL Developer

Title: Oracle PL/SQL Developer Location: Lake Oswego,OR Type: Contract 6+ months (C2C/W2) Interview Mode: WebEx/Skype/Telephonic Job Description : Job Responsibilities include: You will be responsible for designing and implementing product enhancements, creating and modifying PL/SQL packages, redesign of existing product functions, trouble-shooting and resolving product defects, unit and integration testing. Active interaction with off-shore team inIndia, internal customers, other developers, Quality Assurance, Business System Analysts, Tax Research, Professional Services, and Customer Support counterparts is an integral part of the role. Some of the key tasks you will perform include: - Designing database schema that produce high performance software solutions in service-oriented architectures - Programming and unit testing of code based upon design sessions with internal software users, customer-reported issues, and input from Business System Analysts, Marketing, Professional Services, and Support - Unit and performance testing of design as part of development cycle - Developing and re-factoring existing products, providing product enhancements and resolving product defects to meet customer issues - Providing technical expertise and peer code reviews to other team members - Providing architectural input on new and existing solutions - Writing technical specifications, program documentation, and unit test plans for all work performed - Maintaining solid relationships with cross-functional peers and offshore team - Assisting in Quality Assurance process and customer issue resolution - Partnering with Quality Assurance to drive high-quality, on-time deliverables. Job and work experience requirements: - Bachelors in Computer Science or equivalent work experience - 3+ years? experience with Oracle 11g/12c database design and data architecture experience - Experience with PL/SQL, packages, <> stored procedure development and data modeling - Experience in debugging and understanding complex, unfamiliar PL/SQL code <>- Practical experience with large databases containing tables with billions of rows and terabytes of data - Experience using Oracle packages (dbms, ctx, utl etc.), procedures, views, materialized views,    - Experience working with XML and JSON - Familiarity with third party tools such as TOAD, SQLNavigator or SQLDeveloper - Experience with nested tables, user defined functions, packages and triggers, external and partition tables,bulk processing <> - Experience with ETL processing and patterns, implementations and tools along with writing scripts to load/manipulate data between different databases <> - Should be able to perform ETL processes using PL/SQL with/without third party tools - Database performance tuning and job scheduling <> in oracle <> - Additional experience working with other databases such as MySQL and SQL Server is also desirable - Solid work ethic, self-driven with the ability to work with minimal supervision - Experience using Agile-based methodologies within a software development function - Experience with full life cycle development: requirements gathering, analysis, design, coding, unit testing, and acceptance testing - Strong working knowledge with Object-Oriented Analysis, Design, and Implementatio. Experience in any of the following is considered a plus: - Linux/Unix Bash, Perl or Python script writing is a plus-Non-relational database technologies (NoSQL) - Experience working with Java/J2EE and web technologies is a plus <> - Big Data frameworks and implementations (Hadoop, MongoDB, etc.) is not mandatory but would be a plus - Applying Design Patterns and Enterprise Application Integration Patterns - Prior work experience in a fast-paced, high-energy environment - Performance testing, analysis, and tuning experience. Other specialized knowledge and skills required: - Proven strong analytical design and trouble-shooting skills - Highly accountable for meeting all commitments and deadlines - Effective communication skills, both written and verbal for technical and non-technical audiences - Must be able to work independently as well as collaboratively - Drive for continuous process improvement. -- Reference : Oracle PL/SQL Developer jobs source http://www.qoholic.com/jobs/technology/oracle-plsql-developer_i3491

[nox-lathiaen]

Oracle PL/SQL Consultants

Oracle PL/SQL Consultants Location: Carrolton TX/ Lake Oswego, OR Duration: 6-12 Months Contract Job Description: Please verify the authenticity of the profiles and specifically check if they areworking on Oracle 11g/12C in the last project. Job Responsibilities include: You will be responsible for designing and implementing product enhancements, creating and modifying PL/SQL packages, redesign of existing product functions, trouble-shooting and resolving product defects, unit and integration testing. Active interaction with off-shore team in India, internal customers, other developers, Quality Assurance, Business System Analysts, Tax Research, Professional Services, and Customer Support counterparts is an integral part of the role. Some of the key tasks you will perform include: - Designing database schema that produce high performance software solutions in service-oriented architectures - Programming and unit testing of code based upon design sessions with internal software users, customer-reported issues, and input from Business System Analysts, Marketing, Professional Services, and Support - Unit and performance testing of design as part of development cycle - Developing and re-factoring existing products, providing product enhancements and resolving product defects to meet customer issues - Providing technical expertise and peer code reviews to other team members - Providing architectural input on new and existing solutions - Writing technical specifications, program documentation, and unit test plans for all work performed - Maintaining solid relationships with cross-functional peers and offshore team - Assisting in Quality Assurance process and customer issue resolution - Partnering with Quality Assurance to drive high-quality, on-time deliverables. Job and work experience requirements: - Bachelors in Computer Science or equivalent work experience - 3+ years? experience with Oracle 11g/12c database design and data architecture experience - Experience with PL/SQL, packages, stored procedure development and data modeling - Experience in debugging and understanding complex, unfamiliar PL/SQL code - Practical experience with large databases containing tables with billions of rows and terabytes of data - Experience using Oracle packages (dbms, ctx, utl etc.), procedures, views, materialized views,   - Experience working with XML and JSON - Familiarity with third party tools such as TOAD, SQLNavigator or SQLDeveloper - Experience with nested tables, user defined functions, packages and triggers, external and partition tables, bulk processing - Experience with ETL processing and patterns, implementations and tools along with writing scripts to load/manipulate data between different databases - Should be able to perform ETL processes using PL/SQL with/without third party tools - Database performance tuning and job scheduling in oracle - Additional experience working with other databases such as MySQL and SQL Server is also desirable - Demonstrated ability to work as part of a team, as well as independently - Solid work ethic, self-driven with the ability to work with minimal supervision - Experience using Agile-based methodologies within a software development function - Experience with full life cycle development: requirements gathering, analysis, design, coding, unit testing, and acceptance testing - Strong working knowledge with Object-Oriented Analysis, Design, and Implementation Experience in any of the following is considered a plus: - Linux/Unix Bash, Perl or Python script writing is a plus -Non-relational database technologies (NoSQL) - Experience working with Java/J2EE and web technologies is a plus - Big Data frameworks and implementations (Hadoop, MongoDB, etc.) is not mandatory but would be a plus - Applying Design Patterns and Enterprise Application Integration Patterns - Prior work experience in a fast-paced, high-energy environment - Performance testing, analysis, and tuning experience - IntelliJ IDEA IDE -- Reference : Oracle PL/SQL Consultants jobs Source: http://jobrealtime.com/jobs/technology/oracle-plsql-consultants_i2809

[globalmediacampaign]

Best practices for upgrading Amazon RDS for Oracle DB instances from 11.2.0.4 to 19c

Amazon Relational Database Service (Amazon RDS) for Oracle provides newer versions of databases so you can keep your DB instances up-to-date. These versions can include bug fixes, security enhancements, and other improvements. When Amazon RDS for Oracle supports a new version, you can choose how and when to upgrade your DB instances. As you may be already aware, Amazon RDS for Oracle supports two types of upgrades: major version and minor version. In general, a major engine version upgrade can introduce changes that aren’t compatible with existing applications. In contrast, a minor version upgrade in Oracle generally only includes changes that are backward-compatible with existing applications. In other words, a minor version upgrade applies an Oracle Database Patch Set Update (PSU) or Release Update (RU) to a major version. For example, upgrading from 11.2.0.4 to 19c (19.0.0.0) is a major version upgrade, whereas going from 12.2.0.1.ru-2019-04.rur-2019-04.r1 to 12.2.0.1.ru-2019-07.rur-2019-07.r1 is considered a minor version upgrade. Although Amazon RDS for Oracle allows the option to automate some of the patching decisions, such as minor version patching in the form of automatic minor version upgrade, the majority of patching decisions—including the major version upgrades— are up to you. Therefore, it’s critical to be aware of common issues, steps involved, and best practices to upgrade with the least impact on your business. In this post, we a look at the 11.2.0.4 End of Support timeline in Amazon RDS for Oracle, study the version upgrade choices available to you, and dive deep into the best practices to follow during the upgrade process. This post is relevant to database administrators running their DB instances on Amazon RDS for Oracle. Most pre- and post-upgrade steps in this post are general guidelines from Oracle support and upgrade documentation that are relevant to Amazon RDS for Oracle. This post is written considering the various workloads on Amazon RDS for Oracle that could be impacted by 11.2.0.4 DB upgrade. Not all guidelines mentioned here are relevant to all customers. We strongly recommend that you use your judgment to see which one is suitable for your database based on your options and parameters used. Prior knowledge of Oracle database administration and the environment should be used for performing the upgrade. Amazon RDS for Oracle: 11.2.0.4 version End of Support timeline Oracle has announced the end date of support for Oracle Database version 11.2.0.4 as December 31, 2020, after which Oracle Support will no longer release Critical Patch Updates for this database version. Amazon RDS for Oracle will end support for Oracle Database version 11.2.0.4 Standard Edition 1 (SE1) for License Included (LI) model on October 31, 2020. For the Bring Your Own License (BYOL) model, Amazon RDS for Oracle will end the support for Oracle Database version 11.2.0.4 for all editions on December 31, 2020. All 11.2.0.4 SE1 LI instances will be automatically upgraded to 19c on November 1, 2020. Likewise, the 11.2.0.4 BYOL instances will be automatically upgraded to 19c on January 1, 2021. We highly recommend you upgrade your existing Amazon RDS for Oracle 11.2.0.4 DB instances and validate your applications before the automatic upgrades begin. The following table summarizes the timeline for Oracle 11.2.0.4 with SE1 with LI. Dates Activity Now – October 31, 2020 You can upgrade 11.2.0.4 DB instances manually to the version of your choice August 1, 2020 You can upgrade 11.2.0.4 snapshots manually to the version of your choice August 1, 2020 Amazon RDS for Oracle disables new instance creates on 11.2.0.4 for LI November 1, 2020 Amazon RDS for Oracle starts automatic upgrades of DB instances to 19c November 1, 2020 Amazon RDS for Oracle will start automatic upgrades to version 19c for any DB instances restored from snapshots The following table summarizes the timeline for Oracle 11.2.0.4 on SE, SE1, and EE with BYOL. Dates Activity Now – December 31, 2020 You can upgrade 11.2.0.4 DB instances manually to the version of your choice October 1, 2020 You can upgrade 11.2.0.4 snapshots manually to the version of your choice October 1, 2020 Amazon RDS for Oracle disables new instance creates on 11.2.0.4 for BYOL January 1, 2021 Amazon RDS for Oracle starts automatic upgrades of DB instances to 19c January 1, 2021 Amazon RDS for Oracle starts automatic upgrades of DB instances restored from snapshots to 19c For more information about the EOS of the 11.2.0.4 version by Amazon RDS for Oracle, see Announcement: Amazon RDS for Oracle – End of Support Timeline for 12.2.0.1 and 11.2.0.4 Major Versions. Version upgrade choices in Amazon RDS for Oracle Amazon RDS for Oracle supports the following major version upgrade paths (as of this writing). Current Version Supported Upgrade Path 18.0.0.0 19.0.0.0 12.2.0.1 19.0.0.0 ,18.0.0.0 12.1.0.2 19.0.0.0 ,18.0.0.0, 12.2.0.1 11.2.0.4 19.0.0.0,18.0.0.0,12.2.0.1, 12.1.0.2.v5 and higher 12.1 versions As part of deciding which version to upgrade to, you have the following options: 12.1.0.2 –Extended Support for Oracle Database 12.1.0.2 (Terminal Patch Set) ends on July 31, 2022. Amazon RDS for Oracle plans to support Oracle Database 12.1.0.2 until July 31, 2022. 12.2.0.1 –Limited Error Correction Support for Oracle Database 12.2.0.1 ends on March 31, 2022. Amazon RDS for Oracle plans to support Oracle Database 12.2.0.1 until March 31, 2022. 18c – Support for Oracle Database 18c ends on June 8, 2021. Amazon RDS for Oracle plans to support Oracle Database 18c until June 8, 2021. (There is no Extended Support for Oracle Database 18c.) 19c – Premier Support for Oracle Database 19c ends on April 30, 2024, and the Extended Support ends on April 30, 2027. Amazon RDS for Oracle plans to support Oracle Database 19c until April 30, 2027. We recommend you upgrade your 11.2.0.4 DB instances to 19c because it’s the long-term support release. When upgrading any software, checking for compatibility of the new version and its features plays a crucial role in the upgrade’s overall success. Oracle Database versions and releases can have differences in how they work and interact with applications, which may result in compatibility issues. Although the way you interact with Amazon RDS for Oracle remains the same from the major version upgrade perspective, Oracle Database specific features across major versions may change or even become obsolete. For more information about major version upgrades, see Oracle Database Engine Release Notes. Keep in mind the following: Major version downgrades aren’t supported. A major version upgrade from 11g to 12c must upgrade to an Oracle Patch Set Update (PSU) released in the same month or later. For example, a major version upgrade from Oracle version 11.2.0.4.v14 to Oracle version 12.1.0.2.v11 is supported. However, a major version upgrade from Oracle version 11.2.0.4.v14 to Oracle version 12.1.0.2.v9 isn’t supported. This is because Oracle version 11.2.0.4.v14 was released in October 2017, and Oracle version 12.1.0.2.v9 was released in July 2017. For information about the release date for each Oracle PSU, see Oracle Database Engine Release Notes. Downtime considerations for a major version upgrade Amazon RDS lets you manually initiate a major version upgrade by modifying the DB instance—either via the AWS Management Console, AWS Command Line Interface (AWS CLI), or Amazon RDS API. This is an in-place upgrade and requires downtime for your applications during the upgrade process. In the event of any issues with the upgrade phase, you can restore the latest backup. The duration of the outage varies based on your engine version and the DB instance class. You can determine the exact amount of time it takes by exercising a test upgrade using a snapshot restore of the production database in a pre-production environment. If performing the major version upgrade using the modify DB instance method isn’t desirable for your application, an alternative approach is using logical bi-directional replication with either AWS Database Migration Service (AWS DMS) or Oracle GoldenGate. This method can provide the least downtime for the upgrade. This method involves setting up logical replication between the source and target DB instances (both running on the same version). You then upgrade the target DB instance to 19c, while leaving the source to run on 11.2.0.4. When the upgrade of the target DB instance is complete, you can point your application to the upgraded target DB instance. This method, which uses bi-directional replication between the source and target DB instances, can also be used as a fallback plan, should the upgrade break due to incompatibility. This post covers the upgrade process using the modify DB instance method. The logical replication method of upgrading is out of scope of this post. How Amazon RDS for Oracle performs a major version upgrade When a major version upgrade is invoked on the console, AWS CLI, or Amazon RDS API, the automation completes the following steps: Takes a pre-upgrade snapshot (if configured for backups). You can use this snapshot to roll back to if needed. Shuts down the instance and prepares it for the upgrade. Runs Oracle upgrade scripts. Takes a post-upgrade snapshot. When Amazon RDS initiates Step 1, the instance’s status changes from Available to Upgrading. After Step 4, it returns to Available. The following diagram illustrates the high-level steps when the modify-db-instance AWS CLI call is invoked. Best practices for major version upgrades In this section, we dive deep into the recommended best practices during various phases of the upgrade process: pre-upgrade, upgrade, and post-upgrade. What we discuss in the following sections are the common steps typically completed in most Oracle Database upgrades. For a complete list, see the Oracle Database Upgrade Guide. Pre-upgrade phase Oracle uses the following terminology when upgrading to a higher release (or major version upgrade in Amazon RDS terminology): Deprecated features – Features that are no longer being enhanced, but are still supported for the full life of this release of Oracle Database. Desupported features – Those that are no longer supported by fixing bugs related to that feature. Oracle can choose to remove the code required to use the feature. Where indicated, a deprecated feature can be desupported in a future major release. When moving across multiple major releases during the upgrade process, it’s highly recommended to consult Oracle documentation for deprecated and desupported features, options, and parameters in the intermediary releases as well. For more information, see Behavior Changes, Deprecated and Desupported Features for Oracle Database. You should consider the following factors in the pre-upgrade phase. Downtime considerations A typical upgrade with all possible options in the option group might take 1–2 hours. To reduce downtime, consider the following: Take a manual snapshot using the create-db-snapshot AWS CLI prior to starting the upgrade phase. This speeds up the time taken for a pre-upgrade snapshot (which is automatically invoked during the upgrade phase). If you’re upgrading to 19c, it’s recommended to convert all DBMS_JOB to DBMS_SCHEDULER before the upgrade. During the upgrade, Oracle tries to convert the DBMS_JOB to DBMS_SCHEDULER. If you have a large number of DBMS_JOB entries, the upgrade takes longer. Make sure the audit trails aren’t very large. Pre-upgrade checks and upgrades can take longer with large audit trails. Gather optimizer statistics before the upgrade using DBMS_STATS.gather_dictionary_stats. Also gather fixed and dictionary stats. Remove options that aren’t used. It’s a good practice to remove unused options to speed up the upgrades and result in fewer issues and conflicts when moving from one major version to another. Remove or reset parameters that aren’t used for the same preceding reasons. If necessary, you might also have to upgrade the instance class based on the target instance choice. For more information, see DB Instance Class Support for Oracle. Multi-AZ considerations If your DB instance is in a Multi-AZ deployment, Amazon RDS for Oracle upgrades both the primary and Multi-AZ standby simultaneously. Option groups considerations Consider the following for option groups: By default, when one engine is upgraded to the next higher major version, Amazon RDS for Oracle chooses the default option group unless the new corresponding custom option group for the target version isn’t chosen. For example, when upgrading from 11.2.0.4 to 19c, you should create a new option group that’s compatible with the new version 19c and matches closest to the 11.2.0.4 options, and use it during upgrade process. You should also consider factors such as APEX upgrade, which is recommended to be handled as a separate preparatory process, to speed up the upgrade. This also applies to OEM agents. In some cases, options are uninstalled and reinstalled as you move from one version to another. For example, SQLT is freshly installed, which deletes any old metadata stored by the option. When choosing the version of the OEM agent, consider the compatibility with the OMS. Note the option group and the VPC. If a DB instance is in a VPC, the option group associated with the instance is linked to that VPC. This means that you can’t use the option group assigned to a DB instance if you try to restore the instance to a different VPC or a different platform (for example, when you use the OEM option). When you upgrade a major version, understand the changes Oracle makes to the options in that change. For example, Oracle has desupported the Multimedia option in 19c. However, it separated the MDSYS functionality into a separate option called Locator. Oracle also recommends you move from Locator to Spatial and Graph prior to the upgrade. All MDSYS functionality is available with Spatial and Graph. So, when you upgrade to 19c and install Spatial and Graph, all stored procedures work fine. For more information, see “My Oracle support” article 2347372.1 for more details. The PL/SQL package DBMS_XMLQUERY is deprecated in Oracle Database 18c. Oracle recommends using DBMS_XMLGEN instead. Parameter groups considerations If you associate a new parameter group with a DB instance, reboot the database after the upgrade completes. If you need to reboot the instance to apply the parameter group changes, the instance’s parameter group status shows pending-reboot. You can view an instance’s parameter group status on the console or by using a describe command, such as describe-db-instances. The continuous_mine functionality of the LogMiner package DBMS_LOGMNR.START_LOGMNR is obsolete. It was deprecated in Oracle Database 12c Release 2 (12.2). There is no replacement functionality. Oracle didn’t provide any alternative to this. If you use this, you need to address it in a different way, such as AWS DMS or Oracle GoldenGate. Security considerations The following are some important security considerations: By default, Oracle accounts that have not had their passwords reset before upgrade (that are set to EXPIRED status), and that are also set to LOCKED status, are set to NO AUTHENTICATION after the upgrade is complete. Therefore, post-upgrade, any account with a password set to default, locked, and expired loses the authentication method. Although it could be reverted back, it’s recommended to validate the password for its strength before the upgrade and lock it. Check the accounts that use the case-insensitive password version. Log in to SQL*Plus as an administrative user, and enter the following SQL query. If there are any 10g versions, you should refer to the Oracle documentation to fix 10g versions, or user accounts with LOCKED after the upgrade is complete: SELECT USERNAME, PASSWORD_VERSIONS FROM DBA_USERS; Make sure that you don’t have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Starting with Oracle 12c, Oracle uses Oracle Real application Security (ORAS) to store Oracle security policies. Before you upgrade an 11g database to Oracle Database 19c, you must delete any data security roles defined in 11g. After the upgrade, you may have to define the data security roles again. If it’s upgraded without taking this action, any data security policy that includes a data security role becomes invalid in the new 19c database. General considerations Finally, you should consider the following general points: When upgrading to 19c, we recommend you look at the provisioned capacity and see if it would be met. Depending on the instance class your 11g database is running on, you may need to scale the compute to a newer generation of the instance class. For more information, see DB Instance Class Support for Oracle. This could also be an opportunity to evaluate Amazon RDS reserved instances and you may want to purchase new leases for your version before performing the upgrade. Make sure that no objects are invalid before upgrading. It’s a good practice to keep a list of all objects with their respective count, type, and the status prior to the upgrade and compare it after the upgrade. Take a backup of optimizer statistics for all application schemas using the DBMS_STATS package. Collect pre-upgrade AWR or Statspack snapshots prior to the upgrade. These are used during the post-upgrade performance validation. Even though you would have done this in the pre-production environment, it’s a good practice to do this comparison in the production. If you also have a DB maintenance task, such as adding partitions, it’s better to run them before upgrading. Disable scheduled database custom jobs or cron jobs. It’s recommended to disable any custom DDL triggers before upgrade and re-enable them after upgrade. If you’re using materialized views (MV), check the status of all MVs prior to the upgrade. Check the size of the MV logs. If it’s non-zero, address them to make sure they are all in sync. It’s recommended to wait until all MVs have completed refreshing. Make sure that any objects referencing remote databases across database links are accessible to reduce time spent on network timeouts. For more information, see “My Oracle support” Doc ID 1406586.1. For License Included customers, please create a support case for this document. Keep a list of clients and its drivers used along with the version numbers. Identify the corresponding version that works with 19c. Review all hidden parameters and make sure they’re modified or removed to meet the target version. Include all features affected by these parameters as part of the functional and performance testing in the pre-production environment. Upgrade phase After completing the pre-upgrade checks successfully, you can move on to the upgrade phase. If your instance has been using custom option group or parameter group configurations, you must specify new option and parameter groups for the upgrade to go through along with any other additional attributes. When you upgrade your 11.2.0.4 SE1 DB instance to 19c, by default it takes you to SE2 on 19c. In this section, we discuss how to perform the upgrade on the console or via the AWS CLI. To upgrade the engine version of a DB instance on the console, complete the following steps: On the Amazon RDS console, choose Databases. Choose the DB instance that you want to upgrade. Choose Modify. The Modify DB Instance page appears. For License model, choose the desired license type. For DB engine version, choose the new version. Choose Continue and check the summary of modifications. To apply the changes immediately, choose Apply immediately. Choosing this option can cause an outage in some cases. For more information, see Using the Apply Immediately Setting. Otherwise, you can have the upgrade take place in your next weekly maintenance window. On the confirmation page, review your changes and choose Modify DB Instance to save your changes. To perform a major version upgrade in your next maintenance window using the AWS CLI modify-db-instance command, enter the following code: aws rds modify-db-instance --db-instance-identifier mydbinstance --engine-version new_version --allow-major-version-upgrade --no-apply-immediately For information about valid engine versions, use the AWS CLI describe-db-engine-versions command. If you have multiple instances, you could use the AWS CLI combined with a shell script to upgrade multiple instances. For more information about upgrading the database engine for Amazon RDS for Oracle, see Upgrading the Oracle DB Engine. Post-upgrade phase After the upgrade phase, you can complete the following checks and post-upgrade steps: Recompile the INVALID As mentioned in the pre-upgrade phase section, checking for objects with INVALID status and fixing them is required before testing further. This is also a good time to compare the report/list (of object count with status) gathered during the pre-upgrade phase. If Amazon RDS for Oracle is used as an RMAN catalog, upgrade the RMAN recovery catalog. Use the UPGRADE CATALOG command to upgrade the RMAN recovery catalog schema from an older version to the version required by the RMAN client. Complete the following client connectivity steps: A) Make sure to set the ORACLE_HOME, PATH, LD_LIBRARY_PATH, LIBPATH, and more to 19.x in the client home. B) Make appropriate changes to the tnsnames.ora and sqlnet.ora on the client side and incorporate changes related to the 19c or the new endpoint if testing on a new RDS instance restored from the production. C) Test the client connectivity. Based on the identified target version of the driver, perform a regression test. Keep in mind there are cases where the choice of JDBC version might impact performance and change the process optimizer plan. If you used the DBSM_STATS package to export the optimizer stats as mentioned in the pre-upgrade tasks, upgrade the stats table (using DBMS_STATS.UPGRADE_STAT_TABLE) where the optimizer statistics are backed up prior to the upgrade. Then restore statistics and run performance testing. If you have database links to other Amazon RDS for Oracle DB instances or other Oracle instances on Amazon Elastic Compute Cloud (Amazon EC2), it’s recommended to test them from both a functionality and performance standpoint after the upgrade. If you have any external scheduler jobs scheduled in Amazon CloudWatch or cron, ensure those are enabled and can still connect to the database after upgrade and run with no issues. Also make sure to enable any DDL or system triggers disabled as part of the pre-upgrade check. Make sure to have enough free space in SYSTEM and SYSAUX table space. Having an additional 1–2 GB of space in each tablespace is recommended. Validate your applications against the upgraded database. It’s vital that you look at the top SQL statements (either via AWR or Statspack) and verify that they are using the desired plans. After the upgrade, if the SQL statements perform poorly due to the change in the plans by the 19c optimizer, you can use the OPTIMIZER_FEATURES_ENABLE This parameter is alterable at the session level and system level. For example, if you upgrade your database from release 11.2 to release 19c, but you want to keep the release 11.2 optimizer behavior, you can do so by setting this parameter to 11.2.0.4. At a later time, you can try the enhancements introduced in releases up to and including release 19c by setting the parameter to 19.0.0. If you have an automated process of backup and restore, disaster recovery, or production to non-production refresh procedures, we strongly recommend you test them. You need to test any process that interacts with other databases that may or may not be at the same level. Common issues We highly recommend you upgrade the manual snapshots you intend to retain longer, including the snapshots taken using AWS Backup or any third-party partner products. For example, if you used AWS Backup and the backup was migrated to Glacier based on the lifecycle defined in AWS Backup, when the backup is restored, the first thing the RDS does is force upgrade it to one of the supported versions and then complete the restore. If you want to keep it as a copy of the old version (for example, if the database is supporting an old version of a third-party application) you should plan on making a backup using native Oracle tools such as export data pump or RMAN. Also keep the version incompatibilities in mind. After upgrading the database, if you run into connectivity issues such as "ORA-28040: No matching authentication protocol", you might have to upgrade the client version or the JDBC driver. In general, it’s best to match the client and server versions. The best client version for the database version 19c would be client 19c. However, the 11.2.0.4 clients are also supported on 19c. We highly recommend testing for this combination. Please refer to “My Oracle support” document 207303.1. Testing the upgrade of your Amazon RDS for Oracle DB instance Before upgrading the production database to a new major version, you must validate your applications against the new version in a pre-production environment. This rehearsal of the upgrade process also allows you to gauge the time it takes to perform this major version upgrade. The following diagram illustrates the steps to test your upgrade. The process includes the following steps: Take a snapshot of the existing DB instance using the create-db-snapshot AWS CLI call. Restore the DB snapshot using the restore-db-instance-from-db-snapshot AWS CLI call to create a test DB instance on the same version. Run the modify-db-instance AWS CLI call on the test DB instance to upgrade it to the new major version. When the upgrade is complete, you can validate your application against the test DB instance. After the testing is complete, you can schedule and perform the production database upgrade. Additional considerations Amazon RDS for Oracle supports the read replicas feature for the versions 12.1.0.2 and above. If you upgrade your database from 11.2.0.4 to a higher version, you may want to evaluate the read replicas capability and determine if your application requires cross-Region disaster recovery and read scalability. For more information, see Managed disaster recovery and managed reader farm with Amazon RDS for Oracle using Oracle Active Data Guard. As part of upgrading to 19c, you can also use options such as OLAP and Oracle Label security. Conclusion In this post, we reviewed the 11.2.0.4 End of Support timeline in Amazon RDS for Oracle, studied the version upgrade choices available to you, and covered the best practices during the phases of the upgrade process. If you need additional assistance in planning and upgrading your Amazon RDS for Oracle DB instances running on 11.2.0.4, you can reach out to the following resources: AWS Professional Services AWS Partners Partner NAMER LATAM EMEA APAC Contact Apps Associates X X X X bill.saltys@appsassociates.com House of Brick X X X X blindquist@houseofbrick.com Navisite X X ryan.banks@clckwrk.com NorthBay X jim.keller@northbaysolutions.com Wipro X X X X manish.govil1@wipro.com For more information about the upgrade, see the following: Upgrading the Oracle DB Engine Oracle SE2 Upgrade Paths Deprecation of Oracle 11.2.0.4   About the Authors   Sundar Raghavan is a Senior Specialist Solutions Architect at Amazon Web Services.         Srinagesh Battula is a Principal Product Manager at Amazon Web Services       https://aws.amazon.com/blogs/database/best-practices-for-upgrading-amazon-rds-for-oracle-db-instances-from-11-2-0-4-to-19c/

[isearchgoood]

December 20, 2019 at 10:00PM - The Complete SQL Certification Bundle (98% discount) Ashraf

The Complete SQL Certification Bundle (98% discount) Hurry Offer Only Last For HoursSometime. Don't ever forget to share this post on Your Social media to be the first to tell your firends. This is not a fake stuff its real.

In today’s data-driven world, companies need database administrators and analysts; and this course is the perfect starting point if you’re keen on becoming one. Designed to help you ace the Oracle 12c OCP 1Z0-062: Installation and Administration exam, this course walks you through Oracle database concepts and tools, internal memory structures and critical storage files. You’ll explore constraints and triggers, background processes and internal memory structures and ultimately emerge with a set of valuable skills to help you stand out during the job hunt.

Access 92 lectures & 19 hours of content 24/7

Understand data definition language & its manipulation

Walk through database concepts & tools, memory structure, tables, and indexes

Dive into undertaking installation, backup & recovery

This course will prepare you for the Microsoft Certification Exam 70-765, which is one of two tests you must pass to earn a Microsoft Certified Solutions Associate (MCSA): 2016 Database Administration certification. If you’re looking to start a lucrative IT career installing and maintaining Microsoft SQL Server and Azure environments, this course is for you.

Access 102 lectures & 22.5 hours of content 24/7

Deploy a Microsoft Azure SQL database

Plan for a SQL Server installation

Deploy SQL Server instances

Deploy SQL Server databases to Azure Virtual Machines

Configure secure access to Microsoft Azure SQL databases

If you’re interested in working in Database Administration, Database Development, or Business Intelligence, you’d be remiss to skip this course. Dive in, and you’ll foster the technical skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012.

Access 196 lectures & 6.5 hours of content 24/7

Apply built-in scalar functions & ranking functions

Combine datasets, design T-SQL stored procedures, optimize queries, & more

Implement aggregate queries, handle errors, generate sequences, & explore data types

Modify data by using INSERT, UPDATE, DELETE, & MERGE statements

Create database objects, tables, views, & more

This course is aimed towards aspiring database professionals who would like to install, maintain, and configure database systems as their primary job function. You’ll get up to speed with the SQL Server 2012 product features and tools related to maintaining a database while becoming more efficient at securing and backing up databases.

Access 128 lectures & 6.5 hours of content 24/7

Audit SQL Server instances, back up databases, deploy a SQL Server, & more

Configure additional SQL Server components

Install SQL Server & explore related services

Understand how to manage & configure SQL Servers & implement a migration strategy

This course is designed for aspiring Extract Transform Load (ETL) and Data Warehouse Developers who would like to focus on hands-on work creating business intelligence solutions. It will prepare you to sit and pass the Microsoft 70-463 certification exam.

Access 100 lectures & 4.5 hours of content 24/7

Master data using Master Data Services & cleans it using Data Quality Services

Explore ad-hoc data manipulations & transformations

Manage, configure, & deploy SQL Server Integration Services (SSIS) packages

Design & implement dimensions, fact tables, control flow, & more

This course is all about showing you how to plan and implement enterprise database infrastructure solutions using SQL Server 2012 and other Microsoft technologies. As you make your way through over 5 hours of content, you’ll explore consolidation of SQL Server workloads, working with both on-site and cloud-based solutions, and disaster recovery solutions.

Access 186 lectures & 5.5 hours of content 24/7

Explore managing multiple servers

Dive into disaster recovery planning

Discover the rules of database design

Learn how to design a database structure & database objects

This course is designed for aspiring business intelligence developers who would like to create BI solutions with Microsoft SQL Server 2012, including implementing multi-dimensional data models, OLAP cubs, and creating information displays used in business decision making.

Work w/ large data sets across multiple database systems

Develop cubes & Multi-dimensional Expressions (MDX) queries to support analysts

Explore data model decisions

Manage a reporting system, use report builder to create reports & develop complex SQL queries for reports

This course is designed for aspiring business intelligence architects who would like to focus on the overall design of a BI infrastructure, including how it relates to other data systems.

Access 153 lectures & 4.5 hours of content 24/7

Learn BI Solution architecture & design

Understand provisioning of software & hardware

Explore Online Analytical Processing (OLAP) cube design

Study performance planning, scalability planning, upgrade management, & more

Maintain server health, design a security strategy, & more

This course is designed for aspiring database developers who would like to build and implement databases across an organization. Database developers create database files, create data types and tables, plan indexes, implement data integrity, and much more. All of which you’ll learn how to do in this Microsoft 70-464 course.

Access 140 lectures & 5 hours of content 24/7

Optimize & tune queries

Create & alter indexes & stored procedures

Maintain database integrity & optimize indexing strategies

Design, implement, & troubleshoot security

Work w/ XML Data

Write automation scripts

Explore the benefits of an Oracle database that is re-engineered for cloud computing in this course! The Oracle Multitenant architecture delivers next-level hardware and software efficiencies, performance and manageability benefits, and fast and efficient cloud provisioning. Jump into this course, and you’ll explore the essentials for passing the Oracle 12c OCP 1Z0-061: SQL Fundamentals exam.

Access 71 lectures & 15.5 hours of content 24/7

Familiarize yourself w/ data control language

Understand SQL functions & subqueries

Walk through combining queries & data definition language

Prep to ace the Oracle 12c OCP 1Z0-061: SQL Fundamentals exam

The Microsoft SQL Server environment is one of the most preferred data management systems for companies in many different industries. Certified administrators are handsomely paid. This course will prepare you for the Microsoft Certification Exam 70-764 which validates that you are able to administer a Microsoft SQL Server 2016 server.

Access 105 lectures & 30.5 hours of content 24/7

Learn how to administer a Microsoft SQL Server 2016 server

Configure data access, permissions & auditing

Perform encryption on server data

Develop a backup strategy

Restore databases & manage database integrity

from Active Sales – SharewareOnSale https://ift.tt/2Yfi64s https://ift.tt/eA8V8J via Blogger https://ift.tt/392S5KT #blogger #bloggingtips #bloggerlife #bloggersgetsocial #ontheblog #writersofinstagram #writingprompt #instapoetry #writerscommunity #writersofig #writersblock #writerlife #writtenword #instawriters #spilledink #wordgasm #creativewriting #poetsofinstagram #blackoutpoetry #poetsofig