DBA Blogs

How to Manage Oracle 12c MultiTenant Database

VitalSoftTech - Tue, 2019-05-21 00:01
Managing Oracle 12c MultiTenant Database Oracle has drastically modified its architecture by introducing the concept of containers. The main component of this architecture is the CDB container. It is a container which owns the memory structure, the background processes and SYSTEM, SYSAUX tablespaces which are all shareable with other databases which are “plugged” into it. […]
Categories: DBA Blogs

Oracle Certification 12c Notes – DBA Track

VitalSoftTech - Mon, 2019-05-20 10:58
Oracle 12c OCP Exam Notes (1z0-060) Oracle DBA 12c Certification Notes Oracle 12c – Manage Multitenant CDB and PDBs with EM Express Manage Multitenant CDB and PDBs with EM Express. Quick and easy steps to create, setup and deploy EM Express. Oracle 12c Database: Create 12c CDB, PDB Databases Using OUI When installing Oracle Software, […]
Categories: DBA Blogs

Loading email content into oracle table

Tom Kyte - Fri, 2019-05-17 21:46
Hi Tom, I have an interesting requirement, I want to load complete emails ( example outlook) in oracle tables. => When a mail content ( preview) is greater than 4000 than store in attachement table with name "long content" and store complete...
Categories: DBA Blogs

Ora-12560: TNS: protocol adapter error

Tom Kyte - Fri, 2019-05-17 21:46
I use a single instance 12.2C 64-bit Oracle database on a window server 2012R2. suddenly this error <i>ORA-12560: TNS:protocol adapter error</i> began to show when I try to enter the sqlplus. whatever I have searched the internet a lot for a soluti...
Categories: DBA Blogs

Manipulate the autogenerated names for types inside packages

Tom Kyte - Fri, 2019-05-17 21:46
Hey, if you create a type inside a package, this type is created in the database with a name like 'SYS_...'. Is there any possibility to affect/influence the auto generated name? Or do i can rename it? And how? Why I asked that? I work a lot w...
Categories: DBA Blogs

exporting packages,function etc. from one user to another.

Tom Kyte - Fri, 2019-05-17 21:46
Hi, For example X user have many packages,functions,procedures etc. And I want to delete some of them after copying to another user (Y). I mean I want to classify packages,functions etc... I can copy-paste by using Procedure Builder. But this way...
Categories: DBA Blogs

SYSDATE behavior in SQL and PL/SQL

Tom Kyte - Fri, 2019-05-17 21:46
Hello, My quess: there are two different SYSDATE functions ? one defined in STANDARD package and another one somewhere ?inside? Oracle. Example: SQL> select * from dual; D - X SQL> select sysdate from user_objects where rownum=1;...
Categories: DBA Blogs

ORA-15067: command or option incompatible with diskgroup redundancy

VitalSoftTech - Wed, 2019-05-15 00:07
When dropping an online disk from a diskgroup why is ORA-15067 error returned?
Categories: DBA Blogs

Partner Webcast – Oracle Visual Builder Cloud Service: Web and Mobile App Development for all

Oracle Visual Builder focuses on simplifying development by providing a visual approach to application development and publishing. As a visual and declarative cloud environment it allows developing...

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

PLS-00436: implementation restriction error when using forall in update

Tom Kyte - Fri, 2019-05-10 19:26
I have a object as follows: create or replace type claims_only as object (fordnr varchar2(15),glaeubigernr number,fordergnr number); and I have a a collection as: create or replace type claims_only_collection as table of claims_only; I have a...
Categories: DBA Blogs

Partner Webcast – Developing Internet of Things Applications with Oracle IoT Cloud Service

Oracle Internet of Things (IoT) Cloud Service is a managed Platform as a Service (PaaS) cloud-based offering that helps you make critical business decisions and strategies by allowing you to connect...

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

Tried Live SQL

Bobby Durrett's DBA Blog - Thu, 2019-05-09 18:13

Quick post. I tried https://livesql.oracle.com/ for the first time.

Looks like you can try out the latest version of Oracle for free. You can type in SQL statements in your web browser.

There seem to be a boatload of scripts and tutorials to try out. Might be good for someone who is new to SQL.

Bobby

Categories: DBA Blogs

Ace Your Excel Test to Get Your Dream Job

VitalSoftTech - Thu, 2019-05-09 10:29
You aced your first interview and you couldn’t feel better about it – you answered all the questions correctly, made a great impression, and managed to have a professional yet very friendly conversation with someone you might be working with soon. That is when you found out that the next step of the recruitment process […]
Categories: DBA Blogs

Digital Marketing and Its Benefits

VitalSoftTech - Wed, 2019-05-08 10:12
Does your business make use of digital marketing services? Today, a lot of businesses utilize the online world to take their business to a higher level. They engage in digital marketing to share what they can offer. If you do, that’s really good! Now, all you need to do is to grow it or improve […]
Categories: DBA Blogs

Pester script parameter passing not working

Matt Penny - Wed, 2019-05-08 04:22
Problem

I was trying to parameterize a Pester script. The script looked like this:


param (
[string]$ComputerName,
[string]$IPAddress
)

write-dbg "$ComputerName: "
write-dbg "
$IPAddress: "

Describe "$ComputerName is visible" {

It "It is ping-able" {
    {test-connection $ComputerName -count 1} | Should Not Throw

    $(test-connection $ComputerName -count 1 | Measure-Object).count | Should Be 1
}

}

…but passing the parameters wasn’t working.

Solution

The problem was that I was calling the script as follows
$ Invoke-Pester @{PAth = c:\pester\diagnostics\simple\StandardDomainContoller.tests.ps1; Parameters=@{ComputerName = "server1.here.co.uk";IPAddress = "17.6.5.1""}}

…and the Path variable needs quotes:

$ Invoke-Pester @{PAth = 'c:\pester\diagnostics\simple\StandardDomainContoller.tests.ps1'; Parameters=@{ComputerName = "server1.here.co.uk";IPAddress = "17.6.5.1""}}

Categories: DBA Blogs

Partitioning -- 16 : Hybrid Partitioning

Hemant K Chitale - Tue, 2019-05-07 02:55
Oracle 19c introduces Hybrid Partitioning whereby you can have external and internal Partitions co-existing.  External Partitions are on storage (filesystem) outside the database.

Let's say we have a List Partitioned table for the widgets that we manufacture. The table is Partitioned by WIDGET_CLASS_ID, based on an ISO standard.  So all companies that manufacture widgets adopt the same WIDGET_CLASS_ID:

SQL> desc widgets_list
Name Null? Type
----------------------------------------- -------- ----------------------------
WIDGET_CLASS_ID VARCHAR2(5)
WIDGET_ID VARCHAR2(32)
WIDGET_NAME VARCHAR2(32)
WIDGET_DESCRIPTION VARCHAR2(128)

SQL>
SQL> l
1 select table_name, partitioning_type, partition_count
2 from user_part_tables
3* where table_name = 'WIDGETS_LIST'
SQL> /

TABLE_NAME PARTITION PARTITION_COUNT
-------------------------------- --------- ---------------
WIDGETS_LIST LIST 3

SQL>
SQL> l
1 select partition_name,high_value, num_rows
2 from user_tab_partitions
3* where table_name = 'WIDGETS_LIST'
SQL> /

PARTITION_NAME HIGH_VALUE NUM_ROWS
---------------- ---------------- ----------
P_A 'A' 1520
P_B 'B' 520
P_C 'C' 119

SQL>


Later, another widget manufacturer that manufactures widgets of CLASS_ID 'X' is acquired.  The WIDGETS_LIST table is in a non-Oracle database and is received as a CSV file.  We accept the CSV file onto a filesystem location :

sh-4.2$ pwd
/home/oracle/ACQUIRED_COMPANY
sh-4.2$ cat AC_Widgets_List.CSV
'X','ABCXX2','The1','cddfdaxx'
'X','XXD2','The2','dda3'
'X','XRC34','The3','ff33355312'
sh-4.2$


So, we have a CSV file "AC_Widgets_List.CSV" listing the widgets manufactured by this company. We want to add it to our WIDGETS_LIST table.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> create directory acquired_company as '/home/oracle/ACQUIRED_COMPANY';

Directory created.

SQL> grant read, write on directory acquired_company to hemant;

Grant succeeded.

SQL>
SQL> connect hemant/hemant@ORCLPDB1
Connected.
SQL>
SQL> l
1 alter table widgets_list
2 add external partition attributes (
3 type oracle_loader
4 default directory acquired_company
5 access parameters (
6 fields terminated by ','
7 (widget_class_id, widget_id, widget_name, widget_description)
8 )
9* )
SQL> /

Table altered.

SQL>
SQL> l
1 alter table widgets_list
2 add partition P_ACQ_CO values ('X')
3* external location ('AC_Widgets_List.CSV')
SQL> /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','WIDGETS_LIST');

PL/SQL procedure successfully completed.

SQL>
SQL> l
1 select partition_name, high_value, num_rows
2 from user_tab_partitions
3 where table_name = 'WIDGETS_LIST'
4* order by partition_position
SQL> /

PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------------------- ---------------- ----------
P_A 'A' 1520
P_B 'B' 520
P_C 'C' 119
P_ACQ_CO 'X' 3

SQL>
SQL> l
1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS WIDGET_ID WIDGET_NAME
------------ -------------------------------- --------------------------------
WIDGET_DESCRIPTION
--------------------------------------------------------------------------------
'X' 'ABCXX2' 'The1'
'cddfdaxx'

'X' 'XXD2' 'The2'
'dda3'

'X' 'XRC34' 'The3'
'ff33355312'


SQL>


The rows in the "AC_Widgets_List.CSV" file are now visible as rows in a *Partition* in our Oracle Table WIDGETS_LIST.
Of course, these being external, cannot be modified by INSERT/UPDATE/DELETE DML.

The External Attribute Type that I used is ORACLE_LOADER to use the SQL Loader libraries on a filesystem file.  Oracle 19c also supports ORACLE_DATAPUMP, ORACLE_HDFS and ORACLE_HIVE to reference files stored in other types of storage.

Hybrid Partitions are supported with single-level Range and List partitioning methods.  ALTER TABLE to ADD, DROP and RENAME Partitions is supported.

An External Partition can be Exchanged with an External Non-Partitioned Table only.
.
.
UPDATE :  Later, if I update the CSV file (using an external editor) to remove the quotation mark :

sh-4.2$ cat AC_Widgets_List.CSV
X,ABCXX2,The1,cddfdaxx
X,XXD2,The2,dda3
X,XRC34,The3,ff33355312
sh-4.2$

SQL> l
1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS_ID WIDGET_ID WIDGET_NAME
---------------- -------------------------------- --------------------------------
WIDGET_DESCRIPTION
------------------------------------------------------------------------------------
X ABCXX2 The1
cddfdaxx

X XXD2 The2
dda3

X XRC34 The3
ff33355312


SQL>


So, it is possible to edit the External Partition using other methods (here I used "vi" on Linux)
.
.
.

Categories: DBA Blogs

Update statement with outer join

Tom Kyte - Sat, 2019-05-04 08:06
Hi, From the sql script, I would like to understand the difference in the behavior between executions of statement 8 and statement 11. While statement 8 updates the flag_1 to null, statement 11 updates it to 2. What difference it makes when I u...
Categories: DBA Blogs

Pass parameter to where clause in bulk collect statement

Tom Kyte - Sat, 2019-05-04 08:06
Hi I have a basic procedure which bulk collects the results of a select statement into a table array. I then print out one line to show that it has worked.. Code (SQL): <code>CREATE OR REPLACE PROCEDURE use_var IS TYPE r_tab IS TABLE OF msf010%...
Categories: DBA Blogs

CDB Fleet in Oracle Database 18c

Oracle in Action - Fri, 2019-05-03 03:21

RSS content

Oracle database 18c  introduces a new CDB Fleet feature  which allows many CDBs to be managed as one. A CDB fleet is a collection of CDBs and hosted PDBs that you can monitor and manage as one logical CDB from a centralized location.

There are two possible roles within a CDB Fleet:

  • Lead CDB: Only one CDB in the Fleet may be designated as the Lead CDB. The lead CDBis the central location for monitoring and managing all the CDBs in the fleet.
  • Member CDB: The CDBs registered with a lead CDB are called member CDBs. There can be one or more member CDB’s in a CDB fleet.

For every  member CDB,  proxy PDBs for the member CDB and its PDB(s) are automatically created in the lead CDB,.  Consequently, all the member CDBs  and their PDBs are now “visible” in the lead CDB . This enables management and monitoring of the entire estate of PDBs in the fleet, physically distributed across various CDBs, from the lead CDB.

Advantages

Reporting, monitoring, and management of the entire CDB fleet through a single interface:

  • Provides massive scalability of the underlying infrastructure
  • Reduces capital and operational costs
  • Provides greater efficiencies to the business.

Related Links:

‘STUB’ Status In DBA_PDBS

References:

https://docs.oracle.com/cd/E96517_01/newft/database-new-features-guide.pdf

https://docs.oracle.com/en/database/oracle/oracle-database/18/multi/administering-cdb-fleet.html#GUID-5951E81B-4351-4FA4-9F7B-52D2FEB0428D



Tags:  

Del.icio.us
Digg

Copyright © ORACLE IN ACTION [CDB Fleet in Oracle Database 18c], All Right Reserved. 2019.

The post CDB Fleet in Oracle Database 18c appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs