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
63 views

How to convert varchar2 column to number in oracle?

I have a table emp_details. The column emp_salary is of type varchar2 in which certain rows are null: emp_salary 100,34 null 20,2 30,3 null I need a result in which the column type is number: ...
0
votes
1answer
86 views

How can I monitor running queries in Oracle?

We use a closed source application but the built-in reporting tools aren't great so we've started using a seperate database reporting application to generate our reports. I need to get a report ...
0
votes
2answers
106 views

Can a trigger catch updates done by a select for update + lob.write()?

It's maybe a stupid question, but I didn't find an explicit answer... I want to catch any update on my oracle db, so I want to use triggers, but the triggers not been activated when using SELECT FOR ...
0
votes
1answer
151 views

Can we export Selected Row with Subquery using data pump

I am trying to export selected rowS from my database using SUBQUERY but I got error. expdp directory=exp_data dumpfile=selected_row.dmp logfile=rows.log tables=sample,super query="where name IN ...
1
vote
2answers
120 views

Performance improvement by placing tables on a virtual RAM disk

I am trying to improve performance of queries by placing the tables in a virtual RAM DISK. I am using in Oracle 11.2 Standard edition with 4G of SGA and 2G of PGA. I created a Virtual RAM DISK of ...
2
votes
1answer
57 views

Create a table based on a table depending on the values in the initial table column

I have a table like: +----+--------+----------------+ | id | name | address | +----+--------+----------------+ | 1 | Ivan | Moscow,Moscow | | 2 | Petr | Samara,Samara | | 3 | Grigor ...
1
vote
0answers
72 views

Oracle Error 12560 generating big sqlnet.log file

I have a Windows 2008 server which have two versions of Oracle: 11.2.0 and 12.1.0 A huge log file is being generated on the following path: ...
2
votes
1answer
102 views

permissions for yet another “ORA-06512: table or view does not exist” scenario

See the code below. I'm running the code with user x in schema y. The exact same code works if I run it by user x in schema x, but not with user x in schema y. I get the error ORA-06512: table or ...
3
votes
2answers
102 views

How to find foreign keys that don't have corresponding indexes?

I have installed Oracle database 11g R2 with a customized application. In my environment we are experiencing locking on tables. I want to check if there are foreign keys in my database without the ...
3
votes
1answer
56 views

Updating multiple columns using Case

I have a query that looks like below: UPDATE some_table SET column_x = CASE WHEN x_specific_condition THEN new_value_for_x ELSE column_x END, column_y = CASE WHEN y_specific_condition THEN ...
0
votes
1answer
29 views

ORACLE: Can I get results of “SHOW PARAMETER” into a variable or table?

When I execute SHOW PARAMETER CONTROL_MANAGEMENT_PACK_ACCESS, it gives me the value of this Oracle parameter as part of what appears to be a single row with three columns (name, type, and value). How ...
1
vote
3answers
158 views

Oracle 11g ORA-01190: control file or data file 1 is from before the last RESETLOGS

I am running Oracle 11GR2, Enterprise Edition Release 11.2.0.3.0. I got one of my Test DBs corrupted today. Luckily, I have 2 weeks of backups in the Net Backup server, so I thought, "This is easy, ...
1
vote
0answers
86 views

Are there security-issues with a read-only-account on Oracle-DBs?

I am currently working as IT-maintenance-guy for a classic MVC-system(oracle-db/jboss) at public sector. The newest feature should be the shutdown of my oracle-db-readOnly-Account cause of "security ...
0
votes
1answer
60 views

Dynamic query with cursors

I need to execute a dynamic query. I need to extract some values of a table. With these a values I can construct the table names where I must do another query and check every value that it gives me ...
0
votes
1answer
102 views

Executing Shell (bash) from PL/SQL

Does anyone know how to execute a shell script from a PL/SQL procedure or function? Let's say the shell script is '/usr/bin/bash/convert.sh'.
0
votes
0answers
82 views

TNS Time out Error ORA-12170

I am trying to connect to a remote Oracle 12c database from a client machine that has 11g oracle drivers for ODBC. I can reach to remote machine through ping but can not Telnet the remote machine on ...
3
votes
1answer
106 views

Asynchronous batch commits in Oracle

We have an Oracle Database data warehouse that averages 120 commits per second (according to AWR). The top wait activity is log file sync, which is to be expected... For now, overall response times ...
1
vote
1answer
79 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
128 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
68 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
56 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
80 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
134 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
899 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
114 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
72 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
43 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
64 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
79 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
52 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
182 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
90 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?