DBA Blogs

View SYS permissions

Tom Kyte - Thu, 2019-05-02 21:46
A vendor would like me to run a script against a client's database. The script requires 'SYS' user access and makes the following grants: <code>grant select on sys.gv_\$mystat to public;</code> <code>grant select on sys.gv_\$vsession to public;</...
Categories: DBA Blogs

Ways to Name the Output Columns of a Pipelined Table Function

Tom Kyte - Thu, 2019-05-02 21:46
How many ways are there to name the output columns of a pipelined table function? My understanding is the only way is via a RECORD type like this: <code>CREATE PACKAGE blah_blah_blah IS TYPE abc_rec IS RECORD (ID NUMBER, ...
Categories: DBA Blogs

Sequence behavior skipping values

Tom Kyte - Thu, 2019-05-02 21:46
Hello, Ask Tom Team. I have a table in a 2-node RAC with an identity column. The sequence is generated by default, cache 3000 and noorder option. I see something weird. The first insert was id 1, which means that it came from instance 1, then...
Categories: DBA Blogs

Unable to complete finish_redef_table on reference partitioned table

Tom Kyte - Thu, 2019-05-02 21:46
Hi , Could you please have a look at below reference partition scenario Rowcount of order_items is 25 millions: I need to change the partition technique of child table i.r. order_items to DAILY-RANGE INTERVAL partition. Table dont have primary k...
Categories: DBA Blogs

Search on concatenated index values

Tom Kyte - Thu, 2019-05-02 21:46
I am looking into an issue with concatenated index (also known as multi-column, composite or combined index). So, by the current design, I have 2 columns KEY1 and KEY2, which produce my PRIMARY KEY PK1. <code> CREATE TABLE "myTable" ( "...
Categories: DBA Blogs

Error while cloning remote database using dblink: ORA-65345: cannot refresh pluggable database

Tom Kyte - Thu, 2019-05-02 21:46
<code>create pluggable database test3 from test32@ttxtest_link1 parallel 8 refresh mode none create_file_dest ='/u02/app/oracle/oradata/test3/' * ERROR at line 1: ORA-65345: cannot refresh pluggable database ORA-17627: ORA-03135: connection lost ...
Categories: DBA Blogs

DML Operation in PL/SQL Function

Tom Kyte - Thu, 2019-05-02 21:46
Sir, 1) Can I perform DML (Insert, Update, Delete) also into a PL/SQL function.....? 2) Can I call a Trigger Explicitly.
Categories: DBA Blogs

Migrating Oracle database

Tom Kyte - Thu, 2019-05-02 21:46
Hello, Thanks for taking up this question. There is an enterprise java application hosted on a huge (40 TB) Oracle database. Can this Oracle database be migrated to : 1. SQL Server or any other RDBMS? 2. Any No SQL database? I have been as...
Categories: DBA Blogs

When-Tree-Node-Selected question.

Tom Kyte - Thu, 2019-05-02 21:46
I've created a tree and it is populating correctly during the when-new-form-instance trigger, but my question is that when I click the mouse on a tree node, the when-tree-node-selection trigger fires properly and the node is highlighted. But wh...
Categories: DBA Blogs

Partner Webcast – Oracle Mobile Hub Simplifies Enterprise Mobile

Mobile is everywhere and touches every facet of our lives. Oracle Mobile Hub provides an open, comprehensive platform that simplifies development for mobile app developers, backend service...

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

Installed 19.3 on Linux on VirtualBox

Bobby Durrett's DBA Blog - Thu, 2019-05-02 15:14

I noticed that Oracle database 19.3 was available to download so I installed it on a Linux VM under VirtualBox.

I cloned my base Oracle Linux 7 VM and did a yum -y update to get it up to the latest Linux version.

I installed the preinstall RPM:

yum install oracle-database-preinstall-19c

Created the Oracle home, set some variables, and disabled the firewall.

mkdir -p /home/oracle/product/db/19.0.0
chgrp oinstall /home/oracle/product/db/19.0.0
cd /home/oracle/product/db/19.0.0

export ORACLE_BASE=/home/oracle/product
export ORACLE_HOME=/home/oracle/product/db/19.0.0

[root@ora19 ~]# systemctl stop firewalld
[root@ora19 ~]# systemctl disable firewalld

Edited my .bashrc

export ORACLE_BASE=/home/oracle/product
export ORACLE_HOME=/home/oracle/product/db/19.0.0
export ORAENV_ASK=NO
export ORACLE_SID=orcl
. oraenv

Unzipped the downloaded file LINUX.X64_193000_db_home.zip in my ORACLE_HOME. Then I ran runInstaller from the ORACLE_HOME through MobaXterm. For some reason this decided to put the install window overlapping my two screens so I couldn’t tell what I was typing, and it didn’t allow me to move it. Fun times.

It seemed to hang on the last step, but it eventually finished. The process named ora_mz00_orcl was spinning on the CPU for part of the time that it seemed hung. I guess it was 5 or 10 minutes, but I didn’t time it.

Seems to be up and working:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 2 13:00:59 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Thu May 02 2019 12:51:54 -07:00

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

SQL>

Bobby

Categories: DBA Blogs

The Power of HDFS ACLS

Alejandro Vargas - Wed, 2019-05-01 02:12

Kerberos and Sentry help us defining access privileges for a limited set of object living on HDFS

We can extend the control by using access control lists, ACL's 

Requesting the ACL's on a directory will show us if "dfs.namenode.acls.enabled" is set to tru on HDFS:

[root@linux-x64]# hdfs dfs -getfacl /user
# file: /user
# owner: hdfs
# group: supergroup

getfacl: The ACL operation has been rejected.  Support for ACLs has been disabled by setting dfs.namenode.acls.enabled to false.

In this case as it is set to false we need to enable it, for this we go to Cloudera Manager > HDFS > Configuration and search for "dfs.namenode.acls.enabled" and we mark it as enabled.

We need to restart the stale services to enable ACL's

First I'll create a new user for testing

[root@sf_D_DRIVE]# adduser avargas

[root@media]# grep avargas /etc/passwd avargas:x:502:504::/home/avargas:/bin/bash Then I will create a user hdfs a directory inside HDFS for user avargas [root@media]# sudo su -hdfs -bash-4.1$ hdfs dfs -mkdir /user/avargas -bash-4.1$ hdfs dfs -ls /user Found 9 items drwxr-xr-x   - hdfs     supergroup          0 2019-04-29 12:42 /user/avargas drwxr-xr-x   - cloudera cloudera            0 2019-01-24 08:45 /user/cloudera drwxr-xr-x   - mapred   hadoop              0 2018-12-24 23:39 /user/history drwxrwxrwx   - hive     supergroup          0 2017-10-23 09:17 /user/hive drwxrwxrwx   - hue      supergroup          0 2018-12-19 07:44 /user/hue drwxrwxrwx   - oozie    supergroup          0 2017-10-23 09:16 /user/oozie drwxrwxrwx   - root     supergroup          0 2017-10-23 09:16 /user/root drwxr-xr-x   - hdfs     supergroup          0 2017-10-23 09:17 /user/spark As the owner user hdfs I'll assign rwx permissions on the directory /user/avargas to user avargas sh-4.1$ hdfs dfs -setfacl -m user:avargas:rwx /user/avargas -bash-4.1$ hdfs dfs -getfacl /user/avargas # file: /user/avargas # owner: hdfs # group: supergroup user::rwx user:avargas:rwx group::r-x mask::rwx other::r-x Now I'll upload a file as user avargas root@sf_D_DRIVE]# su - avargas [avargas@~]$ hdfs dfs -ls /user > filelist.txt [avargas@~]$ hdfs dfs -put filelist.txt /user/avargas File /user/avargas/filelist is owned by user avargas:supergroup [avargas@~]$ hdfs dfs -ls /user/avargas Found 1 items -rw-r--r--   1 avargas supergroup        680 2019-04-29 13:26 /user/avargas/filelist.txt Directory /user/avargas still owned by hdfs:supergroup, but note the + sign indicating additional permissions assigned on it: [avargas@~]$ hdfs dfs -ls /user | grep avargas drwxrwxr-x+  - hdfs     supergroup          0 2019-04-29 13:26 /user/avargas We can also set ACL’s for several users and groups on a single command

-bash-4.1$ hdfs dfs -setfacl -m user:avargas:rwx,user:hadoop:rwx,group::rwx,other::rwx /user/avargas

-bash-4.1$ hdfs dfs -getfacl /user/avargas
# file: /user/avargas
# owner: hdfs
# group: supergroup
user::rwx
user:avargas:rwx
user:hadoop:rwx
group::rwx
mask::rwx
other::rwx

Note that permissions were not changed for a file

-bash-4.1$ hdfs dfs -ls /user/avargas
Found 1 items
-rw-r--r--   1 avargas supergroup        680 2019-04-29 13:26 /user/avargas/filelist.txt

We can set ACL’s for several users and groups and recursively on all folders and files using the -R flag

-bash-4.1$ hdfs dfs -setfacl -m -R user:avargas:rwx,user:hadoop:rwx,group::rwx,other::rwx /user/avargas

-bash-4.1$ hdfs dfs -ls /user/avargas
Found 1 items
-rw-rwxrwx+  1 avargas supergroup        680 2019-04-29 13:26 /user/avargas/filelist.txt

Categories: DBA Blogs

Rebuilding local non prefix index raises ORA 02149: Specified partition does not exist

Tom Kyte - Tue, 2019-04-30 16:06
I truncated the partition on a table with local partition index. After inserting the records again in the same partition If I try to rebuild the index on that partition it gives ORA 02149: Specified partition does not exist. However i can see the res...
Categories: DBA Blogs

Combine json objects into one document

Tom Kyte - Tue, 2019-04-30 16:06
I need to combine some existing json objects into a new json object. Using the syntax: <code>json3 := json_object('json1' value json1, 'json2' value json2);</code> the existing json objects get wrapped in an extra set of double quotes, and t...
Categories: DBA Blogs

Query Rewrite to improve performance.

Tom Kyte - Tue, 2019-04-30 16:06
Hi, I have this query which takes about 45 minutes to populate the data into AFS_TABLE. The AFS_TABLE is truncated before this insert runs and it does not have any indexes or constraints. The Oracle version is 11.2.0.4. The VW_PAYMENT table is a very...
Categories: DBA Blogs

Writing data to a clob

Tom Kyte - Tue, 2019-04-30 16:06
Hi Tom how to write the table data into clob column in another table in oracle 12.1 version <code>create table abc(sno number(2) not null,file_clob clob); insert into abc(sno) values(1); insert into abc(sno) values(2); insert into abc(sno) va...
Categories: DBA Blogs

Get the most recent 3 transaction records for a customer

Tom Kyte - Tue, 2019-04-30 16:06
I have a customer session table that has session id and session date along with other columns. I want to see the 3 most recent sessions of each customer. Provided the sample SQL for creating data below: <code>CREATE TABLE Customer ( Custo...
Categories: DBA Blogs

Generate Random number and compare it for uniqueness in database

Tom Kyte - Tue, 2019-04-30 16:06
Hi Tom, I have a situation where I have to create a function which will return a unique number (Suffix R and 7 numbers) which will than be compared with data present in database with a particular column which is primary key. if the number generate...
Categories: DBA Blogs

Issue Global temporary table

Tom Kyte - Tue, 2019-04-30 16:06
Hi Tom I have Stored procedure return some data, <code>create PROCEDURE "SP1" (CV_1 IN OUT SYS_REFCURSOR) /*CREATE GLOBAL TEMPORARY table TT_TABL2 ( ORDER_ID NUMBER, REQ_ID NUMBER, TXN_ID NUMBER, ...
Categories: DBA Blogs

Oracle Insert

Tom Kyte - Tue, 2019-04-30 16:06
Hi Tom, How exactly big insert or update getting processed in oracle? Suppose I have to insert >=10GB more records in this table will this much of insert floods the db buffer cache or how it affect the SGA. Is it the same case with update...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs