Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...
0
votes
1answer
32 views
Error in trigger: table is mutating
CREATE OR REPLACE TRIGGER "TRANSACTION"
before
update of totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD
for each row
begin
declare
mportfolioname varchar2(20);
...
3
votes
3answers
54 views
Most efficient and practical way to store time values (HH:MM) in Oracle that makes it easily searchable
I have a set of starting and ending times that I need to store in an Oracle database. I need to store them in a manner that makes them easily searchable (i.e. a typical search is find all rows where ...
0
votes
1answer
30 views
How to use database users as a foreign key field in any table
I want to create a table where I can have a column username which will be the foreign key and it will only contain usernames of existing users on that database. For example, if I want to insert 'John' ...
0
votes
2answers
63 views
How find all the data loaded on previous day?
Issue: Data Loading into development environment from production database
My production database is 40gb but I do not want all of those 40gb of data into my dev env and so my question is
How can I ...
0
votes
0answers
24 views
Database Volume Statistics in Oracle - dba_hist_tbspc_space_usage
I am looking for generating a Database Volume Consumption Report in Oracle 11g based on a specific time period.
For e.g
1) Total Volume( Excluding Index Size) at 12 AM
2) Total Volume( ...
0
votes
1answer
48 views
Mistake during Oracle 11g PITR
I tried using set time until.. and mis-typed the date. Can anyone help me understand how to get my backups into a manageable state?
After the accidental recover, most of my backupset disappeared.
I ...
-1
votes
0answers
36 views
Oracle Hangs on Book Order Process [closed]
Why did our oracle server hang on Book Order Process?
When we look at the v$sqlarea, the sqltxt is incomplete.
2
votes
2answers
52 views
How to remove all hidden parameters from Oracle Database
How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended?
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ...
2
votes
1answer
51 views
What's the most appropriate NLS_SORT parameter to cover all/most European languages?
I'm developing an Oracle 11g-based application which is likely to be used by clients in many different countries (mostly European). I want the program to change its NLS_SORT so that it's case ...
0
votes
1answer
47 views
Oracle RAC 11g R2 DB silent install: parameter needed for system class
I'm installing Oracle RAC DB 11gR2 using response file and silent mode. The installation is failing with the error message:
[INS-35421] This options installs a single instance database only
You ...
0
votes
3answers
46 views
Built-in XML functions
I am trying to use built-in XML functions, but I'm getting an %s: invalid identifier error. Selects works fine without the XML functions.
Any ideas where is the mistake?
select id,full_name from ...
2
votes
1answer
50 views
User Password Expiry date
Is it possible to find my password expiry date without asking the DBA?
My user does not have privileges to view the DBA_USERS table. Also, I tried select * from USER_USERS table, but expiry_date is ...
0
votes
0answers
17 views
Authenticate Oracle Database with Active Directory Users [duplicate]
I'm trying to connect to Oracle Database 11gR2 with the users from Active Directory. I'm looking at some resources/guidance on how to go about doing this successfully.
The link Map Oracle Roles to ...
2
votes
1answer
62 views
How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?
I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job.
I have a remote Oracle 11g R2 database on unix and a local one on Windows.
I read that you can install the oracle ...
4
votes
1answer
74 views
General tool to load dump files
I am a big fan of Postgres both for its price but also for its features.
I am going to have to need to upload into it both Oracle dump and SQL Server files.
I will try to ask and beg for plain .csv ...
2
votes
3answers
34 views
Oracle error : Shutdown immediate error
echo $ORACLE_SID
YBRD
conn sys/hello as sysdba
SQL>shutdown immediate
Then I was waiting for it to shutdown for a couple of minutes. It didn't, so I closed my window.
Then I again logged in and ...
-1
votes
0answers
34 views
How can I start my Oracle database? Is it not allowing user login? [closed]
Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\bh>sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 8 20:17:40 2013
Copyright ...
4
votes
1answer
80 views
Do I need to use NVARCHAR rather than VARCHAR to properly store UTF8 characters in Oracle?
I want my Oracle database to properly store ALL possible UTF8 characters. If NLS_CHARSET is set to AL32UTF8 is that all I need to do? Or do I need to also make sure I use NCHAR and NVARCHAR rather ...
0
votes
2answers
47 views
Oracle 10XE: Stop schemas seeing the all_users table?
I've got an Oracle 10g XE Database that I want clients to be able to view remotely via SQL*PLUS or SQL Developer. Each schema has a name which includes the name of the client, so anyone looking at the ...
1
vote
1answer
56 views
Using a Having clause with an outer join
I want to use a "having" clause with an outer join, using a column that is used in the outer join.
This is my query:
SELECT
I.CARD_BIN BIN
FROM
GE_ICA_BIN I
LEFT OUTER JOIN GE_PAYEE_VALIDATION ...
1
vote
3answers
54 views
Transfer data from DB2 to Oracle
I want to transfer data from an old DB2 system to a new Oracle database.
How should I go about doing this?
-1
votes
0answers
28 views
Oracle Connection Manager cman.ora startup
I can't startup the cman.ora file
Here is the file cman.ora contents
CMAN_Saf1-PC =
(CONFIGURATION=
(ADDRESS=
(PROTOCOL=tcp)(HOST=192.168.0.206)(PORT=1821)
)
(parameter_list =
...
2
votes
1answer
36 views
Testing AFTER INSERT Trigger
I have the following table:
CREATE TABLE Train(
Train_No integer PRIMARY KEY,
Loco_No integer REFERENCES Locomotive(Loco_No),
Back_Loco_No float REFERENCES Locomotive(Loco_No),
...
0
votes
1answer
40 views
Find Underlying Data Type (int - integer) of Oracle Table instead of number
I can create an example table using following syntax.
CREATE TABLE "TABLE2" (
"COLUMN_INT" INT NOT NULL,
"COLUMN_INTEGER" INTEGER NOT NULL,
"COLUMN_SMALLINT" SMALLINT NOT NULL,
...
0
votes
1answer
45 views
User with dba privs can't login to APEX, but can from SQL*Plus
I'm using Oracle 11gR2 XE. A user with dba privileges can't log in to the APEX interface, but can from the command line with sqlplus.
I just created user:
CREATE ROLE administrators;
GRANT dba TO ...
0
votes
0answers
45 views
DBA_SCHEDULER_Job is not giving any result
I have created one procedure that is supposed to trigger on 5th date of every month.for that i have used dba scheduler job.but its not triggering.
select job_name,status from dba_scheduler_jobs;
...
2
votes
2answers
103 views
Duplicating a Database with RMAN
I would like to duplicate to the same server Oracle Database with RMAN. I've configured flash recovery area for Oracle Database. My database is using SPFILE as shown below.
SQL> SELECT ...
2
votes
1answer
34 views
How to connect to Oracle 11g using “Navicat for Oracle”?
My Oracle 11g Enterprise Manager runs like this:
I have taken the following screen-shot after my Oracle installation has been completed:
I have the following Oracle-related services running:
...
0
votes
1answer
75 views
Getting “BadImageFormatException” when attempting to connect through Visual Studio to Oracle
I get the following message: “Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components ...
0
votes
2answers
138 views
Exporting 30 million rows to CSV
I have a query that hits multiple tables in an Oracle database (11g). I don't have access to the server itself. I use SQL Developer or SQL*Plus to connect.
There are about 31 million rows, and 7 ...
0
votes
1answer
49 views
Timezones in Oracle 10
I have an application that stores its timestamps in Oracle using UTC (Coordinated Universal Time). Another application reads these timestamps from the database, but there is no convenient way to ...
0
votes
1answer
52 views
Compare dates problems
I created a table with the following columns:
CREATE TABLE INDICADOR_DADOS
(
ID VARCHAR2(10 CHAR) NOT NULL,
DATE_START DATE NOT NULL,
DATA_END DATE NOT ...
1
vote
1answer
91 views
NLS_TIMESTAMP_FORMAT changing automatically and randomlly
I am using Oracle 11gR2. My nls_timestamp_format is changing randomly from dd-mon-rr to yyyy/mm/dd.
Does anyone have any clue why this is happening?
0
votes
0answers
30 views
Oracle to SQL Server DDL replication
We have this customer requirement to do DDL + DML transactional replication from Oracle to SQL Server 2012. The DML part is straightforward. There are multiple options to choose from including the SQL ...
1
vote
1answer
25 views
Oracle Express intermittently stops responding, requiring a OS reboot to fix
I've been having an issue with Oracle XE the past 2 days and it's really been making me pull my hair out.
There seems to be no rhyme nor reason why this happens, or when it does, but it happened 6 ...
-3
votes
0answers
33 views
Active Directory Proxy Server [duplicate]
I've googled several times but the result is 50% null ^_^
How can I use the Active Directory to route the clients request to the Oracle Database server so that I can do the basic operation on the ...
-1
votes
3answers
69 views
How to load the Adventureworks database into Oracle?
I am trying to work through some of the examples at sqlzoo and I would like to load the example adventureworks example database into an Oracle database at home. Google search has not turned up any ...
2
votes
2answers
88 views
Using dynamic sql inside Oracle stored procedure
Assuming I have the following procedure
CREATE PROCEDURE foo (table1_id IN TABLE1.table1_id%type,
table1_val IN TABLE1.table1_value%type)
AS
SQL_UPDATE VARCHAR2(500) := ...
1
vote
0answers
30 views
Why Oracle Database trigger can became DISABLED
We have an application, using ddl database trigger on oracle database.
Trigger depends on package and several tables, package depends on user defined function.
It works on different customer sites ...
1
vote
1answer
73 views
Oracle database created whats next? Create Schema or Create Tablespace?
I'm a programmer that's being thrown to the DBA/Sysadmin island all by myself with a volleyball that I named Wilson. I'm trying to survive here.
I'm supposed to create a database for an application. ...
-2
votes
1answer
76 views
Windows Server 2008 R2, Active Directory, and Oracle Database Server
I have a group of clients, Windows server 2008 R2(Active Directory Installed in it), and an oracle database Server
I Want to know how to make the configuration of the following Diagram in this link:
...
2
votes
1answer
105 views
Why does DBMS_UTILITY.COMPILE_SCHEMA break function-based indexes?
After mass structure updates, some views/procedures became invalid.
So I just recompiled them with:
exec dbms_utility.compile_schema( USER )
But after that, all function-based indexes, which ...
2
votes
2answers
91 views
how to check which background process are running in my oracle database?
To check all background process in oracle we can see using v$bgprocess view. I want to check which background process are active. Can anybody please help ?
Thanks
4
votes
4answers
128 views
Oracle 11g delete by unique key slow
We have been plagued by a deadlock issue for quite some time that relates to deletes and inserts on the same table. It happens sporadically, but had been increasing in frequency. It would manifest ...
0
votes
1answer
32 views
using row data for column headers
I have a table
CREATE TABLE "AUTOMATION"."TESTRESULTS"
(
"BUILDNUMBER" VARCHAR2(30 BYTE),
"TESTNAME" VARCHAR2(100 BYTE),
"STATUS" VARCHAR2(100 BYTE)
)
sample data would be
...
1
vote
1answer
48 views
Summation query with complex grouping criteria
I have a database of coded actions with this structure.
{User, Date, ActionCode}
I need to generate reports based on dates and action codes, and the action codes are not clean because we have to ...
0
votes
1answer
64 views
Database creation on Linux with dbca: “command not found”
I have installed an Oracle on my machine. When I open a session with VNCServer and type dbca, either as root or as oracle, I get these errors:
-bash : -dbca : Command Not found
Xlib: connection to ...
0
votes
1answer
81 views
Ideal frequency to gather table statistics in Oracle for database of 1GB in size
I want to know what should be the ideal Frequency for Analyzing tables in a schema.
Database size 1GB.
Currently the frequency is set to 7 days, it takes around 40 mins to complete. Data volume is ...
0
votes
4answers
50 views
Selecting the first purchase item of each customer
I have a table like this:
CREATE TABLE SALES
(
CID NUMBER(4),
PDATE DATE,
ITEMCODE NUMBER(4),
ITEMNAME VARCHAR2(20)
);
INSERT INTO SALES VALUES (1111,'15-JAN-2011',4001,'LUX');
INSERT INTO SALES ...
0
votes
1answer
48 views
Efficient way to perform approximated search?
I have to perform a join between table_a and table_b, using three fields as key being one of them the date of the event, say, both tables have id1, id2 and evnt_time for eache record.
As it happens ...
