Tag Info

Hot answers tagged

27

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


15

Oracle is not like MySQL in this. Like most other RDBMSs it comes with it's own built-in storage engine that cannot be exchanged for another.


14

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.


14

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


11

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


11

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


11

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


11

Your kernel parameters need modifying. Edit /etc/sysctl.conf and ensure the following lines are present: kernel.shmall = 18350080 kernel.shmmax = 75161927680 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 Then reboot the machine.


10

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.


10

I wouldn't suggest removing those tables, no. The Oracle XE browser-based interface is written in APEX (Application Express, a framework for developing web-based apps in the Oracle database). If you break APEX, that interface will likely stop working.


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


10

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


10

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


9

The database doesn't bother authenticating itself to itself. As the database does not use the password identification strategy when performing operations, changing the SYS password only impacts how humans can use the account.


9

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


9

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


9

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


9

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


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

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


9

Two solutions: Install oracle manually the first time. Then zip all ORACLE_HOME and for future installation simply do: Unzip oracle_home into the new home Run $ORACLE_HOME/bin/relink all Run ./runInstaller -silent -attachHome ORACLE_HOME="<Oracle_Home_Location>" ORACLE_HOME_NAME="<Oracle_Home_Name>" Other solution (not my preferred but ...


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

National Language Support (because Americans have this weird idea of using MM-DD-YYYY as a date format, and different languages have different words for their months).


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

When you issue an ALTER SYSTEM KILL SESSION, Oracle merely marks the session as killed and does the actual work of killing the session asynchronously. That may take just a couple seconds, it may take many hours if the session has an uncommitted transaction that did a lot of work that now has to be rolled back or if the session needs to stay around in a ...


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 it in one go, sort of: SQL> MERGE INTO sale s 2 USING (SELECT ROWID rid, sale_at, product_id 3 FROM sale 4 WHERE product_id IN (SELECT parent_id FROM split) 5 UNION ALL 6 SELECT CAST (NULL AS ROWID) rid, sale_at, child_id FROM sale 7 JOIN split ON (parent_id = product_id)) m 8 ON ...


8

You created the table in the SYS schema (which you should never, ever do. Really, never). When you log in as teacher1 any statement looks for objects in that schema. But there is no TEACHER1.INSTRUCTORS table, because the real name is SYS.INSTRUCTORS (did I mention what a bad idea it is to create objects in the SYS schema?). You need to run select * from ...


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



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