DBA Blogs

11g Apex Email not working from Sqlplus

Tom Kyte - Mon, 2018-05-28 21:46
Hi Tom, I can successfully send email from Apex application using apex_mail.send. When I try to use the same from sqlplus it gives the following Error BEGIN apex_mail.send (p_to => 'test@test.com', p_fr...
Categories: DBA Blogs

Why might Consistent Reads be increasing with every iteration of a select cursor being run by PL/SQL?

Tom Kyte - Mon, 2018-05-28 03:26
<code></code>We have some PL/SQL that is running many sql statements, and taking an awfully long time to complete. (It?s Oracle code, btw, in E-Business, not custom). The problem seems to be a couple of the statements, (run thousands of times each) w...
Categories: DBA Blogs

Full text indexes in Oracle XE

Tom Kyte - Mon, 2018-05-28 03:26
hello Is it possible to combine b-tree indices with Full-Text indices in oracle 11(express)? Merci
Categories: DBA Blogs

Need some Oracle Request syntaxe to extract informations

Tom Kyte - Mon, 2018-05-28 03:26
hy all, it's my first discussion that i post . please help me: i work on a solution of supervision of oracle database, and i need two request syntaxe to extract informations: The first: the requset to show the most Oracle query consume cpu time ...
Categories: DBA Blogs

Is it possible - to show OS disk free space together with DBA_DATAFILES data ?

Tom Kyte - Mon, 2018-05-28 03:26
In some of my custoner's databases the DBAs are using AUTOEXTENSIBLE datafiles, but with many datafiles comparting the same filesystem, in this sense : tablespace A, datafiles /u01/oradata/ts_A_file01.dbf autoextend unlimited tablespace B, da...
Categories: DBA Blogs

Exadata Vs RAC

Tom Kyte - Mon, 2018-05-28 03:26
Tom ? My understanding of RAC( or grid computing) , we use cluster of not so expensive servers for higher availability. But Oracle is marketing Exadata ( expensive severs ? relatively speaking ) for performance / higher availability , so on. ( if ...
Categories: DBA Blogs

How to find the UUID of a device in Linux for Oracle ASM

Pakistan's First Oracle Blog - Sun, 2018-05-27 22:45
UUID stands for Universally Unique Identifier. I use UUID for my disk device, when I need to create and add disks for Oracle ASM, as UUID is independet of device name or mountpoint. So its always a good idea to include UUID of device in the fstab file in Linux.

So here is how to find the UUID of a device in Linux for Oracle ASM:




[root@bastion ~]$ ls -l /dev/disk/by-uuid
lrwxrwxrwx 1 root root 11 JAN 18 20:38 1101c254-0b92-42ca-b34a-6d283bd2d31b -> ../../sda2
lrwxrwxrwx 1 root root 11 JAN 18 20:38 11dc5104-C07b-4439-bdab-00a76fcb88df -> ../../sda1

HTH.


Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs