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

-5
votes
1answer
34 views

how to data dictionary? [closed]

How to data dictionary owner and table name. Additionally, the output should add the comments on the rows. Input: owner and table name. Output: columns for Name, Null?, Type, Comments focusing on a ...
0
votes
1answer
28 views

Oracle : Is my DISTINCT function working properly?

I have alphanumeric values as my primary key. I am querying my database in two different ways and finding different results. It is almost certain that I might be missing some key point in my queries. ...
1
vote
1answer
29 views

Oracle Wallet Connection setup for SSIS

How to configure SSIS package to connect to oracle using oracle wallet? Specifically for using OLEDB to connect to oracle and load data to oracle tables.
0
votes
1answer
28 views

Oracle 11G, Move indexes from one datafile to another

I have an Oracle 11G DB installed on Windows Server 2008 SP2 64bit OS. Below is some data files usage statistics: As you can see, there are two files: index01.dbf and index02.dbf belong to the same ...
-1
votes
2answers
57 views

Count txn date in oracle database [closed]

We want to count transaction date/time as result screenshot above, Please helps check code as below, and the code as below is when run the result as screenshot below, Thanks all guy. for r ...
1
vote
2answers
86 views

Why query plan changes by number of rows

I execute following two queries on an oracle database: select * from qsn join plc on plc.fk_qsn = qsn.id where rownum < 48507 which returns result in less than a second and with following ...
1
vote
1answer
46 views

Run Oracle queries on Sublime Text 3

I'm trying to run some queries in Sublime Text3. I downloaded this plugin, and I set up the settings: { "connections": { "Connection 1": { "type" : "oracle", ...
-3
votes
1answer
34 views

Converting SQL Syntax: Oracle to MySQL [closed]

I have the following SQL code written in Oracle SQL developer: CREATE TABLE students ( student_id INT NOT NULL, username VARCHAR2(30), email VARCHAR2(80), password VARCHAR2(30), f_name VARCHAR2(30), ...
-1
votes
2answers
73 views

Oracle audit log to other server?

How can I configure audit log records to other server? Because: DB Server is full because it is very big. Our IT team manages DB server and our team (security team) is going to get audit log in our ...
0
votes
1answer
50 views

Oracle: How can I create a where clause programmatically?

Suppose I have a table like: TBL_CONDITIONS | ID | WHERE_CLAUSE | |----|---------------------------------------| | 1 | fruit = 'apple' | | 2 | ...
0
votes
1answer
58 views

Is catclust.sql really required? What is the importance

We are in a process of upgrading RAC Databases, and when validating the installed components, we saw that some of the databases aren't displaying the RAC component, but still the RAC works normally ...
0
votes
1answer
75 views

Can we expdp using JOINS in QUERY clause

I have a task to export data from database using data pump. I have successfully exported database using subquery in QUERY clause. But I want to know can we expdp using joins in QUERY clause?
0
votes
3answers
79 views

How to make Database to support Multiple Languages

How to make the database to support national language characters if there are any special characters in languages other than English it is showing as ?. For example :AQUAFRESH Мой первый зубик is ...
0
votes
1answer
99 views

Does PL/SQL code run differently in sql plus vs sql developer?

Today, I stumbled upon a question someone posted about the / character and ; character. The Answer doesn't seem to be down-voted, so I questioned it a little more as to why it wasn't down-voted. ...
0
votes
1answer
41 views

Identify rman files and delete them

I need to clean up space and do a full backup of database. Old backups are safe on tape. My backups are on 2 specifics directories (2 channels). So I did: DELETE EXPIRED BACKUP; DELETE OBSOLETE; ...
0
votes
1answer
76 views

How to commit transaction on an after update event trigger?

I'm new here in dba.stackexchange.com, so if I were to make any mistake, my apologies in advance. I kinda confused on how trigger work. So I have a trigger called updcredaccess on table A that ...
1
vote
1answer
56 views

How to add Subpartition to all partitions

I believe adding sub-partition in Oracle 11g is easy ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS'); I have close to 250 partitions..namely jan07 feb07 ... ...
0
votes
0answers
47 views

how to track lob changes?

this question continues this one: I have table that simulate a file system or directory of text files with the following columns: file_name varchar2 not null primary key, file_text clob, ...
1
vote
1answer
61 views

Selective data extract at Schema level

I am attempting to achieve the following within a single data pump export: SCHEMA1 = Structures only SCHEMA2 = Structures only SCHEMA3 = Structures and data The answer in this question only works ...
1
vote
1answer
44 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
74 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
98 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
106 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
115 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 ...
1
vote
1answer
53 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
68 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: ...
1
vote
1answer
91 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
100 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
53 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
27 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
102 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
47 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
80 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
61 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
99 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
70 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
114 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
58 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 ...
0
votes
0answers
48 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
52 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
32 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
46 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
72 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
117 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
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 ...