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

1
vote
1answer
76 views

Subtract two dates stored as varchar

I have 2 varchars like: 20160316131824 20160316080002 How can I subtract to see if there is more than 24 hours of difference between the 2 values? I tried using the symbol - but it gives me an ...
3
votes
2answers
126 views

SQL Error: ORA-14300: While partitioning and subpartitioning

I have tried this earlier and it works: I have T1 which has no DATE or STATE which needs to be partitioned on DATE and sub-partitioned on STATE Hence for the ID present in T1, fetch the DATE and ...
1
vote
1answer
67 views

Can standby database of a physical database be pluggable or vice versa?

We are currently planning to upgrade our databases from 11g to 12c. But I have some confusions regarding 12c. Here are my questions. Can standby database of a non-container database be pluggable or ...
1
vote
2answers
35 views

SQLPLUS: SELECT INTO prompting for variable value

Any idea why this is prompting me for the value? I'm calling the script via batch file: DECLARE precount NUMBER; nowcount NUMBER; BEGIN SELECT COUNT(*) INTO precount FROM ...
1
vote
0answers
50 views

ERROR:string literal too long

I have one column which is varchar2(4000 char). Max length of data is 3189. I have checked lenghtb also which is giving the same length 3189. we are displaying this data in one of FE tool(Speedbase) ...
0
votes
2answers
43 views

Does subqueries “short-circuit” based on other conditions?

I have a slightly costly case when exists (...) then 1 else 0 end sub-query in the select clause. The sub-query is only really needed if another column in the main query has a specific value (can ...
-1
votes
1answer
33 views

Select and Insert for all data - Need help querying [closed]

At a first glance, this is my query INSERT INTO T2 SELECT T1.*, T3.DATETIME, T3.STATEID FROM T1 WHERE T3.DATETIME BETWEEN '01-NOV-15' AND SYSDATE LEFT OUTER JOIN T3 ON ...
1
vote
1answer
68 views

ORA-01652 : unable to extend temp segment by 128 in tablespace HFDORA_TEMP

I have increased temp tablespace temp02.dbf by the following command alter tablespace HFDORA_TEMP add TEMPFILE '/orac/u01/oradata/hfd2/system/hfd2/temp02.dbf' size 2000M; But after increasing it ...
0
votes
1answer
34 views

how to run DBMS_RULE_ADM [closed]

Oracle 11.2.0.3 My instructions are DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, '<schema_name>'); I tried to run them at sqlplus /as sysdba but it tells me unknown ...
0
votes
0answers
55 views

Oracle to SQL Data migration failed with No errors using SSMA tool

I'm trying to migrate tables from Oracle (Oracle 11g r2) to SQL Server (SQL server 2014) using SSMA tool, successfully able to create the schema but data migration is failing. I've installed SSMA + ...
0
votes
1answer
78 views

Not getting current IP address in my oracle database [closed]

I have installed Oracle database in my local machine and my local machine IP address is - 192.168.1.55 but in Toad its showing different SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual; ...
2
votes
1answer
128 views

LGWR is switching frequently in every 3,4,7 minutes. what should i do

I am running Oracle 11g R2 single instance on Linux 6.7. Data guard is also configured. In a database I have 4 groups of redologs, each group has 2 members and each logfile size is 200mb. The ...
3
votes
2answers
896 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
48 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
113 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
64 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
42 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
51 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
63 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
89 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
65 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
87 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
55 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
103 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
90 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
40 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
77 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
51 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
173 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
75 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
89 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
68 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?
1
vote
2answers
63 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
64 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
124 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
79 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
48 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
50 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
39 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 ...
1
vote
1answer
126 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
58 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
100 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
90 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
74 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
32 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
19 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
140 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
52 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
65 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 ...