Hot answers tagged oracle-11g-r2
23
SYS:
automatically created when Oracle database is installed
automatically granted the DBA role
has a default password: CHANGE_ON_INSTALL (make sure you change it)
owns the base tables and views for the database data dictionary
the default schema when you connect as SYSDBA
Tables in the SYS schema are manipulated only by the database. They should never ...
13
Commants to the local instance execute on return. Multi-line commands to the server execute on semicolon
Special commands as detailed in the SQL*Plus manual are the only ones that do not accept semi-colons. Wheras SQL Commands must end with a ; in order to be parsed by the server.
10
At the sites I've worked at, any changes that need to be made to the production instance(s) must be scripted as change scripts that will run in SQL*Plus; in addition, scripts needed to recreate all schema objects from scratch must be kept up-to-date. All these scripts are checked into change control, and are migrated from there.
You can audit DDL changes or ...
10
No.
The only thing that guarantees result set order is an ORDER BY clause in your query.
This is a popular question about SQL so it's worth repeating what I've written in response to similar questions about SQL Server and MySQL:
In the SQL world, order is not an inherent property of a set of data.
Thus, you get no guarantees from your RDBMS that ...
10
The aim when shutting down for maintenance (or cold backup) is that the database is left in a consistent state with no need for rollback/recovery on startup.
There are 3 SQL*Plus shutdown commands that achieve this in theory, all of which immediately prevent new sessions connecting to the instance:
shutdown normal or just shutdown - waits for all sessions ...
9
From the 11g Oracle Documentation:
SYS AND SYSTEM Users
The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the DBA role.
SYS
This account can perform all administrative functions. All ...
9
Take a look at PSOUG's notes on NULL. As Fabricio Araujo hinted, NULL is not really a value like the number 4 or string 'bacon strips'. In fact, NULL is untyped in the SQL language, which is why you cannot validly use it in an equality comparison. You need the special IS [NOT] NULL syntax to check if a value is NULL or not.
9
Use a view that excludes the virtual columns to do the manipulation. I've just tested this & it works:
create view v_tq84_virtual_test_with as ( select col_1, col_2, col_3, col_4 from tq84_virtual_test_with );
declare
r v_tq84_virtual_test_with%rowtype;
begin
select * into r from v_tq84_virtual_test_with where col_2 = 8;
r.col_4 := r.col_4 - 2;
...
9
Firstly, you must enable flashback. In SQL*Plus as SYSDBA:
shutdown immediate;
startup mount;
alter database flashback on;
shutdown immediate;
startup;
Next before your load operation, create a restore point:
create restore point before_load;
Now do you work, and when you're done:
shutdown immediate;
startup mount;
flashback database to restore point ...
8
You can use regular expressions and regexp_replace to remove the duplicates after concatenation with listagg:
SELECT Num1,
RTRIM(
REGEXP_REPLACE(
(listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()),
'([^-]*)(-\1)+($|-)',
'\1\3'),
'-') Num2s
FROM ListAggTest;
This could be tidier if ...
8
It is not completely clear what your after with your question. I assume you want to know if and importantly when an alter index .. coalesce was performed.
I think without (an in other answers already mentioned auditing setup) it is impossible to get such a date (although I would want to know if someone knows better).
That said, the effects of an alter ...
8
Back in the days when I worked in an Oracle shop, we had a specific 'dev' (development) server, which had different security restrictions than the 'prod' (production) server. Developers could do whatever they needed, and then we'd hand off the necessary scripts to the DBA to apply to the production server.
In the case of our critical systems (SCT Banner, ...
8
Consider the following scenario:
There is a Unix user named gaius on the Oracle server with external authentication, so in Oracle there is a corresponding user called ops$gaius. When logged into a shell, I can also log straight into my Oracle schema, and my cron jobs don't need a password embedded in script either.
Remote OS authentication is permitted, ...
8
Its an interesting question, to be sure. Most people who are familiar with Oracle development wouldn't give it a thought but when you come down to it, its sometimes confusing to define the demarcation between SQL and PL/SQL.
By looking at the definition of the acronyms, you start to get an idea of what areas of functionality each covers:
SQL - Structured ...
8
The Oracle Concepts Guide for 11.2 says the following (The note is of particular interest):
Globalization Support Environment
The globalization support environment
includes the client application and
the database. You can control
language-dependent operations by
setting parameters and environment
variables on the client and server,
which ...
8
AVG and other aggregate functions work on sets of data. The WHERE cause does not have access to the entire set, only to data for the row it is operating on. If you created your own AVG function (as a normal function and not a custom aggregate function) it would only be passed one ID value when called from the WHERE clause not the entire set of ID values.
...
8
This is a resource issue.
The DB server cannot satisfy your queries due to the client host being configured incorrectly as-per the pre-requisites for an Oracle database client installation.
Ask your system administrators to verify that they have set the required number of open files, semaphores, shmax etc etc. Link to the Oracle documentation - I assume ...
8
You can do this with DBMS_LOCK and an exclusive lock.
See the following procedure:
CREATE OR REPLACE PROCEDURE myproc
IS
lockhandle VARCHAR2(128);
retcode NUMBER;
BEGIN
DBMS_LOCK.ALLOCATE_UNIQUE('myproclock',lockhandle);
retcode:=DBMS_LOCK.REQUEST(lockhandle,timeout=>0, lockmode=>DBMS_LOCK.x_mode);
IF retcode<>0
THEN
...
8
The first thing I'd do is check the stats for the columns individually with these queries:
select count(*) from table1 where col1 = 123;
select count(*) from table1 where col2 = '1';
If the estimated cardinalities here agree much better with the actual row counts, it means there is some correlation between the columns that the CBO cannot guess based on ...
7
It increases single points of failure and enlarges the risk surface of your data.
An attacker who gains access to the system will, with OS Authentication, have access to the database. By requiring more secure access to the database, the potential attacker must escalate their privileges on the compromised system to gain root or oracle access, rather than any ...
7
If it is wrapped in
BEGIN ... END
DECLARE ... END
CREATE OR REPLACE ... END
Is a one-liner prefixed EXECUTE
Then it is PL/SQL. What does this mean under the hood? SQL gets "compiled" to a query plan and executed, immediately returning a result set in the event of SELECT or the number of rows affected in other cases, or an error. PL/SQL however is more ...
7
When your OS user is from a DBA group, you can connect AS SYSDBA with OS authentication:
Two special operating system groups control database administrator
connections when using operating system authentication. These groups
are generically referred to as OSDBA and OSOPER. The groups are
created and assigned specific names as part of the database
...
7
This part is unusual:
ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/
This needs to be char-by-char identical every time. I see it ends with a slash here, which is non-standard, and I suspect that you've added it by mistake. Adding a slash will result in "ORACLE not available" in so-called bequeath sqlplus, I've just experimented on my system. Try again ...
7
The documentation states the size of datatypes here.
0 (the number) is stored as 1 byte. Other single digit numbers will be stored in 2 bytes (one for the exponent, one for the mantissa). You can test this yourself by creating a test table and using the VSIZE() function on test data (doc link).
A CHAR(n) will be stored in n bytes.
I'd always store numbers ...
7
I personally think it's a matter of taste.
The scripts where the constraints are defined through an ALTER statement are a bit more flexible, as you don't need to care about the order of creation (first create all tables, then all PKs, then all FKs).
The scripts with embedded constraints are more "self-contained" however. You don't need to look for other ...
6
So the way I do this is I don't use default values. Or rather, I set them to NULL as default. Then within my procedure body I check to see if it's equal to NULL and if so set it to a sane default. At that point you could mangle a flag or two in the same space to see what's being set or not set.
However, there's the edge case of if they intended to pass in ...
6
The second statement is creating an object table.
It is almost never the case that you really want to use an object table. That was something that was introduced in the 8i time frame when Oracle was making the database object oriented. While a lot of the object oriented PL/SQL enhancements have been useful, using object types in SQL is not something ...
6
As far as I can see, with the currently available language specification, this is
the shortest to achieve what you want if it must be done with listagg.
select distinct
a.Num1,
b.num2s
from listaggtest a cross join (
select listagg(num2d, '-') within group (order by num2d) num2s
from (
select distinct Num2 num2d from ...
6
This is the offending code causing your query to take hours to return results:
and not exists (
select null
from pa q
where q.PROJect_NBR = p.project_nbr
and q.status <> p.status
and q.aud_timestamp < ...
6
You're not seeing the benefits of parallel execution because both insert methods use single-row inserts, hence parallel does not kick in !
Parallel execution is only available for bulk operations. Lots of tiny operations doesn't qualify as a set operation. You're inserting rows one by one here, you should try it with a bulk operation (INSERT /*+APPEND*/ ...
Only top voted, non community-wiki answers of a minimum length are eligible
