Index based on an Oracle function with an empty function name

I'm trying to understand this syntax in creating an index in a database that I use:

CREATE  INDEX IDX ON SQ.MYTABLE (('ID_COLUMN1'), ('ID_COLUMN2'), ('ID_COLUMN3'));

After searching for a moment, I found no examples using this syntax, and how it differs from a normal multi-column index:

CREATE  INDEX IDX_2 ON MYSQ.MYTABLE ('ID_COLUMN1', 'ID_COLUMN2', 'ID_COLUMN3');

I discovered that it was an index based on the functions, analyzing the following example that I was trying to assemble:

CREATE TABLE t (a number, b number, c number);

insert into t values (1, 2, 3);
insert into t values (4, 5, 6);
insert into t values (7, 8, 9);
insert into t values (10,11, 12);
insert into t values (13, 14, 15);
insert into t values (16, 17, 18);

CREATE INDEX IDX2 ON t (a, b, c);
CREATE INDEX IDX ON t (('d'), ('b'), ('c'));

select * from USER_INDEXES;


INDEX_NAME  INDEX_TYPE  TABLE_OWNER     TABLE_NAME  TABLE_TYPE  UNIQUENESS  COMPRESSION     PREFIX_LENGTH   TABLESPACE_NAME     INI_TRANS   MAX_TRANS   INITIAL_EXTENT  NEXT_EXTENT     MIN_EXTENTS     MAX_EXTENTS     PCT_INCREASE    PCT_THRESHOLD   INCLUDE_COLUMN  FREELISTS   FREELIST_GROUPS     PCT_FREE    LOGGING     BLEVEL  LEAF_BLOCKS     DISTINCT_KEYS   AVG_LEAF_BLOCKS_PER_KEY     AVG_DATA_BLOCKS_PER_KEY     CLUSTERING_FACTOR   STATUS  NUM_ROWS    SAMPLE_SIZE     LAST_ANALYZED   DEGREE  INSTANCES   PARTITIONED     TEMPORARY   GENERATED   SECONDARY   BUFFER_POOL     FLASH_CACHE     CELL_FLASH_CACHE    USER_STATS  DURATION    PCT_DIRECT_ACCESS   ITYP_OWNER  ITYP_NAME   PARAMETERS  GLOBAL_STATS    DOMIDX_STATUS   DOMIDX_OPSTATUS     FUNCIDX_STATUS  JOIN_INDEX  IOT_REDUNDANT_PKEY_ELIM     DROPPED     VISIBILITY  DOMIDX_MANAGEMENT   SEGMENT_CREATED
IDX2    NORMAL  USER_4_AE9EA5   T   TABLE   NONUNIQUE   DISABLED    (null)  FIDDLEDATA  2   255     65536   1048576     1   2147483645  (null)  (null)  (null)  (null)  (null)  10  YES     0   1   6   1   1   1   VALID   6   6   2019-08-19T22:06:23Z    1   1   NO  N   N   N   DEFAULT     DEFAULT     DEFAULT     NO  (null)  (null)  (null)  (null)  (null)  NO  (null)  (null)  (null)  NO  NO  NO  VISIBLE     (null)  YES
IDX     FUNCTION-BASED NORMAL   USER_4_AE9EA5   T   TABLE   NONUNIQUE   DISABLED    (null)  FIDDLEDATA  2   255     65536   1048576     1   2147483645  (null)  (null)  (nul

http://www.sqlfiddle.com/#!4/ae9ea5/5

What is the purpose of this index?

oracle sqldeveloper – how can enable, disable constraints if the table has partitions by reference?

j & # 39; uses oracle12C as a database and using get_ddl to get ddl of database objects. Now, I have two tables Table 1 and Table 2 and Table1 has partition, Table2 uses the main constraint of Table1 as a reference constraint and also uses PARTITIONED BY REFERENCE.For example: –

TABLE1 is: –

create table parent_emp(
empno      number  primary key,
job        varchar2(20),
sal        number(7,2),
deptno     number(2)
)
partition by list(job)
( partition p_job_dba values ('DBA'),
  partition p_job_mgr values ('MGR'),
 partition p_job_vp  values ('VP')

)

TABLE2 is: –

    CREATE TABLE "SECONDARYUSER"."REFERENCE_EMP" 
   (    
    "ENAME" VARCHAR2(10), 
    "EMP_ID" NUMBER, 
    "EMPNO" NUMBER, 
     CONSTRAINT "FK_EMPNO" FOREIGN KEY ("EMPNO")
      REFERENCES "SECONDARYUSER"."PARENT_EMP" ("EMPNO") ENABLE
   ) 
  PARTITION BY REFERENCE ("FK_EMPNO") 
 (PARTITION "P_JOB_DBA" , 
 PARTITION "P_JOB_MGR" , 
 PARTITION "P_JOB_VP" ) "

My problem is that I want to disable the activation constraints of table1 and table2, but when I run the script, I get a tracking error.

  1. alter table parent_emp disable the constraint SYS_C0010720 cascade;

it is used to disable the primary key of Table1 but generating the following error: –

02297. 00000 - "cannot disable constraint (%s.%s) - dependencies exist"
*Cause:    an alter table disable constraint failed becuase the table has
           foriegn keys that are dpendent on this constraint.
*Action:   Either disable the foreign key constraints or use disable cascade

I understand this, so I tried to disable the constraint of table2 and execute the following query.

  1. alter table reference_emp disable FK_EMPNO constraint cascading;

but that gives me the following error: –

alter table reference_emp disable constraint FK_EMPNO cascade
Error report:
SQL Error: ORA-14650: operation not supported for reference-partitioned tables

Please suggest me how can I disable, activate the constraints.

How long does it normally take to install oracle 18c in linux mint?

I'm trying to install Oracle xe 18c in Mint Linux, apparently it works well, but that does not happen from the next point.

Specify a password to be used for database accounts. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9]. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts:
Confirm the password:
Configuring Oracle Listener.
Listener configuration succeeded.
Configuring Oracle Database XE.
Introduzca la contraseña de usuario SYS: 
**************
Introduzca la contraseña de usuario SYSTEM: 
**************
Introduzca la contraseña de usuario de PDBADMIN: 
*************
Preparar para funcionamiento de base de datos
7% finalizado
Copiando archivos de base de datos

This continues about 15 minutes ago, I doubt that the installation time is long and that I am just patient or that something is wrong?

oracle – strange behavior pl / sql – the last character is removed from the select statement, but the actual data has a different length

We have a table with a column name A with the type nvarchar(23).

the next query will always return 23 which means that the actual length of all records are 23.

select length(trim(req.A)), count(*)
  from tableName req
group by length(trim(req.A));
|length(trim(req.A))|count(*)| 
------------------------------
|23                 |1006    |

But when we select in this table with this query, its behavior is different and it seems that the last character is always removed from the Gridview result in the pl / sql developer.

select LENGTHB(req.A) lenb, length(req.A) len, req.* from tableName req
where req.A = 'NHBBBBB1398052635902235'; -- Note to the equal sign and the last charactar (5) of the where clause

the result is:

|lenb|len|          A           |
---------------------------------
|46  |23 |NHBBBBB139805263590223|

As you can see the last character (5) is deleted in the selected result.

Can you please explain what is happening !? Is it related to the configuration pl / sql? How to solve this?

Migrate a remote oracle database (11g) to an oracle XE docker database (11g)

Oracle is a new user and I am trying to migrate an existing virtual machine on which deployment scripts are run to each version to use a Docker container instead. Then I can run the versioning scripts and mark a new container with each version (to use for our integration tests).

Unfortunately, it seems that no matter what I try (exp, expdp, generate ddl from datagrip, generate ddl from data studio, expdp in toad), it is impossible to fully copy a database remote in the XE database without a ton of errors in the packages. or key constraint errors.

What do I miss?

Here is what I have done so far:

expdp user/pass@XE directory=dp_dir FULL=YES dumpfile=8_17.dmp logfile=8_17.log network_link=XE_BUILD content=ALL TRANSPORT_FULL_CHECK=YES

This creates a dump file that seems to be valid.

So I do that;
impdp importer/importer@XE directory=dp_dir full=Y dumpfile=8_17.dmp logfile=imp_full.log

This also ends with a relatively small number of errors. However, I am constantly receiving key constraint errors and broken packets that I do not encounter when using the remote database.

I've tried to rebuild all the packages and things like that, still no luck.

Oracle DDL Trigger Error – RDSADMIN.RDS_DDL_TRIGGER

On Oracle 11g, when calling a procedure via a Web application, which creates a temporary table inside, I am faced with an error as RDSADMIN.RDS_DDL_TRIGGER error. . When looking for this problem, we found that RDSDMIN is the system user created by default, but nothing else. Surprisingly, running the same procedure from the oracle developer succeeds. Can I have more information about this?

c # – Create from scratch an Oracle database on a remote server

Salutations to all.

I am doing an independent database program, using System.Data.Common, DBProviderfactory, etc. I have to create a database on an Oracle server from scratch, then create tables, stored procedures, and functions. The program runs from a location other than the Oracle server. I already did it with a SQL server and now I have to do it with Oracle.

I share the code for SQL Server:

dpf = DbProviderFactories.GetFactory(dp); //dp = "System.Data.SqlClient"

conexion = dpf.CreateConnection();
using (conexion)
{
     //Cadena de conexión a la master para crear la base de datos
     this.conexion.ConnectionString =
          "server=" + this.txt_SD_Servidor.Text +
          ";database=master" +
          ";user id=" + this.txt_SD_UserId.Text +
          ";password=" + this.txt_SD_Pwd.Text;
     this.conexion.Open();

     dbcmd = conexion.CreateCommand();
     dbcmd.CommandText = "CREATE DATABASE " + this.txt_SD_BD.Text;
     dbcmd.ExecuteNonQuery();

     conexion.Close();

     //Cadena de conexión a la nueva base de datos para crear las tablas, procedimientos y funciones.
     this.conexion.ConnectionString = this.txt_CnStr.Text;
     this.conexion.Open();

     dbcmd = conexion.CreateCommand();

     dbcmd.CommandText = "CREATE TABLE ..."
     dbcmd.ExecuteNonQuery();

     dbcmd.CommandText = "CREATE PROCEDURE ..."
     dbcmd.ExecuteNonQuery();

     dbcmd.CommandText = "CREATE FUNCTION ..."
     dbcmd.ExecuteNonQuery();
}

I have no experience working with Oracle, I only used it to connect to existing databases and manipulate records.

I've seen an example in a previous post, but in this post they use a local server, ie the program is on the same computer as Oracle.

Here is the link to the post:

Create an Oracle database and a schema by ADO.NET program

As you can see in the previous post, the location of the oradata road is local:

string oracleDataPath = "C:\PATH_TO_ORADATA\";

I have to do the same thing, but direct it to a remote server.

Thank you in advance for the help you can give me.

4 tables join Oracle – Code Review Stack Exchange

I have 4 tables and I am trying to join them. it does not work yet.

Hello community.

I have 4 tables and I am trying to join them. it does not work yet. I must contact this user to be able to retrieve the first and last name of the engineer, the name and email of the faculty, as well as the subject and title of the class for each course registration.

CREATE TABLE Engineers(
EID NUMBER,
Lastname varchar2(50),
Firstname varchar2(50),
Email varchar2(50),
Graddate DATE,
PRIMARY KEY(EID)
);

CREATE TABLE Faculty(
FID NUMBER,
Lastname varchar2(50),
Firstname varchar2(50),
Email varchar2(50),
Hiredate DATE,
PRIMARY KEY(FID)
);


CREATE TABLE Classes(
CID NUMBER,
Subject varchar2(50),
Catalognbr varchar2(50),
Title varchar2(50),
PRIMARY KEY(CID)
);

CREATE TABLE ClassEnrollments(
EnID NUMBER PRIMARY KEY,
EID NUMBER NOT NULL,
FID NUMBER NOT NULL,
CID NUMBER NOT NULL,
CONSTRAINT fk_engineers
    FOREIGN KEY (EID)
    REFERENCES Engineers(EID) ON DELETE CASCADE,
CONSTRAINT fk_faculty
    FOREIGN KEY (FID) 
    REFERENCES Faculty(FID) ON DELETE CASCADE,
CONSTRAINT fk_classes
    FOREIGN KEY (CID) 
    REFERENCES Classes(CID) ON DELETE CASCADE
);

and that the request I came up with:

SELECT
    EnID,
    Engineer.Lastname AS Lastname,
    Engineer.Firstname AS Firstname,

    Faculty.Lastname AS Flastname,
    Faculty.Email AS email,
    Classes.Subject AS Subject,
    Classes.Title AS Title,
FROM
    ClassEnrollments
INNER JOIN Engineer ON Engineer.EID = ClassEnrollments.EID
INNER JOIN Faculty ON Engineer.FID = ClassEnrollments.FID
INNER JOIN Classes ON Engineer.CID = ClassEnrollments.CID;

oracle – quickly retrieves a small number of rows

I am not sure if my SQL code has a problem or if the problem lies with Oracle. We have a function on a web application that must display a table with a large number of entries (in millions). However, the display only gets 500 lines at a time. I guess it should be very fast.

The idea is to do something like this:

SELECT TOP 500  WHERE 

The WHERE clause is fully covered by an index. However, Oracle does not provide either TOP or LIMIT, so the alternative to use seems to be ROW_NUMBER() or OFFSET / FETCH FIRST.

However, when checking the execution plans, it seems that even for the simplest queries, Oracle claims to need to read the entire array before applying it. ROW_NUMBER() and filtering through it. Same thing for FETCH FIRST because it seems like Oracle translates FETCH FIRST into a ROW_NUMBER() construction:

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=500)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=500)

Example:

SELECT ID FROM  FETCH FIRST 500 ROWS ONLY;

Plan of execution:

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                      |  1643K|  5581M| 34399   (1)| 00:00:03 |
|*  1 |  VIEW                  |                      |  1643K|  5581M| 34399   (1)| 00:00:03 |
|*  2 |   WINDOW NOSORT STOPKEY|                      |  1643K|  1234M| 34399   (1)| 00:00:03 |
|   3 |    TABLE ACCESS FULL   | TABLENAME            |  1643K|  1234M| 34399   (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------

This becomes more extreme with a single line because the plan is the same. These costs are brutal for an operation resulting in a very small number of lines.

Is there a way to prevent Oracle from reading all records before returning anything?

plsql – Oracle – Browses the list of databases to check the status

I have a table with database names and connection strings. Do you know a method to browse the list of database names (and / or connection details) and test the database connectivity (just know if the database is active)? The only problem is that DBLinks does not point to these databases, but we use OID if it can be useful / useful.

Thank you!