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


15

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

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


11

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


11

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


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


11

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


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

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

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


8

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


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

So, with thanks to @YasirArsanukaev for the time he put in, I have found a solution which works, but which I can't really explain. Riffing on the LOCAL_LISTENER thought, I was reading this other answer where it said: The database uses the LOCAL_LISTENER parameter to identify the listener it should register with. By default that is null, which ...


7

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


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

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


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

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

SELECT seq.nextval INTO ENABLED_USER_ID FROM dual;


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

the result of the expression to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(1244108886,'SECOND') is a value of the oracle datatype DATE. This type contains date and time. If you want to display this value to a user you must convert it to a string. This conversion can be done by an application or you can let oracle convert it to a string type as it ...


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

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


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

A cursor is a pointer to a result set for a query. By returning a sys_refcursor you allow the client to fetch as many or few of the rows from the query as it requires. In stateful applications this could be used to page through results. A cursor can allow more flexibility than writing a PL/SQL function that returns an array as it's completely up to the ...


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



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