Tag Info

Hot answers tagged

26

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


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.


12

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

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

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

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


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

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

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

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


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

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


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

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


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

You need to shrink the data files, which can be tricky. However, possibly the easier method to describe and implement is to create a new tablespace of approximately the right size, use ALTER TABLE to move the table segments, and ALTER INDEX to rebuild the indexes in the new tablespace. When the old tablespace is empty of objects you can drop it. Oracle ...


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


7

SQL Developer is using the so called "thin driver". For this the JDBC driver (a file named ojdbc6.jar) implements the complete Oracle network protocol which is usually handled by the Oracle client. It could use the another implementation: the "OCI" (or "thick") driver which would require an Oracle client to be installed. That would be necessary if e.g. ...



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