Tag Info

Hot answers tagged

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 ...


13

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 ...


11

There is an exception. When you define 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 define 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, 3 ...


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

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 ...


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

Tom Kyte has a number of different solutions to generating flat files from Oracle on his site. There is a PL/SQL implementation using UTL_FILE as well as a Pro*C SQL unloader application.


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

You would do this with the RETURNING clause in your first INSERT statement. UPDATE: Happened to write about this in my blog recently.


8

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 ...


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 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, ...


7

I think it goes like this DECLARE ENABLED_USER_ID PLS_INTEGER; DISABLED_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', ...


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

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 ...


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

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

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

This works fine: select regexp_substr(':123:456:', '(\d+):', 1, 2, 'i', 1) from dual; I think yours fails because the opening and closing colons won't get matched by both occurrences (because the first match is greedy).


6

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 ...


6

This would be the equivalent in SQL Server syntax. Based on my reading of the Oracle docs, NULLIF and PIVOT appear to have the same format as their SQL Server kin. The challenge will be the pivot list which needs to be static unless you make the query dynamic as Itzik demonstrates but I have no idea if that can be translated to P/SQL WITH ...


6

Type: lsnrctl start On your Unix box. That starts the listener. If you still can't connect after that it's either a firewall issue or the listener isn't configured.


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 ...


6

Bit late to the party on this one... The database should register with the listener automatically, making the SID_LIST entries redundant, and this seems to be happening with your 192.168.111.111 environment. If the listener is started after the database it can take a while for it to register, and there may be situations where it doesn't do so at all. You ...


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

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

SELECT seq.nextval INTO ENABLED_USER_ID FROM dual;


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

In general, no. A tnsnames.ora change shouldn't require a reboot but some applications will read and parse the tnsnames.ora at startup to be able to present a drop-down list of servers to the user, for example, and will cache whatever was read when the application started up rather than re-reading the file. Depending on the situation, it might be easiest ...



Only top voted, non community-wiki answers of a minimum length are eligible