Oracle 11g is the July 11th, 2007 release of Oracle’s primary database product. New features added in 11.0.1 include Virtual Columns, Automatic Memory Management and Interval Partitioning.
-2
votes
1answer
49 views
what's the fastest method to export a table through sql query ? 11g - performance
I'm connected to the oracle server remotely. I need to export a table data(the whole table) with millions of records but due the hosting restrictions I can only use SQL (no exp , expdp) . Therefore ...
1
vote
1answer
132 views
oracle streams apply: how to get a reason why LCR message was not applied
I've set up bidirectional oracle streams replication (11gR1) using identical scripts on both machines (DB1 and DB2). Although changes from DB1 are being applied to DB2, changes from DB2 to DB1 aren't.
...
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 ...
0
votes
1answer
78 views
What's the most effective way to back-up / save data on Oracle 11g large data sets?
We have a database(actually more databases split by primary access path, modulo on a key.) with millions of records (about 400 millions ). Right now I need to save a specific table userData(which has ...
4
votes
1answer
95 views
Designing Simple Schema for Disaggregation of Demand Forecast
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw ...
0
votes
0answers
32 views
Passing parameters to a procedure executed by DBMS_SCHEDULER
I have a scheduler program that runs a stored procedure that requires input parameters.
BEGIN
DBMS_SCHEDULER.DROP_PROGRAM
(program_name => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
...
1
vote
1answer
72 views
grant execute on utl_file to user in Oracle 11g XE
sqlplus connect <sys>/<pwd>
grant execute on utl_file to <user>
connect <user>/<pwd>
DESC utl_file
then i get:
ORA-24372: invalid object for describe
How can I access ...
1
vote
1answer
77 views
Unable to start Oracle Database Server : Get an error as I try to start it
I just installed Oracle Database Express Edition 11g Release 2 for windows.It created a short cut icon on the desktop :
but as I click this icon I see this dialog box :
What is it ? How do I ...
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
46 views
Why we need auxiliary instance in TSPITR
Need help!
I've read Database Backup and Recovery User's Guide 11g but I can't uderstand why we need auxiliary instance to perform tspitr.
We can do complete recovery of tablespace within same ...
0
votes
3answers
1k views
Convert a Unix timestamp to a DATETIME in a View
I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out.
...
1
vote
1answer
125 views
Oracle Forms 10g to 11g migration Error
I having problems from first steps, while migrating Oracle Forms from 10g to 11g. Some of libraries dose not want to compile. the compiler just hangs up when I try to compile them, and Forms builder ...
0
votes
0answers
82 views
pl/sql developer show errors with arabic letter
I have pl/sql on my PC but when an error occurred while I write an incorrect sql query the oracle error message show with Arabic letter as below
ORA-00936:???? ????? ??????
I am using oracle-client ...
0
votes
0answers
115 views
Error AWR Operation failed: CATPROC not valid when creating database with dbca
I have a fresh installation of Oracle 11g enterprise edition on CentOS 6, both 64 bits. I have installed Oracle and so far everything looks fine. I have already added an started a listener. The ...
3
votes
4answers
3k views
Simplified/automated datafile free disk space reclaim
On Oracle version 11g:
After Googling, I can't find a simple way to reclaim free space after deleting a table.
I've found a lot of explanations, telling how the datafile becomes fragmented, the big ...
1
vote
0answers
138 views
Registry error while trying to install Oracle 11g on Win 2008 R2
I am new to Oracle and trying to install Oracle database 11g Enterprise Edition on Windows 2008 Server R2 but getting this error all the time...
It gives me error when updating registry key ...
1
vote
1answer
114 views
Update oracle sql database from CSV
I tried google at first but no luck.
Is it possible to update tables from csv file?
I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the ...
0
votes
0answers
48 views
extproc env variables oracle 11g
I have oracle 11g with extproc separately configured in listener.ora.
Users report some environmental variables that should be exported are not set.
From where does extproc gets it environment ...
4
votes
3answers
204 views
Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)
I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job;
alter system set memory_max_target = 512M scope = spfile;
Which I guess ...
0
votes
0answers
39 views
No network ACL, yet my user can successfully connect to external server
This one's had both me and our dba scratching our heads.
We have an Oracle 11.1.0.7.0 instance, DEV, with a user who can successfully connect to our ldap server, e.g.:
DECLARE
l_session ...
2
votes
1answer
213 views
unique constraint violated (ORA-00001) in concurrent insert [closed]
I have a procedure that is called from concurrent transactions:
//Some actions here
INSERT INTO table1
(table1_id, table1_val1, table1_val2, table1_val3)
VALUES
...
4
votes
1answer
960 views
Oracle 11g listener fails with ORA-12514 and ORA-12505 errors
I run an instance of Oracle 11g locally on my development machine and can connect to the local instance directly via SqlPlus:
c:\>sqlplus ace
SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar ...
15
votes
3answers
15k views
Can I change the SID of an Oracle database?
The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
Is there an easy & quick way to change the SIDs of the test databases on the server?
Dropping & recreating of ...
2
votes
1answer
68 views
Fast paginated result, for various filter clauses
I have been working on obtaining a paginated result from a table in my database (11g). While I have a query that does work (ie, the result is correct) it does not perform as well as I'd like to and I ...
1
vote
1answer
1k views
Oracle server connection reset by peer socket write error
Full disclosure, I'm not a database admin, but if anyone is able to help me, it would be you guys.
Our Oracle 11g database server stopped working, I'm not aware of anything that could have caused it, ...
1
vote
1answer
978 views
Oracle.exe (SHAD) process keeps increasing
I have an Oracle Server 11g that we use for integration tests. For some reason, the processes count [select count(*) from v$process;] keeps increasing up to the limit, causing ORA-12514: TNS:listener ...
1
vote
1answer
178 views
Oracle 11G Can't create database from template
I am attempting to create a new database from an existing template in Oracle 11G.
I have done this before using dbca and haven't had a problem, but now I am hitting the following error:
TNS-04404: no ...
3
votes
3answers
2k views
cursor: pin S wait on X in 11g
This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution.
Any advice will be ...
4
votes
1answer
521 views
temporary table inside procedure oracle
I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g.
Mysql procedures allow me to:
1. Create temporary tables
2. Insert data into temporary tables / Query these ...
0
votes
1answer
649 views
How and when to use sys_refcursor in oracle
Can some one give me a small explanation about how and when should someone use sys_refcursor ?
3
votes
1answer
90 views
Use a Context namespace in a multi-tenant database?
I'm developing an Apex application that uses database Contexts in conjunction with Row Level Security. I'd like to install a second copy of the schema and workspace on the same instance; this works ...
0
votes
0answers
81 views
Renaming the system generated partitions during interval partitioning
I am trying to rename the system genrated partition names(generated during interval partitioning). I try to do this while my table is live(data read and write is happening).
I need two scripts.
I ...
2
votes
1answer
48 views
Transaction stays in capture queue
I set up simple bidirectional db replication using Oracle Streams. I used enterprise manager for this. Streams are active and their status is ok. The problem is that the replication is very slow. What ...
4
votes
2answers
730 views
Where can I find autoextend events?
In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?
0
votes
4answers
2k views
What's a good client tool to access Oracle RDBMS? [closed]
I'm looking for a good client tool to replace "Oracle SQL Developer" (the tool I currently use) which supports:
Oracle DB 10g, 10gR2, 11g
Running SQL code
Debugging SQL code (with walk-through... ...
1
vote
0answers
112 views
PeopleSoft or Oracle App Designer Tool Alternative
We are using PeopleSoft app designer tool to manage and create projects, database objects - tables, columns, views etc in Oracle database. This tool has limitation that we cannot create tables/columns ...
2
votes
1answer
484 views
Rename interval partitioning system generated partition names
I have an existing table which I partitioned (interval partitioning on a daily basis) using dbms_redefnition.
PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "p1_1" ...
1
vote
1answer
279 views
Indexing issues while partitioning with dbms_redefinition
I get an error while doing the redefinition. Following are the steps which I have taken so far:
Step 1. Start redefinition
DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'T_FDC_TOOLCONTEXT',
...
6
votes
3answers
422 views
ALTER SYSTEM DISCONNECT SESSION doesn't work
Oracle 11g. The devs leave sessions connected overnight, sometimes certain cron jobs fail because of that. I am trying to kill all user sessions, but I can't.
I login as:
sqlplus system/[password] ...
4
votes
3answers
274 views
SQL Server pivot query on Oracle source
I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:
╔══════════╦══════════╗
║ ...
3
votes
2answers
1k views
How to return a CTE as REFCURSOR from an Oracle stored procedure?
I tried to modify an Oracle stored procedure to use a CTE instead of a simple Select Statement.
My procedure looked like this:
Create or replace Myproc ( MyRefCursor IN OUT SYS_REFCURSOR)
as
begin
...
1
vote
1answer
609 views
Delete Archive log file
I am using oracle database 11g. Some day's back we have deleted our Archivelog Manually, and now it's getting full and due to that we are not able to connect to our database.
So how should i delete ...
0
votes
1answer
702 views
db_recovery_file_dest_size issue
RMAN gives me this error how should i resolve this issue:
ORA-19815: WARNING: db_recovery_file_dest_size of 7516192768 bytes is 100.00 used, and has 0 remaining bytes available.
Thanks in ...
2
votes
1answer
97 views
Generating an arithmetic sequence
I would like to generate a named table in a WITH clause which will contain this arithmetic progression:
╔══════════╗
║ mynumber ║
╠══════════╣
║ 0 ║
║ 30 ║
║ 60 ║
║ 90 ║
║ ...
0
votes
1answer
396 views
check listener status other than 1521
I have two listner:
one is running on port 1521 another on 1531.
lsnrctl stat show's status of services running on port 1521 only.
How should i check whether the services running on port 1531 is ...
3
votes
2answers
842 views
Oracle 11g setup closes without errors on pre-requisite check
I'm installing Oracle 11g and the setup wizard just closes automatically after the pre-requisite check.
I have administrative access
UAC is disabled
IPv6 is diabled
There is more than enough free ...
2
votes
1answer
764 views
After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail
I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
3
votes
2answers
123 views
Support ticket system - when/how to move old tickets?
I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting ...
-2
votes
1answer
62 views
Best indexing for users table [closed]
This scenario occurs so many times (for almost every website) that I thought I would ask here:
What would be best indexing for a users table:
Edit: I think index on email-addresses and username may ...
7
votes
1answer
631 views
Why am I NOT getting a mutating table error in trigger?
It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation:
A mutating table is a table that is being modified ...
