DBA Blogs

Regular Expression is not working if the search criteria with LIKE and NOT LIKE in single input field

Tom Kyte - Sat, 2018-05-26 14:46
Hi Tom, Need your help ! Please find the LiveSQL link Thanks in Advance ! I have a table st_exp with s_desc column only, user has option to search by s_desc criteria. Scenario :The user may enter text critiria LIKE and NOT LIKE in the ...
Categories: DBA Blogs

Error in date comparison for partitioned table

Tom Kyte - Fri, 2018-05-25 02:06
Hi Tom, I am seeing a strange issue with a query which queries data from a partitioned table having sub-partitions. Please see table, query and the error. Can you please help, what could be the reason for this error? <code>CREATE TABLE trans_de...
Categories: DBA Blogs

Procedure Performance Number vs Pls_integer

Tom Kyte - Fri, 2018-05-25 02:06
I have task to improve performance in some of the packages and procedures in our application. We have 1 package and it has subprograms around 15-20 procedures. Below are my clarifications required. Iam making changes to datatypes from NUMBER to...
Categories: DBA Blogs

XMLQuery

Tom Kyte - Thu, 2018-05-24 07:46
Hi Tom, I am trying to learn XQuery use with SQL but it looks very complicated. Can you please advise with some simple cases (I am not interested in XML generation but using XML and XQuery functions with relational data tables). Where to start? Wh...
Categories: DBA Blogs

Oracle Database - Grant/Revoke High Concurrency

Tom Kyte - Thu, 2018-05-24 07:46
We have an Oracle 10g release 2 database running on a production environment. It's experiencing a lot of concurrency, as Sql Developer 17.4 "Waits for past 1 hour" graph shows. When the database is behaving slow, we take a look at that graph, and ...
Categories: DBA Blogs

Can I user automatic List in subpartitions?

Tom Kyte - Thu, 2018-05-24 07:46
Dears, I have a table that contains two columns one for year and the other for month. I need to partition this table based on year and month, where year represent the partitions and under that the month represent that sub-partitions. What I need i...
Categories: DBA Blogs

Multiple block allocation to small table

Tom Kyte - Thu, 2018-05-24 07:46
Hi, I executed below query on my database and found given output: <code>select a.table_name, a.NUM_ROWS, a.AVG_ROW_LEN, a.LAST_ANALYZED, a.SAMPLE_SIZE, a.blocks from user_tables a where num_rows <10;</code> Output: <code>TABLE_NA...
Categories: DBA Blogs

Nested loop and hash join.

Tom Kyte - Thu, 2018-05-24 07:46
Hi Tom, Can you help me in understanding how optimizer decides which join ( hash or nested loop) it will use for joining. Also which is the driving table in nested loop. There are lot of confusing answers on this on internet, which one to rely...
Categories: DBA Blogs

Create a physical standby for 12c RAC

Tom Kyte - Thu, 2018-05-24 07:46
hi - this weekend we have a project that is going live. we will be importing data (about 1TB) into the database. after that we want to create the physical standby. what is the best, efficient and most proven way to create a physical standby database ...
Categories: DBA Blogs

How to gather statistics on a standard edition database

Tom Kyte - Thu, 2018-05-24 07:46
Hi, I'll like to gather some statistics on long running statements on a standard edition database. Can you please suggest the best way to gather stats on this statement? <code> BANNER ...
Categories: DBA Blogs

Limit and conversion very long IN list : WHERE x IN ( ,,, ...)

Tom Kyte - Thu, 2018-05-24 07:46
How many elements may be in the WHERE x IN (,,,) list ? I see 2 ways to overcome IN list limitation: 1) use x=el_1 OR x=el_2 OR x=el_3 OR ... 2) create temporary table , but another question arise here: why create table A( X INTEGER, Y...
Categories: DBA Blogs

Users, schemas & privileges in #Exasol

The Oracle Instructor - Wed, 2018-05-23 09:18

Exasol Logo

In Exasol, a database user may own multiple schemas – or even none at all. I connect to my Community Edition to show that:

C:\Users\uh>cd \Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus

C:\Program Files (x86)\EXASOL\EXASolution-6.0\EXAplus>exaplusx64 -c 192.168.56.101:8563 -u sys -p exasol -lang EN

EXAplus 6.0.8 (c) EXASOL AG

Wednesday, May 23, 2018 3:28:29 PM CEST
Connected to database EXAone as user sys.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create user adam identified by adam;
EXA: create user adam identified by adam;

Rows affected: 0

SQL_EXA> grant dba to adam;
EXA: grant dba to adam;

Rows affected: 0

SQL_EXA> select user_name from exa_dba_users;
EXA: select user_name from exa_dba_users;

USER_NAME
------------------------------------------------------------
SYS
ADAM

2 rows in resultset.

SQL_EXA> select schema_owner,schema_name from exa_schemas;
EXA: select schema_owner,schema_name from exa_schemas;

SCHEMA_OWNER
-------------------------------------------------------------
SCHEMA_NAME
-------------------------------------------------------------
SYS
RETAIL

1 row in resultset.

SQL_EXA> connect adam/ADAM;

Wednesday, May 23, 2018 3:34:42 PM CEST
Connected to database EXAone as user adam.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create table t1 (n number);
EXA: create table t1 (n number);
Error: [42000] no schema specified or opened or current schema has been dropped [line 1, column 27] (Session: 1601269589413551548)
SQL_EXA> open schema adam;
EXA: open schema adam;
Error: [42000] schema ADAM not found [line 1, column 13] (Session: 1601269589413551548)

Demo user adam has the DBA role granted but there is no adam schema yet. I need to create it first:

EXA: create schema adam;

Rows affected: 0

SQL_EXA> open schema adam;
EXA: open schema adam;

Rows affected: 0

SQL_EXA> create table t1 (n number);
EXA: create table t1 (n number);

Rows affected: 0

SQL_EXA> create schema adam2;
EXA: create schema adam2;

Rows affected: 0

SQL_EXA> create table adam2.t2 (n number);
EXA: create table adam2.t2 (n number);

Rows affected: 0

SQL_EXA> select table_schema,table_name from exa_user_tables;
EXA: select table_schema,table_name from exa_user_tables;

TABLE_SCHEMA
--------------------------------------------------------
TABLE_NAME
--------------------------------------------------------
ADAM
T1
ADAM2
T2

2 rows in resultset.

As you see, user adam has now two schemas with different tables in them. Now briefly to privileges:

SQL_EXA> create user fred identified by fred;
EXA: create user fred identified by fred;

Rows affected: 0

SQL_EXA> grant create session to fred;
EXA: grant create session to fred;

Rows affected: 0

SQL_EXA> grant select on adam.t1 to fred;
EXA: grant select on adam.t1 to fred;

Rows affected: 0

SQL_EXA> connect fred/FRED;

Wednesday, May 23, 2018 3:53:34 PM CEST
Connected to database EXAone as user fred.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> select * from adam.t1;
EXA: select * from adam.t1;

N
-----------------

0 rows in resultset.

SQL_EXA> select * from adam2.t2;
EXA: select * from adam2.t2;
Error: [42500] insufficient privileges: SELECT on table T2 (Session: 1601270776421928841)
SQL_EXA> connect adam/ADAM;

Wednesday, May 23, 2018 3:54:33 PM CEST
Connected to database EXAone as user adam.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> create role allonadam2;
EXA: create role allonadam2;

Rows affected: 0

SQL_EXA> grant all on adam2 to allonadam2;
EXA: grant all on adam2 to allonadam2;

Rows affected: 0

SQL_EXA> grant allonadam2 to fred;
EXA: grant allonadam2 to fred;

Rows affected: 0

SQL_EXA> connect fred/FRED;

Wednesday, May 23, 2018 3:55:54 PM CEST
Connected to database EXAone as user fred.
EXASolution 6.0.8 (c) EXASOL AG

SQL_EXA> select * from adam2.t2;
EXA: select * from adam2.t2;

N
-----------------

0 rows in resultset.

SQL_EXA> drop table adam2.t2;
EXA: drop table adam2.t2;
Error: [42500] insufficient privileges for dropping table (Session: 1601270923042332982)

That’s because ALL contains ALTER, DELETE, EXECUTE, INSERT, SELECT and UPDATE but not DROP which can be confirmed using EXA_DBA_OBJ_PRIVS.

Categories: DBA Blogs

ORA-01659 on creation of a not unique global partitioned index

Tom Kyte - Tue, 2018-05-22 19:06
Dear Tom, I have a table that stores climatic data with this layer: idcell,day,field1,.... This table is locally partitioned by range on day and it has a local PK index: idcell,day. I want to create a not unique global partitioned index on i...
Categories: DBA Blogs

PL/SQL programming to write to file in batches of 2 million rows

Tom Kyte - Tue, 2018-05-22 19:06
I have been assigned to the task. Task :- In one table , I have 10 million records and I need to export table data into a CSV files/Text files but the condition is that(I need to export into 5 files ,each file should contain 2 million records) 1) ...
Categories: DBA Blogs

Partition pruning with MEMBER OF operator

Tom Kyte - Tue, 2018-05-22 19:06
Hello Tom ! Is it possible to force Oracle to use (sub-)partition pruning when MEMBER OF operator is used on some nested table? For example: <code>SELECT * FROM A_TABLE WHERE COL_1 MEMBER OF NUMBER_TAB_TYPE(1,10,4);</code> where NUMBER_TAB_TY...
Categories: DBA Blogs

HOW TO GET OLD VALUE OF A DATA

Tom Kyte - Tue, 2018-05-22 19:06
HI,THERE I have a situation here, in one of my table, i hvae loc_id column my requirement is that i want all loc_id that have changed to new loc_id eg: LOC_ID CUST_NAME ---------- -----------------...
Categories: DBA Blogs

How to change the subscript/index value in an associative array?

Tom Kyte - Tue, 2018-05-22 19:06
Hi Tom, Is it possible to change the subscript/index value of an existing element in an associative array? <code> declare type a_arr is table of varchar2(20) index by pls_integer; tb1 a_arr; begin tb1(1) := 'aaaa'; tb1(2) := '...
Categories: DBA Blogs

TDE Encryption Wallet Change Password

Tom Kyte - Tue, 2018-05-22 19:06
Requirement We need to be able to change the password on our Oracle TDE Encryption Wallet, which we use to encrypt some of our Tablespaces. This frequently asked questions article states that this can be done with OWM or orapki http://www.orac...
Categories: DBA Blogs

Costs associated in a query execution plan

Tom Kyte - Tue, 2018-05-22 00:46
Hello, I have a question to be clarified related to costs associated with SQL query execution. I have view built on 3 big tables. <code>CREATE OR REPLACE VIEW TEXT_TRANSLATION AS SELECT c.module ...
Categories: DBA Blogs

Write PLSQL procedure to get query result in an excel automatically

Tom Kyte - Tue, 2018-05-22 00:46
Hi Though I have checked in google many times for the answer of my question 'Write PLSQL procedure to get query result in an excel automatically', till I am not having clear understanding . Could you Please explain it in simplest way possible....
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs