DBA Blogs

Need some suggestion on Special characters.

Tom Kyte - Thu, 2018-03-08 22:26
Hi Tom, I'm using Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production. Need some suggestion regarding how to handle special characters. <b>My doubt with converting any special characters to normal character except belo...
Categories: DBA Blogs

It doesnt Work. VARCHAR2(4000 CHAR)

Tom Kyte - Thu, 2018-03-08 22:26
Hi, I have five columns as below in my DB col1 VARCHAR2(4000 CHAR) col2 VARCHAR2(4000 CHAR) col3 VARCHAR2(4000 CHAR) col4 VARCHAR2(4000 CHAR) col5 VARCHAR2(4000 CHAR) When i try to save the data with max length, i get the following error. <...
Categories: DBA Blogs

Making APEX_WEB_SERVICE.make_rest_request work with HTTPS

Tom Kyte - Thu, 2018-03-08 22:26
I have created a standard template database via DBCA in Oracle 12c R2 and then installed the APEX code via the apex installation directory that was installed with the Oracle 12c R2 binaries. I have also upgraded APEX to 5.0.4 I have created a s...
Categories: DBA Blogs

ODP.NET - System.Exception: 'UnmarshalColumnData: Unimplemented type' when calling nested cursor

Tom Kyte - Thu, 2018-03-08 22:26
Hello! I've faced with strange behaviour while calling procedure over ODP.NET with nested cursors inside. 1. I've created simple Oracle function under example schema: <code>CREATE OR REPLACE FUNCTION TEST_CUR RETURN SYS_REFCURSOR IS O...
Categories: DBA Blogs

Does an automated cloud data warehouse need partitioning and indexes?

Bobby Durrett's DBA Blog - Thu, 2018-03-08 16:33

This is a late post inspired by my time at RMOUG Training Days 2018. I attended two RMOUG Training Days presentations about automated cloud data warehouse database systems. The first was about Snowflake. The second was about Oracle’s coming Autonomous Data Warehouse Cloud offering. Both products seem to have the goal of automating as much as possible in a data warehouse database and neither seem to allow users to create indexes or to partition their data in the way they choose.

This raises the obvious question – do you need the ability to create indexes and define your own partitioning scheme in a data warehouse database? I have seen many situations on data warehouse databases where both are helpful. When we went to Exadata there was a push to drop all of our indexes but we did not. Also, we used partitioning to improve query performance. If we had to get rid of both features what would we have done on Exadata?

I don’t know the answer, but people say that we need more automation and less control. So, you could spin up a cloud based data warehouse database quickly and start using it. But how many knobs are left to turn when performance is slow? Do users need indexes and partitioning methods to get acceptable performance? Really, what is wrong with giving users the ability to create indexes and to partition tables in the way they want?

Time will tell whether index and partition free cloud based data warehouse database systems make it. But, for now I’m left to wonder what we are losing without these features.

Bobby

 

Categories: DBA Blogs

Changing Security Role Password in Oracle GoldenGate 12.3 Microservices

DBASolved - Thu, 2018-03-08 12:02

One of the nice things about Oracle GoldenGate 12.3 Microserivces, is the flexibility we now have to interact with Oracle GoldenGate. Additionally, what comes with Oracle GoldenGate 12.3 Microservices is a new role based security frame work.

When you first setup Oracle GoldenGate 12.3 Microservices, you setup your ServiceManager and initial deployment. Upon setting this up, you will assign a new administrator to the frame work. This is a different user than the Oracle GoldenGate user within the database. This user is the user assigned to the security role (highest role). This user is the equivalent of the SYS user in the Oracle Database and has access to everything within the security framework of Oracle GoldenGate 12.3 Microservices. It should be a no-brainer to keep the password for this user secure and to as few people as possible.

Now, when you look at the user from inside of the Administration Server (AdminServer); notice there is not an update button under the Action category (Figure 1).

Figure 1:

So how do we update this password for a user in the security role, much less any other role? Remember, when I said Oracle GoldenGate 12.3 Microservices was “flexible”? We have to use the RESTful API to update the password.

The exact end-point that you have to use is: /services/v2/authorizations/{role}/{user}. More info can be found here.

The way that I update the password is by using a JSON document and a simple cURL command. Below is a simple JSON document example:

{
“credential”:”welcome1″
}

Then the cURL command I use is:

curl -u oggadmin:********* -H “Content-Type:application/json” -H “Accept:application/json” -X PATCH http://localhost:16001/services/v2/authorizations/security/oggadmin -d @update_security_password.json| python -mjson.tool

After running the cURL command, the password for the oggadmin user is updated. You should see some similiar output to this output.

{
“$schema”: “api:standardResponse”,
“links”: [
{
“href”: “http://localhost:16001/services/v2/authorizations/security/oggadmin&#8221;,
“mediaType”: “application/json”,
“rel”: “canonical”
},
{
“href”: “http://localhost:16001/services/v2/authorizations/security/oggadmin&#8221;,
“mediaType”: “application/json”,
“rel”: “self”
}
],
“messages”: [],
“response”: {
“links”: [
{
“href”: “http://localhost:16001/services/v2/authorizations/security/oggadmin&#8221;,
“mediaType”: “application/json”,
“rel”: “self”
}
]
}
}

Hope this helps you understand how to change a user password from REST within the Oracle GoldenGate 12.3 Microservices Architecture.

Enjoy!!!

Categories: DBA Blogs

MD5 Function with Large string having characters more than 4000

Tom Kyte - Thu, 2018-03-08 04:06
I am looking for a function to MD5 values for concatenated columns in the table. I plan to use this function on before insert/update trigger to store this values as one of the column (md5_hash) in the same table. The intention is to use this column f...
Categories: DBA Blogs

srvctl add service on standalone

Tom Kyte - Thu, 2018-03-08 04:06
Hi, I am working on 12cR1 version installed on my windows 8.1 laptop. Trying to learn EBR and so want to create additional services so that I can connect to two different editions if required. Using below command, but getting error. >srvctl a...
Categories: DBA Blogs

Oracale procedure execution is giving an error

Tom Kyte - Thu, 2018-03-08 04:06
I have a stored procedure in oracle. Whenever i try to execute it its throwing me an error mentioned below: Error starting at line 5 in command: exec usp_eventmgmt_get_events(TO_DATE('2018-07-18', 'YYYY-MM-DD'),'District',1) *Error report: ORA-06550:...
Categories: DBA Blogs

Mimicking Sql Server OUTPUT Clause

Tom Kyte - Thu, 2018-03-08 04:06
I am trying converting SQL Server T-SQL queries to Oracle based queries. In SQL Server there is an ability to use the OUTPUT Clause within a query. See example below. BEGIN TRANSACTION DELETE TableA OUTPUT "DELETED".* INTO "TESTARCHIVE"....
Categories: DBA Blogs

Line numbers in error messages do not match source lines

Tom Kyte - Thu, 2018-03-08 04:06
When running a PL/SQL script through sqlplus, eventual error messages contain a line number that does not match the source lines. For example : <code> ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 144 ORA-06512: at li...
Categories: DBA Blogs

Rman backup

Tom Kyte - Thu, 2018-03-08 04:06
I have a database - about 6.2Terabytes in size - has about 525G freespace. There are basically three tablespaces (other than the normal. FO_tables has about 50 tables. One of the tables there has two blob columns - each in it's own tablesapce. One...
Categories: DBA Blogs

view compiled with errors

Tom Kyte - Thu, 2018-03-08 04:06
I am trying to create a view and i am getting message as "<i>Error report: SQL Command: force view VIEW_REPORTEVENTS Failed: Warning: execution completed with warning"</i> <code>CREATE OR REPLACE FORCE VIEW VIEW_REPORTEVENTS AS SELEC...
Categories: DBA Blogs

Training on Demand: Oracle WebLogic Server 12c Implementation Specialists Boot Camp

Oracle WebLogic Server 12c Implementation Specialist Boot Camp is a technical enablement program aimed at partner technical consultants who will be implementing Oracle WebLogic Server 12c solutions....

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

Oracle Chatbots MOOC runs again!

Oracle Chatbots MOOC is back due to popular demand. This MOOC has already run previously, but if you didn't have time to finish or didn't get a chance to participate previously,...

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

Trivadis Performance Days 2018

Richard Foote - Wed, 2018-03-07 19:06
I’m very excited and privileged to be asked to present the opening and closing sessions at this year’s Trivadis Performance Training Days in Zurich, Switzerland on 19-20 September 2018. It’s one of the premier Oracle training events in Europe, one which I’ve always wanted to attend. This year has a fantastic lineup, so I’m really […]
Categories: DBA Blogs

Find the last time for consecutive rows by status

Tom Kyte - Wed, 2018-03-07 09:46
Hello, I need some help with an Oracle Sql Query. For each day i must calculate the time difference between each cycle IN & OUT and then sum the difference as total worked minutes. The main problem that i have it's that the input data is variabl...
Categories: DBA Blogs

How to exclude a list of tables in DBMS_STATS.GATHER_SCHEMA_STATS ?

Tom Kyte - Wed, 2018-03-07 09:46
Hello, Is there any way to exclude a list of tables while doing DBMS_STATS.GATHER_SCHEMA_STATS? DBMS_STATS.LOCK_TABLE_STATS doesn't answer my need exactly. Best regards, Amine
Categories: DBA Blogs

adding space between a number and a letter in a record

Tom Kyte - Wed, 2018-03-07 09:46
Hi Tom, I want to add space between number and a letter in a column. For eg: somestreet 22a --> Somestreet 22 A oldstreet 5d --> Oldstreet 5 D othernewtreet 134B --> Othernewstreet 134 B Similarly, for all suc...
Categories: DBA Blogs

regarding table partition

Tom Kyte - Wed, 2018-03-07 09:46
hi Team, We have set initrans value for partition table to 255 in our exadata box, this table have very high concurrency , after which we noticed huge archive generation , here can help us how this inittrans value is related with archive genera...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs