The second and terminal release of Oracle Corporations Oracle 11g database product. 11.2 was released for Linux on September 1st, 2009. New features added include RAC One Node, Improved data compression ratios (up to 20x), Edition Based Redefinition, Recursive Subquery Factoring, Instance Caging, ...
4
votes
1answer
149 views
Is there a way to export an Oracle database to a CREATE DATABASE command?
I have an existing Oracle 11 instance, I want to export the database (which was created via DBCA) to an equivalent "CREATE DATABASE" command, handling the character set, etc. I'd also like to get a ...
4
votes
1answer
119 views
How to use simple output parameters?
I'm new to Oracle, so I apologize for such a simple question, but I've already wasted several hours on it.
I have the following stored procedure:
create or replace procedure MyTestProc (outparam OUT ...
4
votes
1answer
211 views
Comments in EXPDB parameter file
Is there a way to put comments in a data pump export parameter file?
4
votes
2answers
240 views
Oracle build_plan_xml visualizer
Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML
I now have a very nice looking Execution Plan display for SQL Server (as a result of my ...
4
votes
3answers
9k views
Oracle 11g - Create Database fails?
I've just installed Oracle 11g and linked it to SQL Developer.
I want to create a database for my django install.
CREATE database 'django';
Error starting at line 1 in command:
create database ...
4
votes
1answer
399 views
Help with Query Tuning
EDIT - drachenstern: see this question for more information on this problem and on the source of his query below:
Group data by non-unique keys by distinct time range
Can someone please advise ...
4
votes
1answer
516 views
What is the difference between these two create table statements?
a)
SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE);
Table created.
SQL> desc xml_tab1;
Name Null? Type
----------------------------------------- -------- ...
4
votes
1answer
3k views
oracle format specifiers: to_number vs to_char
SQL> select TO_NUMBER(123.56,'999.9') from dual;
select TO_NUMBER(123.56,'999.9') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select TO_CHAR(123.56,'999.9') ...
4
votes
3answers
345 views
Gathering information to tune oracle row prefetching
I'm trying to determine a value for the number of rows an oracle client will prefetch (i.e. OCI_ATTR_PREFETCH_ROWS, exposed as setFetchSize in JDBC) that will minimize the number of server round trips ...
4
votes
2answers
1k views
Determine Index Compression Candidates Online
By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression:
ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE;
...
4
votes
1answer
340 views
How many control files should I have?
On the Oracle Database Appliance, the default deployment only gives you a single control file.
I find this a little puzzling. The single control file causes a policy violation in the automatically ...
4
votes
2answers
238 views
Multicolumn extended statistiscs, NLS_SORT and NLS_COMP, CHAR columns, and the bane of my existance
This is as a result of this question.
So I'm working with a query that looks like this:
select count(*)
from table1
where col1 = 123
and col2 = '1';
The problem is this: col2 is defined as a ...
4
votes
1answer
198 views
How to I interleave logs from two different Oracle tables?
I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's ...
4
votes
1answer
123 views
Enforce data integrity with a recursive table
I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
4
votes
3answers
4k views
Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes
Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
4
votes
2answers
312 views
Return a fully dynamic table from an Oracle function
I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
3
votes
4answers
1k views
Why does = NULL not work in Oracle?
I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL.
Why does = NULL not work here?
3
votes
4answers
896 views
Is it safe to delete dummy tables Oracle 11g XE
i created a database schema in 11g express edition, however there are some APEX tables and some dummy data...
Is it safe to delete them? also i would like to know what does apex means and why they ...
3
votes
3answers
267 views
What's the most cost effective or cheapest way for high availabity on Oracle Standard Edition One?
We are currently using Oracle Standard Edition One running on two physical database server.
If I wanted to get higher availability, what's the most cost effective way or cheapest way to achieve this?
...
3
votes
2answers
2k views
What is the difference between ROLE and PRIVILEGE for Oracle?
Is role a collection of certain privileges?
3
votes
2answers
196 views
Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?
I have something that looks like this (SQL Fiddle here):
Date Location Value
1/1/2012 Baltimore 36
1/1/2012 Houston 36
1/1/2012 Chicago 55
2/1/2012 Baltimore 49
...
3
votes
2answers
5k views
Using Oracle client to connect to remote database
Although I can do a telnet to remote oracle port I am unable to connect.
I have a following tnsname.ora connecting to oracle database using sql plus:
remoteOracle =
(DESCRIPTION =
(ADDRESS_LIST ...
3
votes
2answers
4k views
Which picture does the ORA-01830 error message refer to?
ORA-01830: date format picture ends before converting entire input string
About the error message.
I am confused. Which picture is the error message talking about? All I provided was a date.
3
votes
1answer
1k views
What does WE8DEC mean?
It is one of the commonly used character sets. What does it stand for?
Is it an acronym for something?
3
votes
2answers
80 views
Is there a bug with PL/Scope in combination with associative arrays?
I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something.
I have the following package
create or replace package tq84_pkg_c as
...
3
votes
1answer
3k views
Importing schema to new or different tablespace
Is there a convenient way to import a schema into Oracle 11gR2 using a single new or different tablespace than where the data originated?
As an example, I have exported BLOG_DATA from OLDDB, where ...
3
votes
1answer
90 views
Using Inteval Datatype to store result of calculation
I want to create a table which has hiredate and Interval of hiredate and sysdate. How to create a column that automaticlaly stores the calculation.
3
votes
2answers
2k views
Trouble killing sessions then immediately dropping users in Oracle 11g XE
I would like to be able to kill active sessions by a given user, and then drop that user, and do so within a single database call. Here is what I have so far:
DECLARE
cursor c is select *
...
3
votes
2answers
201 views
How to create a user with a particular user_id?
Is it possible to specify the user_id of a user while creating it?
SQL> create user user1 identified by user1;
User created.
SQL> select user_id, username from all_users where ...
3
votes
1answer
831 views
How to connect multiple users/schemas in Oracle 11g?
I'm almost new user to databases, so I think I'm doing something wrong.
I have the database created.
I created 3 users, 1 it's an Admin, 2 are just users.
I created 2 roles, 1 for the Admin and 2 ...
3
votes
1answer
566 views
How far should bind variables go in Oracle?
I am currently examining all database users's SQL code in our department for not using bind variables. We do experience an ORA-04031 error within a constant period of time.
Now, I have a quite firm ...
3
votes
1answer
283 views
Query taking long on table with low record count
I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs.
...
3
votes
2answers
492 views
Oracle 11g see transaction or session start time
I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records?
I've got a transaction id "TX-1234-abcd", a ...
3
votes
3answers
2k views
Does an Oracle nvarchar2 take twice as much space as varchar2?
Regarding point 4 of this question (which is in reference to MS SQL Server):
VARCHAR does not store Unicode characters.
NVARCHAR does store Unicode characters.
Today's applications should ...
3
votes
3answers
260 views
What are the 'free to use' workload generators available for Oracle?
A couple that I know about are
http://www.dominicgiles.com/swingbench.html and
http://hammerora.sourceforge.net/
Are there any others? I am looking for tools what can generate random workload ...
3
votes
1answer
411 views
How to find the schema name of a data pump dmp file?
I have been given a DMP data pump export file to import into my local Oracle instance. I've tried running this command line:
impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp ...
3
votes
1answer
1k views
Is there a timeout for a query over database link?
Edit/Preface:
This question has been migrated from SO as I am particularly interested on the question on timeouts on DB link queries. The provided workaround from SO is somewhat OK, but I'm really ...
3
votes
1answer
297 views
Oracle explain for cascading delete is wrong
My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it.
If I hit the explain button on a statement like this ...
3
votes
1answer
999 views
How can I delete a corrupted table space?
Problem:
On an Oracle 11.2g DB;
We have a corrupted TS that does not allow starting the database instance above mount state. The TS cannot be removed with standard methods (i.e., enterprise manager).
...
3
votes
1answer
2k views
SQL*Loader-605: Non-data dependent ORACLE error occurred
As I try to load data into Oracle, I've encountered SQL*Loader-605: Non-data dependent ORACLE error occurred error.
Data is (are?) in .txt file and its size is 95GB.
Here are some more details ...
3
votes
1answer
356 views
In a two-server dataguard setup, can I store the recovery catalog in the same database?
We currently have an Oracle 11gR2 database that is backed up with rman, with the rman records stored in the control files. Control file auto-backup is turned on. Backups are to disk (and, ultimately, ...
3
votes
2answers
323 views
Migrating to Oracle Flashback for Historization on Oracle 11g
Our application built using Oracle 11g requires that we keep historic data.
The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically ...
3
votes
1answer
139 views
Wait time differences in Oracle
I'm trying to track the different wait time classes for current Oracle sessions but I must be misunderstanding something. When I compare the sum(time_waited)/1000 for a given session in ...
3
votes
1answer
293 views
Network design considerations for Oracle Database Appliance
With the introduction of Oracle Engineered Systems the DBA is moved somewhat closer to infrastructure design decisions, and expected to at least have some opinions of the network design requirements ...
3
votes
1answer
226 views
Log rotation of diag logs?
In on of our Oracle 11.2.0 installations the directory database\diag\rdbms\$DB\$DB\alert is filled with hundreds of xml files (log.xml, log1.xml ...). Can Oracle be configured to do log rotation ...
3
votes
1answer
161 views
Oracle 11, incremental backup goes wrong
Got a pretty big instance on a one-week backup cycle with an incremental (differential) strategy .. or rather, that's what it was supposed to be. Somehow the daily differentials seem to come out as ...
3
votes
2answers
170 views
Oracle extents - How can I have so few with this large size?
Note on duplication:
This is not a duplicate of "Oracle extents - How can I have so many with this small size?" This is a separate question I entered which happens to be in the same domain.
This is ...
3
votes
2answers
468 views
Oracle extents - How can I have so many with this small size?
This is a different question than the one with the almost-identical title.
Again, this is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents ...
3
votes
2answers
757 views
Create unique DB_NAME and DB_DOMAIN for an Oracle 11g instance
I am somewhat confused by these terms in the init.ora file:
db_domain
db_name
instance_name
plus the database name which is provided to the create database command. I do not completely understand ...
3
votes
1answer
2k views
Oracle 11g hunting down the deadlock, maybe foreign key?
I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only ...
