Hot answers tagged oracle-11g
17
You need to recreate the control file
This post by Kaunain Ahmed describes the necessary steps:
do: alter database backup controlfile to trace;
extract the "create controlfile" command from the
background-dump-destination tracefile.
shutdown the DB.
Change the DB-Name in your init.ora and change the init.ora
Change the SID in the ...
12
The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:
alter session set nls_date_format = 'yyyy-mm-dd';
drop table tq84_partitioned_table;
create table tq84_partitioned_table (
status varchar2(1) not null check (status in ('A', 'B')),
date_a date not null,
date_b ...
10
The classic solution to this is to partition your tables, e.g. by month or by week. If you have not come across them before, a partitioned table is like several identically structured tables with an implicit UNION when selecting, and Oracle will automatically store a row in the appropriate partition when inserting it based on the partitioning criteria. You ...
10
Since 9i dbnewid utility (nid) can be used to change database name
(and DBID if required).
If database name being changed only then resetlogs is not required:
1. startup database in mount mode
shutdown immediate
startup mount
2. run nid to change database name:
nid target=sys/syspassword@dbtns dbname=newname setname=YES
3. shutdown and ...
10
30-90 minutes according to Oracle's Best Practices for Upgrading. This is about the closest estimate you will get given all the unknowns in this situation.
The size of the database really matters very little in determining how long the upgrade will take. Here are the main factors effecting the duration (from the Oracle.com upgrade blog):
Number of ...
9
The difference is that -- and /* */ can be used in a PL/SQL block, while REM[ARK] cannot. The following will work in SQL*Plus:
REM comment
-- comment
/* comment */
begin
DBMS_OUTPUT.PUT_LINE('Test'); --comment
DBMS_OUTPUT.PUT_LINE('Test'); /* comment */
end;
/
These will not:
begin
DBMS_OUTPUT.PUT_LINE('Test'); REM comment
end;
/
begin
...
8
Your problem might very well have to do with incorrect versions or wrong default connection. Make sure that you use the exp utility from the 10g installation and the imp utility from the 11g installation.
Also make sure that the environment variables like ORACLE_HOME are correct and that PATH is adjusted for the correct ORACLE_HOME/bin
How does your connect ...
8
First, are you using "database" in the Oracle sense of the term? Or are you using it in the sense that other database vendors (such as SQL Server or MySQL) use the term?
If you are using "database" in the Oracle sense, that would be the size of the SYSTEM and SYSAUX tablespaces at a minimum and would possibly include the size of the UNDO and TEMP ...
8
There is an exception. When you have defined a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised, but if you have the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:
SQL> create table TB_TR_TEST(
2 col1 number,
...
7
You would do this with the RETURNING clause in your first INSERT statement.
UPDATE: Happened to write about this in my blog recently.
7
A 10g file can be loaded into an 11g database. It is throwing a networking error and TNSPING doesn't go all the way to the database, just as far as the listener port. I suspect you'd have the same trouble connecting via SQLPLUS.
Check your tnsnames.ora and that the database and listener are available
7
There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE.
Aggregate /Case version:
select personid,
max(case when optionid = 'A' then 1 else 0 end) OptionA,
max(case when optionid = 'B' then 1 else 0 end) OptionB,
...
6
Yes, you can and it is quite easy too.
In Oracle, the ORACLE_SID is just the name for the Oracle Instance and has not very much to do with the DBNAME. A database with the name PROD, can be served using Instances with any valid name. There is no direct connection between the SID and the DBNAME. This connection is made using the parameters.
The parameter ...
6
You will need a block if you are declaring variables
With 11g, support for sequences has been improved so you can use them like:
ENABLED_USER_ID := SEQ.NEXTVAL;
rather than using a select statement (though both will work)
Other options for persisting the values include saving them to a table or creating a context, but I think sequence.currval is really ...
6
I think it goes like this
DECLARE
ENABLED_USER_ID PLS_INTEGER;
ENABLED_USER_ID PLS_INTEGER;
BEGIN
ENABLED_USER_ID := SEQ.NEXTVAL;
DISABLED_USER_ID := SEQ.NEXTVAL;
INSERT INTO USERS (ID, USR_NAME)
VALUES (ENABLED_USER_ID, 'ANDREW');
INSERT INTO CAR (CAR_ID, CAR_NAME, USR_ID)
VALUES (CARSEQ.NEXTVAL, 'FORD', ...
6
Are you really trying to use Windows 8? No version of Oracle is supported on Windows 8 yet and, if history is any guide, I wouldn't expect any of them to work without patches that aren't available yet. Is Windows 8 even in public beta yet? I'd hate to try to take a class using an early beta operating system.
Oracle 11.2 is supported on Windows 7 (which ...
6
I don't see how this query could ever have returned the current day. ROWNUM starts with 1 so TRUNC(sysdate - rownum) will never return the current day and neither will TRUNC(sysdate + rownum). Both sides of your UNION return exactly 32 rows so the entire query should always return 64 rows. If you want 32 days before today, 32 days after today, and today ...
5
Welcome to the world of Database Administration... and good luck. You're going to want to read up on as much Oracle documentation as you can, as well as other good technical sources (O'Reilly has always been good), and subscribe to lots of Oracle blogs.
I'll answer your questions here, but you're really going to want to get a solid foundation in RMAN ( For ...
5
You should look at the built in UTL_FILE package. There are several ways you could use it.
You could write any number of procedures in packages that use the UTL_FILE package to write to any number of files. These procedures can then be called from almost any application including SQL*Plus.
You could write a PL/SQL script to do the same work and call the ...
5
Why does a wait event that occurred 5 times accounts for 1.1% of your database wait time concern you as a potential threat? Is there some additional information that leads you to believe that this is a threat? Obviously, something has to be in your top 5 wait events. Is this event consistently one of the top wait events? Or did it just appear on one AWR?
...
5
You will have to examine how the question is worded. By default 11g will create all three tablespaces. You can however create a database with only SYSTEM and SYSAUX. Technically speaking you can upgrade pre-10g database and not add SYSAUX, or remove the SYSAUX tablespace after the database is created and therefore have a database with only a SYSTEM ...
5
Presumably, you have a development and test instance of this database running on similar hardware with a similar data volume and the same database components installed, correct? And, presumably, you will be upgrading these lower environments (and testing that whatever applications use this database still function correctly), correct?
Assuming that is the ...
5
To do this, you'll be wanting to use the 11.1 Data Pump (expdp/impdp) rather than exp/imp. With Data Pump you export using the higher version export utility with the VERSION= parameter.
For example:
expdp scott/tiger version=10.1 directory=DUMPDIR dumpfile=DUMPFILE.dmp logfile=DUMP.log
Don't forget to create the directory (using CREATE DIRECTORY DUMPDIR ...
5
You have the basics right. There is only one type of commit (no normal, fast...).
from the concepts doc:
When a transaction commits, the following actions occur:
A system change number (SCN) is generated for the COMMIT.
The internal transaction table for the associated undo tablespace
records that the transaction has committed. The ...
5
What is the problem with the table becoming large? Generally, any sort of OLTP query will access the table using an appropriate index in which case the size of the table is more or less irrelevant. The cost of using an index will grow at an O(log(n)) rate-- practically, a b*-tree index will only add one or two levels for any realistically sized table. And ...
5
You can use a CONNECT BY query to generate an arbitrary sequence:
SQL> variable v_step NUMBER
SQL> variable v_maxnumber NUMBER
SQL> exec :v_step := 30; :v_maxnumber := 211;
PL/SQL procedure successfully completed
SQL> WITH sequenceTable AS (
2 SELECT (LEVEL - 1) * :v_step myNumber
3 FROM DUAL
4 CONNECT BY (LEVEL - 1) * ...
4
One aspect to consider is how much of the delete performance result from indexes and how much from the raw table. Every record deleted from the table requires the same deletion of the row from every btree index. If you've got 30+ btree indexes, I suspect most of your time is spent in index maintenance.
This has an impact on the usefulness of partitioning. ...
4
It is a weird function with a weird history - but then so is nvl2 weird. lnnvl is basically an is not true operator - no doubt it can be put to good use like nvl2 can, but when you have to look a function up every time you use it to remind you exactly what it does, you are left wondering if it is best to stick to nvl, coalesce, decode and nullif along with ...
4
Oracle says about Indexes and Index-Organized Tables under Full Index Scan: In a full index scan, the database reads the entire index in order.
Yet, unter Fast Full Index Scan, it reads: A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index ...
4
I'm not aware of a particular method that records this, the information doesn't appear in file$ or x$kccfe . In general though I wouldn't worry about these events so much as how much free space I have in both the tablespace and the file system. There are alternative methods to monitor this
Updated At Leigh's suggestion
In answer to Gaius comment - but too ...
Only top voted, non community-wiki answers of a minimum length are eligible
