Hot answers tagged oracle-11g
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
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
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
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 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
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 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
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 ...
4
I prefer to pivot query manually, but you may use PIVOT as well.
SELECT PersonID,
MAX(CASE WHEN OptionId ='A' THEN 1 END) AS OptionA,
MAX(CASE WHEN OptionId ='B' THEN 1 END) AS OptionB,
MAX(CASE WHEN OptionId ='C' THEN 1 END) AS OptionC
FROM PersonOptions
GROUP BY PersonID
4
Run:
lsnrctl status LISTENERNAME
... where LISTENERNAME is the name of the listener that is listening on port 1531. You can get this name from your $ORACLE_HOME/network/admin/listener.ora file.
For example:
LISTENER1531 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1531))
...
4
If you start your instance using a server parameter file (a binary version of initialization parameter file, spfile), you can extract the initialization parameters to plain-text initialization parameter file (pfile), alter them, and then start your instance with modified memory parameters.
sql> create pfile='myinit.ora' from spfile='spfileORCL.ora';
...
3
That's expected, most DDL operations on partitions will invalidate the indexes affected by the DDL. The ALTER TABLE docs state that on all relevant operations.
Specifically for truncate partition:
For each partition or subpartition truncated, Oracle Database also truncates corresponding local index partitions and subpartitions. If those index partitions ...
3
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 ...
3
Using a join?
SELECT
minion.boss,
emp.name,
minion.minion
FROM acg_employees emp
JOIN
(SELECT b_id as boss,
LTRIM(MAX(SYS_CONNECT_BY_PATH(e_id,','))
KEEP (DENSE_RANK LAST ORDER BY curr),',') as minion
FROM (SELECT b_id,
e_id,
ROW_NUMBER() OVER (PARTITION BY b_id ORDER ...
3
If even an ALTER SYSTEM KILL... does not remove the session after a bit, then the only thing you are left with is to kill it on the OS level. In Linux you can get the command to kill the process as follows:
SELECT 'kill ' || ' -9 ' || p.spid
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid=:SID_To_Kill;
In Windows you can get the ...
3
You're not supplying a correct connection string. If you're using ezconnect, you need to pass the connection string in the format: //host:port/service-name
The expdp command-line should be:
expdp user/pass@//host:port/service-name full=Y VERSION=10.2 directory=m_dump dumpfile=DB10G.dmp logfile=expdpDB10G.log
So, something like:
expdp ...
3
Another option (short of full auditing) is a servererror trigger for the schema in question (or the whole database).
drop table error_log;
create table error_log (error_time timestamp, username varchar(50), msg varchar(4000), stmt varchar(4000));
CREATE OR REPLACE TRIGGER servererror_trigger
AFTER SERVERERROR
ON SCHEMA
declare
sql_text ...
3
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 ...
3
It depends on why you are creating the temporary tables in MySQL.
Frequently, people that are creating temporary tables in other databases are doing so in order to work around limitations that don't exist in Oracle where readers don't block writers and writers don't block readers. In other databases, you commonly copy data from a permanent table to a ...
3
I believe you should be aiming for a plan that avoid any actual sort operation, and "stops short" as soon as possible.
To avoid the sort (and "materializing" the inner view), your sort order must match exactly the index columns, or your where clauses must be strict equals only on all the leading columns. Otherwise there will be a need to sort subsets, and ...
3
11g has the pivot construct, which appears to meet your needs:
with pivot_data as (
select buildnumber, testname, status
from testresults)
select * from pivot_data
pivot (min(status)
for buildnumber
in ('1' as build_1, '2' as build_2, '3' as build_3)
)
order by 1;
Note the min(status), the pivot requires an aggregate function, ...
2
Look at these lines :
CREATE TABLE DEPARTMAN
( DEPARTMAN_ID NUMBER(*, 20) NOT NULL,
DEPARTMAN_AD VARCHAR2(40) NOT NULL,
DEPARTMAN_SOYAD VARCHAR2(40) NOT NULL),
CONSTRAINT DEPARTMAN_PK PRIMARY KEY (DEPARTMAN_ID)
);
You have an extra left parenthesis before the CONSTRAINT. After you remove it, it should look like this:
CREATE TABLE DEPARTMAN
( ...
2
The psuedo-column ORA_ROWSCN is assigned at commit time and can be used to identify the order of commits, with the following caveats:
The table must be created with the ROWDEPENDENCIES clause. If this isn't done then commits will be at the block level, not the row level. You cannot alter a table to have this, you must drop and re-create it
Updated data ...
2
Why are you migrating to MySQL? Did you consider PostgreSQL? I only ask because PostgreSQL is pretty much feature-compatible with Oracle, and would make a migration an order of magnitude simpler.
That said, translating 80 table definitions and 500 queries will probably take a month or so. Simple CRUD statements will be limited to the expert's typing ...
2
Getters and Setters implies retrieving the data for one row and changing the data for one row. It sounds like you are planning to call a get procedure for each row you want in the file level table and then for each of those rows loop through getting the corresponding data from the batch level table etc.
My suggestion is that you write one SQL statement ...
2
Check the Oracle Utilities documentation. When using Datapump tools, you can reset paths using the REMAP_DATAFILE parameter for import jobs.
Also, insure your OS session variables reflect the new DB. This is often over-looked when working with a new DB on a server that already has a DB working on it.
2
select 'LOG_1' AS LOG_SOURCE, TIME_CREATED,ENTRY_TEXT
FROM LOG_1
UNION ALL
select 'LOG_2', TIME_CREATED,ENTRY_TEXT
FROM LOG_2
ORDER BY 2
I formalize this kind of logic as a view so you can document and comment it.
CREATE OR REPLACE FORCE VIEW COMBINED_LOGS
(LOG_SOURCE,
TIME_CREATED,
ENTRY_TEXT ) AS
...
2
After surfing google for days i have found the most simple and clear example to reclaim the free space in the tablespace after delete. I hope this helps
Link: http://www.dbforums.com/oracle/976248-how-reduce-tablespaces-used-space-after-delete-records-2.html
solution:
ALTER TABLE MOVE demo
Let's create a table with 9999 rows in it, each sized around ...
2
Here is Oracle's answer.
Basically your options are these:
Delete transient files no longer needed for current policies.
Change something stored in the Fast Recovery Area to be stored elsewhere This could be temporarily or permanently.
Increase the db_recovery_file_dest_size to be larger than 7 GB.
Decrease your backup window or redundancy.
Change your ...
Only top voted, non community-wiki answers of a minimum length are eligible
