tag:blogger.com,1999:blog-89551022833749364012024-03-13T21:05:02.021-07:00SQL> /* Prompts */Databases related links and more ...SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.comBlogger92125tag:blogger.com,1999:blog-8955102283374936401.post-48919819780814482562017-05-07T20:32:00.000-07:002017-05-07T20:32:01.705-07:00ORA-00600: internal error code, arguments: [kziaVrfyAcctStatInRootCbk: !user], [APEX_PUBLIC_USER] after noncdb_to_pdb.sqlI found a bug last week after the creation of a PDB from NON-CDB in an empty 12.2 CDB with the noncdb_to_pdb.sql script :
After a successful conversion from NON-CDB to PDB, here is the error when trying to connect in the PDB as APEX_PUBLIC_USER :
In the PDB, I looked at the APEX users more closely and found that APEX_PUBLIC_USER and APEX_050000 were defined as common users. Even if they SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-19559203429699046282016-02-04T09:35:00.001-08:002016-02-04T09:35:25.299-08:00Repartitioning a root persistent disk on Linux Red Hat 7 (Google Cloud)
You can take this procedure from the Google Cloud Platform documentation: Repartitioning a root persistent disk
Or you can take this shell script ... https://raw.githubusercontent.com/DarkAngelStrike/GoogleCloud/master/fdisk.sh
Here is an example on a new Red Hat 7 instance ...
SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-28847171664379364262016-01-20T07:47:00.001-08:002016-01-20T07:47:23.134-08:00Modifying firewall rule of Google Cloud VM with command line tools.
Here's how to modify and add port 1522 to an already defined firewall rule named "allow-tns" of a Google Cloud VM.
And the links to gcloud commands ...
gcloud compute firewall-rules list
gcloud compute firewall-rules update
SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-20259693063318059662016-01-19T11:05:00.002-08:002016-01-19T11:53:59.232-08:00Adding Oracle ASM disks to Google Cloud VM with command line tools.This is what the web portal looks like and if you haven't tried the Google Cloud Platform yet you really should do so.
Google Cloud Platform
Here is some links to the docs of the gcloud commands used in this post ...
gcloud compute instances describe
gcloud compute instances list
gcloud compute disks describe
gcloud compute disks create
gcloud compute instances attach-disk
gcloud SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-41287838533646745382015-11-10T13:28:00.001-08:002015-11-10T13:33:20.549-08:00dbaascli - CLI for different tools to be used with Oracle Cloud DBSYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-18479106784645209302015-11-10T09:40:00.001-08:002015-11-10T09:40:51.490-08:00Enable EPEL Repository - Oracle Database Cloud ServiceSYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-39746705130809038622015-11-03T08:22:00.000-08:002015-11-03T08:22:33.961-08:00PSU bundle patch: Installed in the CDB but not in the PDB.SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-23741903419459901602015-10-30T09:43:00.001-07:002015-10-30T09:51:17.020-07:00Adding Oracle ASM disk to Amazon EC2 with command line tools.SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-7182487022795476292015-10-13T10:56:00.001-07:002015-10-13T10:59:37.634-07:00DBMS_METADATA generating incorrect syntax for the CREATE TABLE statements with ROW ARCHIVAL enabled (12.1.0.2)While experiencing with the In-Database Archiving feature in 12.1, I stumble upon this unpublished bug with DBMS_METADATA.GET_DDL.
The clause "ILM ENABLE LIFECYCLE MANAGEMENT" was returned instead of "ROW ARCHIVAL" which causing an ORA-00933 at creation time. And here is what I found on My Oracle Support about this issue who is also impacting datapump imports in 12c:
CAUSE
The cause of this SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-87513649854639956922013-10-29T08:23:00.001-07:002013-10-29T08:57:18.147-07:00Move datafile like a "lying child" - Oracle 12cI tried to move a PDB datafile (PDB1) with the fullpath name from the CDB$ROOT container.
And the database is acting like a "lying child" responding it doesn't know file #13 ...
As you can see, you need to be in the right container to make it work.
SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-65325121347151443752013-10-17T09:40:00.002-07:002013-10-17T09:40:39.763-07:00Gallery: Oracle Linux 6.4 + Oracle Database 12c on Microsoft AzureSYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-69698716811964048892013-10-17T08:55:00.001-07:002013-10-17T11:42:09.357-07:00Gallery: Oracle Multitenant Self-Service Provisioning - Oracle 12c
SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-26809810177282772242013-10-16T11:30:00.003-07:002013-10-16T13:29:49.167-07:00ORA-27369: Oracle Multitenant Self-Service Provisioning - Oracle 12c
I tried to drop a PDB with the Oracle Multitenant Self-service Provisioning Tool. I received this error ...
Don't look too far because this error is not really what it seams ...
To solve this issue, you only need to update the password of the OS User. A script called "update_osuser_creds.sql" is provided in the "utilities" folder of the installation package.
SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-82988276866551709302013-07-24T11:14:00.000-07:002013-07-24T11:14:16.849-07:00Add the current container name to SQL*Plus prompt - Oracle 12cI think it could be useful to know which container you are currently using. So I added this in my "glogin.sql" file :
So with that I can always know in which container I'm in ...
And if I want to change the container with "ALTER SESSION SET CONTAINER", I simply use this simple script called "altcon.sql":
SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-23487425900162200912013-07-11T13:49:00.001-07:002014-10-21T09:14:47.178-07:00Online statistics gathering with CTAS and IAS - Oracle 12c New featureI found this little gem in the Oracle 12c documentation the other day ...
A little while after, found some more info on this new "Online Statistics Gathering" ...
Online Statistics Gathering for Bulk Loads - Oracle® Database SQL Tuning Guide 12c Release 1 (12.1)
Optimizer with Oracle 12c - Online statistics gathering (p.19)
And here is my demo ...
Create Table As Select (CTAS)
SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-43424853843808050972013-07-02T12:10:00.000-07:002013-07-02T12:12:33.075-07:00WITH_PLSQL hint - Oracle 12c New featureThis weekend I read about the new WITH_PLSQL "hint" on several sites. I looked in the official documentation and I saw this:
So I decided to give it a try on every type of DML (INSERT, UPDATE, DELETE and MERGE).
Don't forget to put the WITH_PLSQL "hint" to avoid getting an "ORA-32034: unsupported use of WITH clause":
Ref: http://www.oracle-base.com/articles/12c/SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-48733647419509085282013-06-28T08:48:00.000-07:002014-10-21T09:11:04.189-07:00Move datafiles online - Oracle 12c New featureDo you remember the time when you had to move a datafile ...
In Oracle 12c you can now move online datafiles with one simple statement: ALTER DATABASE MOVE DATAFILE
And even if there is transactions using these datafiles ...
Session #1
Session #2
Where's what we can see in the alert.log ...
You can even move SYSTEM, SYSAUX and UNDO datafiles with this statement ... so let's move allSYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-18149797165773446482013-06-27T11:23:00.000-07:002013-06-27T11:23:06.466-07:00PLSQL in SQL - Oracle 12c New featureI wanted to test what I saw in a presentation named "12 Things About The Latest Generation of Database Technology" by Thomas Kyte. I took the same queries from the demo and executed them against an Oracle 12cR1 Database (12.1.0.1.0) on Oracle Linux 6.3 x86_64.
Here is the new syntax with PLSQL "embedded" in a SQL query ...
Here is the creation of a stage table ... (ctas+stats+function) ...
SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-39090986355412586592013-06-12T11:04:00.000-07:002013-06-13T13:24:19.152-07:00How many uppercase and lowercase characters in a column.Figure out how many uppercase, how many lowercase characters are in a column, we are also interested in how many are neither uppercase or lowercase. For bonus points, you can add 3 more columns to the output, one column will hold only uppercase characters, one column will hold only lowercase characters, one column will hold only characters that are neither uppercase or lowercase.
The MrClean SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-91807548043469149572013-04-25T11:18:00.000-07:002013-04-25T11:47:17.323-07:00Simple technics of privilege escalation (11.2.0.3)
"In this article I will show some basic technics of escalating privileges in Oracle 11.2.0.3 Database. My goal in each case is to gain DBA privilege from *ANY* privileges."
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- CASE 1 - EXECUTE ANY and CREATE ANY PROCEDURE
--~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HR@ORCL> SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-80810982441143004152013-04-17T07:27:00.000-07:002013-04-17T07:28:10.995-07:0011gR2 Flashback Guaranteed Restore Point without enabling Flashback loggingFirst of all let’s turn OFF Archive log mode and ensure NO flashback logging.
SQL> startup mount
ORACLE instance started.
Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 398461624 bytes
Database Buffers 50331648 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL> alter system archive log stop;
System altered.
SQL> alter database noarchivelog;
Database SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-16761205524498043832013-02-20T10:28:00.001-08:002013-02-20T13:29:21.769-08:00Oracle Database 11gR2 with Amazon EC2 (ami-3d739c54)From: https://aws.amazon.com/amis/oracle-database-11g-release-2-11-2-0-1-enterprise-edition-64-bit
This EBS backed AMI comes with Oracle Enterprise Linux Release 5 Update 4 and Oracle Database 11g Release 2 (11.2.0.1) Enterprise Edition software pre-installed and configured on the 64 Bit platform. In a matter of minutes, you can have a fully configured Oracle Database computing environment SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-52638000672984616492012-11-22T10:19:00.002-08:002012-11-22T10:58:33.413-08:00Add loopback devices OEL6 (ASM)Add 2 loopback devices using "mknod","chmod" and "chown" commands ...
# ls -l /dev/loop*
# mknod /dev/loop8 b 7 8
# mknod /dev/loop9 b 7 9
# chmod 660 /dev/loop8
# chmod 660 /dev/loop9
# chown root:disk /dev/loop8
# chown root:disk /dev/loop9
# ls -l /dev/loop*
Add 10 loopback devices using "MAKEDEV" command ...
# ls -l /dev/loop*
# MAKEDEV -v /dev/loop8
# ls -l /dev/loop*
And finaly SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-48359605673911475772012-10-15T10:52:00.000-07:002012-10-15T11:10:08.231-07:00Sql Dashboard For RAC/Exadata - V2It tells you info about Exadata smart scan/storage index saving/Flash cache/ CPU usage/TOP sqls/waits/Cluster interconnect traffic/Top segments by GC* waits and a lot more info. which can be useful to monitor system performance. Based on current formatting, It can support up to 8 node cluster information easily.
I tried my best to make this utility lightweight by reading data from gv$ views once SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0tag:blogger.com,1999:blog-8955102283374936401.post-29671197944778303002012-10-15T07:19:00.000-07:002012-10-15T07:19:11.553-07:00“Database In-Memory Machine”Everyone is talking about In Memory at the moment. On blogs, in tweets, in the press, in the Oracle marketing department, in books by SAP employees, even my Violin colleagues… it’s everywhere. What can I possibly add that will be of any value?
http://flashdba.com/2012/10/09/in-memory-databases-part1/
In the first part of this blog series on In Memory Databases (IMDBs) I talked about the SYSDBAhttp://www.blogger.com/profile/04171778675369505586noreply@blogger.com0