All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

3
votes
2answers
889 views

How to check if the table has rows?

I am completely new to PL/SQL. I have written the following PL/SQL script. But it doesn't execute and gives compilation error: set serveroutput on SIZE 1000000; IF EXISTS (select * from my_table) ...
-1
votes
1answer
47 views

Alter - Subpartition a partitioned table

I have a table which is partitioned by RANGE on DATE key CREATE TABLE T1 DOC_ID VARCHAR NOT NULL. STATE VARCHAR ... DOC_DATE ... PARTITION BY RANGE (DOC_DATE)( PARTITION feb16 VALUES LESS ...
2
votes
3answers
89 views

Oracle 11gR2: ORA-01102: cannot mount database in EXCLUSIVE mode

I am running Oracle 11gR2 on an AIX box. I did a rename database and change DB ID using NID. I can restart db in a nomount mode, but I can not mount it by the following error. ORA-01102: cannot ...
0
votes
1answer
45 views

How to change initial extent of an existing partition

I'm having trouble with something that should be rather simple ... I'm sure I'm missing something obvious. I'm trying to change the initial extent of an existing table partition. For some reason, I ...
1
vote
1answer
41 views

special setup to create views in Oracle in default schema

When creating views in Oracle, do you need special setup for a given user if the view and table in which the view is reading from are in two different schemas? The user creating the view has dba ...
0
votes
1answer
49 views

What is the name of the default schema in Oracle?

When you create an object in Oracle, and do not specify a schema (object prefix), is there a name for that default schema? I see in Oracle SQL Developer, that a newly created object shows up in the ...
1
vote
1answer
59 views

Is there any trick to Resize LVM online that raw by UDEV rule without filesystem?

Here is the Logic Volume for example: lvcreate -L2G -n Lv_OraD1 vg_db lvcreate -L180G -n Lv_OraD2 vg_db lvdisplay /dev/mapper/Lv_OraD* UDEV rule to raw the LV: vi ...
2
votes
1answer
79 views

Why Does Oracle kill a session at the specific time? [closed]

Oracle kills sessions remotely open at the specific time. It means if I open a session at local machine, it is ok. In my case, at every 58 minute. For example, at 13:58, 14:58, 15:58 and so on. It ...
0
votes
2answers
63 views

How to omit multiple columns where is not null?

I've constructed the below query which satisfies the requirements, except output includes NULL values between all "vendor_address2" columns, which isn't intended. I suspect my error is somewhere ...
0
votes
1answer
86 views

Encapsulate table with view

I need to change the order of columns in my table (Oracle and SQL Server). Reason is that some of our software calls select * and insert without specifying columns. Refactoring will be too expensive ...
0
votes
1answer
50 views

How to group joined tables by aggregate function, specifically by average group-name type?

I'm trying to build a query selecting two columns (category types) from one table JOINED to another table-column displaying average grades as percentage number-types; here is my first attempt: SELECT ...
0
votes
4answers
90 views

How to dump Oracle database into a textual SQL script?

I am thinking on what mysqldump or pg_dump are doing. So, they dump an active database into a textual file containing the SQL queries, which reproduce this whole database. In mysql and in postgresql, ...
2
votes
2answers
81 views

How do I get the DDL used to create function based index?

I am reverse-engineering the schema of an Oracle database. I have a table and I created a function-based index on it. DROP TABLE Fbi_tab; CREATE TABLE Fbi_tab ( a INTEGER, b INTEGER, c ...
0
votes
1answer
37 views

cli connection to oracle on linux

I am trying to connect to a local oracle on both windows and linux machines. I am running the following command echo Select count(*) FROM all_tables where owner='schema_name'; | sqlplus ...
0
votes
2answers
56 views

Table redefinition ends with ORA-02448

I'm trying to redefine a table to be partitioned. What I did: CREATE TABLE WERTEVAR_TMP( "WVTEIL" NUMBER(10,0), "WVMERKMAL" NUMBER(5,0), "WVUNTERS" NUMBER(5,0), "WVWERTNR" ...
0
votes
1answer
49 views

Very large subpartitions after table redefinition

I'm trying to redefine a table to be partitioned. The original table has about 1.5M rows and about 150MB size. select segment_type, segment_name, bytes / 1014 / 1024 as SIZE_MB from user_segments ...
1
vote
1answer
115 views

What does node eviction mean in the Oracle RAC world?

Software environment: Oracle 12c with RAC running on RHEL The question might sound rather banal, but I wasn't able to find any word on this in the Oracle documentation I read thus far. By searching ...
0
votes
1answer
74 views

Finding the most repeated value for each associated attribute

I'm practicing some SQL and I thought about the following problem: For each pub find the time when more people go. I have the following tables: GOESTO id_person id_pub time 1 ...
2
votes
1answer
70 views

Grant select on all synonyms of schema to another schema in Oracle

I need to write a query for granting the select privilege to all synonyms and tables of one schema to another schema in Oracle. Example: Admin schema has all the tables. I want the User schema to ...
1
vote
1answer
57 views

Oracle 11.2.0.3 Service Name Mismatch issue

My Environment During Installation SID lab3 Service_Name lab3.domain.com Changes Some time ago I changed my service_name to just lab3 to match my SID Select instance_name from v$instance; ...
-1
votes
1answer
23 views

Not sure what's wrong with this… HELP! [closed]

SELECT ROUND(months_between(SYSDATE,birthdate)/12,'YEAR') FROM f_staffs WHERE first_name ='Bob' it is giving me an "invalid number" error. Can anyone help me fix this?
0
votes
2answers
58 views

How to Find SYS Privilege Users

I want to know which users have SYS and SYSDBA privilege before export/import operation. I searched a lot but I could not find.
1
vote
0answers
59 views

Configure Oracle database (first time user)

I am trying to learn how to use Oracle Database. I downloaded the Standard Edition version 12c from here: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html. I ...
1
vote
3answers
112 views

How to draw ER diagram for recursive relation when the number of roles is more than 3?

I know for a recursive relation where there are 2 roles, for example: Worker works for Manager and Worker and Manager both are 2 roles of Employee, we show it this way: But what if Employee had ...
0
votes
1answer
62 views

ORA-29913 and ORA-30653 error selecting an external table

I created an external table using a .tbl file and now Im trying to select all rows from that table, using: select * from users_load; But Im getting this error: ORA-29913: error in executing ...
0
votes
0answers
46 views

oracle : Effect of inserting new rows when row movement is enabled

I have a table which is partitioned by key. I want to move few records (which I require later) to another partition so that I can drop this partition and get some free space. I am enabling row ...
1
vote
1answer
49 views

Determine structure of out cursor

I have a function in Oracle that neither I nor my team wrote and is heavily obfuscated. However, it has a cursor out parameter. How can I find out the number and types that the cursor contains? For ...
0
votes
1answer
36 views

How to total additional results from alias column results in Oracle SQL?

I'm attempting to display the same number of invoices that have the same number of days between two specific dates (e.g. issuance date and payment date). I can ascertain a segment of the ...
0
votes
1answer
97 views

Oracle connection from Squirrel SQL

I am trying to set up Squirrel SQL for the first time on my Ubuntu Desktop. Here is the SQLPlus command I use to connect to my DB: sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com" ...
0
votes
0answers
56 views

Oracle SUBSTRB function using different character-set

SUBSTRB returns the substring with the length calculated in bytes. Though I didn't find it documented anywhere but it seems that the byte length calculation is done based on database character-set. ...
0
votes
0answers
67 views

DBeaver launch script from file

I'm trying to find a good alternative to SQL Developer for performing queries and scripts on Oracle instances. I find DBeaver to be an interesting tool, it has a lot of great features, BUT it seems ...
1
vote
1answer
84 views

Oracle Wallet Error in Red Hat High Availability Resource

Environment: Running Red Hat 7.2, using Pacemaker 1.1.13-10.el7_2.2 and Corosync 2.3.4-7.el7_2.1 to implement cluster failover of an Oracle 12c Enterprise Edition Release 12.1.0.2.0 Database. ...
0
votes
1answer
72 views

Inheritance between table and type in Oracle

The code: CREATE TABLE CLIENTS_PUBLICS OF TYPE CLIENT_PUBLIC UNDER CLIENTS NESTED TABLE Adresse STORE AS ADRESSES NESTED TABLE Compte_Bancaire STORE AS COMPTES ; The error: Error at Command ...
0
votes
1answer
28 views

How to convert the column sum-function-total to dollars in Oracle SQL?

I am unable to format the below query intending to display the column-alias total re-formatted to display as dollars. Though unsuccessful, I suspect a nested solution combined with TO_CHAR, is ...
0
votes
0answers
17 views

How to troubleshoot ORA-00333 with NOARCHIVELOG mode

I'm getting the following errors when I try to connect to database: ORA-00333: refaire bloc 8746 d'erreur de lecture de journal compte 7562 Errors in file ...
0
votes
1answer
133 views

Load data from flat file into oracle table [closed]

I created a new database, then created a table "users". Now, I'm trying to load data to this table from a flat file called 'users.tbl', that has some records with the following format: ...
1
vote
1answer
50 views

Duplicate fails with no copy of datafile nnn found to restore

I've duplicated our production to our UAT environment several times in the past but due to various reason, this time has been a serious PITA. Oracle 11.2.0.3. We have a recovery catalog setup. This ...
0
votes
1answer
60 views

Oracle: dbms_repair outputs zero errors about known corrupted object

In an Oracle 9i instance we have a datafile with corrupted blocks as evidenced by the log generated by the exp utility: . . exporting table IMPORTANT_TABLE EXP-00056: ORACLE error ...
1
vote
1answer
85 views

SCAN listener redirects connection to another port

I have setup RAC. I have two listeners LISTENER and LISTENER_DG. LISTENER is listening port 1521 LISTENER_DG is listening 1530 When client connects to the database using SHARED connection mode even ...
0
votes
2answers
58 views

Space is removing at end of the line

While running the insert script in sql*plus, space is removing. Can you please help us is there any plus command available in Oracle to not remove space at end of the line. Ex: After the text ...
1
vote
1answer
58 views

How to mount a query to join by searching by last date

I have the following tables: TB_PRODUCTS ID COD_PROD Name =============================================== 1 100 Video monitor 2 101 Keyboard 3 102 Mouse ...
2
votes
1answer
123 views

Oracle. How to copy BLOB, LONG RAW and BFILE column between tables

I have a table name mytable in Oracle 9i. columns of mytable are id number(9,0) C1 LONG RAW, C2 VARCHAR2(256), C3 BLOB, C4 BFILE when I try to do this: create this_other_table as select * from ...
-2
votes
1answer
101 views

Display top 5 sales and bottom 5 sales in the same query

I want to display the top 5 sales and the bottom 5 sales in the same query in Oracle, not in PL/SQL.
0
votes
1answer
44 views

Changing own user permissions with Oracle SQL not being the DBA

I have been making changes to a couple of schemas of different environments (they belong to the same application). Sometimes I'll want to copy data from one environment to the other, and sometimes ...
0
votes
1answer
79 views

Compare two databases and get the differences

I want to between two databases and get the differences: columns: in the first table the column name is varchar2(12), in the second table the column name is varchar2(20). Or I have an additional ...
0
votes
1answer
65 views

Migrating database from oracle 11.2.0.03 to 11.2.0.04. [closed]

I have been assigned task of migrating our oracle database from 11.2.0.03 to 11.2.0.04 version. Being new to oracle, I have list down all activities I need to carry out to make it successful. I will ...
0
votes
1answer
76 views

How do I kill a non-running impdp job?

I am trying to run an import operation into an Oracle 11G R2 database from a .dmp file using SQL Developer 4.1's Data Pump Import Wizard. I set everything up with IMMEDIATE start, but then the job ...
0
votes
0answers
77 views

How do I open Data Pump Import Wizard in SQL Developer 4.1?

How do I find and open the Data Pump Import Wizard in SQL Developer 4.1? I opened the Help search tool in SQL Developer 4.1 and searched for Data Pump, and found directions that include: 1.) ...
1
vote
1answer
70 views

Compare Strings ignoring accents in SQL

I would like to know if there is an easy way to compare two text values ignoring the accents and upper case. Im working with an Oracle database. Thank You!
1
vote
3answers
196 views

ORA-02270: no matching unique or primary key for this column-list

I getting an error report of: Error report - SQL Error: ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column-list" ...