SQL Tricks:
Oracle SQL tricks
Here are some useful SQL tricks, tested on an Oracle database.
----------------------------------------------------------------------------
Does Oracle use my index or not?
One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example:
SQL> CREATE TABLE t1 (c1 NUMBER);
Table created.
SQL> CREATE INDEX t1_idx ON t1(c1);
Index created.
SQL> ALTER INDEX t1_idx MONITORING USAGE;
Index altered.
SQL>
SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;
TABLE_NAME INDEX_NAME MON USE
------------------------------ ------------------------------ --- ---
T1 T1_IDX YES NO
SQL> SELECT * FROM t1 WHERE c1 = 1;
no rows selected
SQL> SELECT table_name, index_name, monitoring, used FROM v$object_usage;
TABLE_NAME INDEX_NAME MON USE
------------------------------ ------------------------------ --- ---
T1 T1_IDX YES YES
To reset the values in the v$object_usage view, disable index monitoring and re-enable it:
ALTER INDEX indexname NOMONITORING USAGE;
ALTER INDEX indexname MONITORING USAGE;
---------------------------------------------------------------------------
Find last N records from table
I think I have found a easy way to select last n record from a table.
below is my SQL statement:
select * from (select rownum a, column1,column2 ,...,columnn from my_table)
where a > ( select (max(rownum)-10) from my_table);
where my_table is the sample table's name ;
column1 to columnn are the columns in the table,
you can replace 10 as the n where you want to select;
------------------------------------------------------------------------------------------------------------------------------
How to move tablespace to a new location
1) Take the tablespace offline
ALTER TABLESPACE tablespace-name OFFLINE;
2) Use the OS to Move the tablespace to the new location
3) Run the following command:
ALTER TABLESPACE tablespace-name RENAME DATAFILE ‘OS path to old tablespace\tablespace-datafile- name.dbf’ TO ‘OS path to new location\tablespace-datafile-name.dbf’;
4) Take the tablespace back online
ALTER TABLESPACE tablespace-name ONLINE;
If the following error is displayed
ORA-01113: file n needs media recovery
ORA-01110: data file n: ‘new location file name’
Issue the the following command:
recover datafile ‘new location file name ‘;
and take the tablespace back online
ALTER TABLESPACE tablespace-name ONLINE;
------------------------------------------------------------------------------------------------------------------------------
Simple solution for ORA-12638: Credential retrieval failed
If the Oracle client was installed using the domain administrator account instead of the local administrator, the following message could be displayed when trying to connect:
ORA-12638: Credential retrieval failed
From the wording of the error message one could think that this error means that he is using the wrong password but this is not the case. To solve this:
1) Go to /oracle home/network/admin and open sqlnet.ora
2) Search for SQLNET.AUTHENTICATION_SERVICES= (NTS) and change it to
SQLNET.AUTHENTICATION_SERVICES= (NONE)
3) Save the file and try to connect, this should work
-----------------------------------------------------------------------------------------------------------------------------
What to do when Enterprise Manager is not able to connect to
the database instance (ORA-28001)
If you are trying to connect to the Oracle enterprise Manger and you get the following errors:
Enterprise Manager is not able to connect to the database instance.
And in t
Enterprise Manager is not able to connect to the database instance.
And in the “Agent connection to instance” section:
Status: Failed
Details: ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)
One of the reasons could be that the password for the SYSMAN user is expired. However, changing the password alone will not solve this issue. Several additional steps are required in order to make Oracle Enterprise Manager connect:
Before you start: Verify that ORACLE_HOME, ORACLE_SID environment variables are set. – If not, set them using as environment variables or open a command line and type SET ORACLE_SID=<The database SID>
1. Stop the dbconsole: emctl stop dbconsole (dos and Unix) or using the windows services stop the OrcleDBConsole<The database SID>.
2. Connect to the database as a user with DBA privilege with SQL*Plus
and run the following command:
alter user sysman identified by <the new password> ;
3. Verify that the new password works
SQL> connect sysman/<the new password>
4. Go to ORACLE_HOME/<HostName_SID>/sysman/config and save a backup of the emoms.properties file.
a) Open the file emoms.properties and search for:
oracle.sysman.eml.mntr.emdRepPwd=<some encrypted value>
Replace the encrypted value with the new password value
b) Search for oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
and change TRUE to FALSE
c) Save and close emoms.properties
5. Restart the dbconsole:
emctl start dbconsole (dos and Unix) or using the windows services start the OrcleDBConsole<The database SID>.
6. Open emoms.properties again and Search for:
a)oracle.sysman.eml.mntr.emdRepPwd=
verify that the password is encrypted
b) oracle.sysman.eml.mntr.emdRepPwdEncrypted=
verify that the value is set to TRUE
7. Refresh Oracle Enterprise Manager
-------------------------------------------------------------------------------------------------------
Display BAD Index
select owner||'.'||index_name||' STATUS='||status status
from dba_indexes where status='UNUSABLE'
union all
select owner||'.'||index_name||' DOMIDX_STATUS='||domidx_status status
from dba_indexes where domidx_status='IDXTYP_INVLD'
union all
select owner||'.'||index_name||' DOMIDX_OPSTATUS='||domidx_opstatus status
from dba_indexes where domidx_opstatus='FAILED'
union all
select owner||'.'||index_name||' FUNCIDX_STATUS='||funcidx_status status
from dba_indexes where funcidx_status='DISABLED'
/
----------------------------------------------------------------------------------------------------------------------------------
Steps to create Stand by Database
1. Shutdown Normal the primary database
2. Copy all of the tablespace datafiles to the standby datafile directory
Note: This may take a long time depending on datafile size and network traffic
OS>COPY <drive>:\Oracle\ORADATA\<sid>\*.dbf <drive>:\Oracle\ORADATA\STANDBY\*.*
OS>COPY *.arc <drive>:\Oracle\ORADATA\STANDBY\Archive\*.*
3. Ensure the following INIT.ORA parameters are set on the primary DB:
LOG_ARCHIVE_DEST = "<drive>:\Oracle\ORADATA\<sid>\Archive"
LOG_ARCHIVE_FORMAT = "LOG%s%t.ARC"
LOG_ARCHIVE_START = TRUE
4. StartUp the primary DB with archive logging turned on
SVRMGRL>connect internal
SVRMGRL>startup mount <sid>;
SVRMGRL>alter database archivelog;
SVRMGRL>archive log start;
SVRMGRL>alter database open;
5. create a standby control file
SVRMGRL>alter database create standby controlfile as '<drive>:\Oracle\ORADATA\STANDBY\standbycf.ctl';
MOVE standbycf.ctl control01.ctl
COPY control01.ctl control02.ctl
COPY control01.ctl control03.ctl
6. Update and/or Create TNSNAMES.ora and LISTENER.ora files to include standby db
7. Copy the \Oracle\Admin\<sid> directory to the corresponding standby db directory
8. Modify the init.ora file as follows:
DB_NAME must remain the same as the primary database's entry
STANDBY_ARCHIVE_DEST = "<drive>:\Oracle\ORADATA\STANDBY\Archive"
LOG_ARCHIVE_DEST = "<drive>:\Oracle\ORADATA\STANDBY\Archive"
LOCK_NAME_SPACE = STANDBY
DB_FILE_NAME_CONVERT = ('<drive>:\Oracle\ORADATA\<sid>\','<drive>:\Oracle\ORADATA\STANDBY\')
LOG_FILE_NAME_CONVERT = ('<drive>:\Oracle\ORADATA\<sid>\Archive','<drive>:\Oracle\ORADATA\STANDBY\Archive')
9. Use ORADIM to create StandBy Oracle DB service
10. Copy the password file PWD<sid>.ora to PWDSTANDBY.ora
11. SVRMGRL>Connect sys/change_on_install@standby
12. SVRMGRL>STARTUP NOMOUNT pfile=\Oracle\STANDBY\Admin\PFile\init.ora;
13. SVRMGRL>ALTER DATABASE MOUNT STANDBY DATABASE;
14. SVRMGRL>ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';
15. Copy over any archive log files
16. SVRMGRL>RECOVER STANDBY DATABASE;
17. SVRMGRL> <Ret> | FILENAME | AUTO | CANCEL
------------------------------------------------------------------------------------------------------------------------------
The Oracle 7.3, and 8.0.3, and 8.1.5 manuals are not supposed to be online. The 9i manuals are supposed to be online (free username/password required)
A PL/SQL package to generate random numbers
What if you want a random number in the where clause, like, so you can choose 1/10 of the rows at random? Use a hash instead.
select * from emp where dbms_utility.get_hash_value(dump(rowid),0,100)<10;
The biggest difference between Oracle and everyone else is the locking mechanism. Everyone else gets share locks on data when they query it. That means other queries can also see the data, but queries block writes and writes block queries. (Update: the default for everyone else, for for example for DB2, is for queries not to block writes. Instead they show any data committed at the time the query internally fetches the data. This means each query result can be internally inconsistent, but it allows greater concurrency.) Oracle doesn't have share locks. Instead it uses snapshots, which are points in time. A query looks at the data as of some point in time. If someone changes the data later, that's OK, the query still sees the old version of the data. Each query result is internally consistent, but consecutive queries may not be consistent with one another. Queries don't block writes and writes don't block queries. Writes still block writes. Both share locks and snapshots are reasonable concurrency models, but they aren't the same. It's wrong to think in terms of one when working with the other.
Derived tables (from-clause subqueries, inline views): select * from (select * from emp);
insert into (select deptno from dept)
select trunc(a) from (select sum(empno)/5000 a from emp);
This has the same effect as if you used a view. It is useful for queries that have a repeated complex expression; for example select x from (select avg(sin(empno)) x from emp group by deptno)
where x > 0;
It's also useful when you want to use one of the values found by a subquery, for example when deleting all outdated entries from a log: delete from log where rowid in
(select rowid from log
minus
select a.rowid
from log a, (select id, max(time) mtime from log group by id) b
where a.id = b.id and a.time = b.mtime);
As of 9i, inline views can also be expressed with a "WITH clause", for example
with sub1 as (select deptno, avg(sin(empno)) x from emp group by deptno)
select a.deptno, a.x, b.x from sub1 a, sub1 b where a.deptno > b.deptno;
When should varchar2 be used instead of char? Always. Varchar2 is always faster, more space efficient, less buggy, and its comparison rules are more likely to be what you expect.
Is SQL case sensitive? No. Can I give tables case-sensitive names with weird characters in them? Yes. Quote them, like so: create table "Table" ("My column is KEWL!!!" int);
How do I insert data into an nvarchar2 column? From 9i onwards, just like any other character column, and the character set is guaranteed to hold the Unicode repertoire and measure characters using UCS2 codepoints. There is implicit conversion between char and nchar. Before 9i, avoid nvarchar2. If you really must use it, you insert like so: "insert into nemp (ename) values (n'SCOTT')".
Building a big bogus table: create table a (a1 number, a2 varchar2(50));
begin
for i in 1..10000 loop
insert into a values (i, 'I am a unique and extraordinary individual');
end loop;
commit;
end;
/
alter table a add constraint apk primary key (a1);
select count(*) from a; -- 10000 rows
Like the making of sausage and politics, it is best not to understand the making of Oracle data types. select dump(1), dump(-1), dump(sysdate) from dual;
Ouch! Ouch! You're twisting my arm. I'll explain, for Oracle numbers at least. All data on disk looks like <length><stuff>
All data on disk is byte-sortable (other than the lengths).
Digits are base 100, and the first byte is the (base-100) exponent. For example, the number 1 has exponent 193, 10 also has 193, 100 has 194, 0.01 has 192.
Negative numbers complement the exponent. 62 for -1, 63 for -.01, 61 for -100.
Why? Because the exponent has to be byte-sortable.
Digits. Positive digits are 1..100. Negative digits are 101..2. Again, to be byte-sortable. Base-100 digits use about 7 out of 8 bits of each byte, so this is a reasonably efficient way of storing numbers.
Negative numbers have a trailing byte of value 102, unless the maximum number of digit bytes (20) are used. To make things byte sortable. -1 > -1.01, right? But (62,100) < (62,100,100). The trailing 102 corrects that: (62,100,102) > (62,100,100,102).
But why isn't the exponent for 1 192, for -1 63, and why are the digits 1..100 and 101..2 instead of 0..99 and 99..0? Because, way back in prehistory, Oracle used 0 as a null terminator, so you couldn't store 0 on disk for any other purpose. So 1 was added to everything. There's no reason not to store 0 on disk anymore because all values store their lengths separately, but the number format hasn't changed.
My guess is that negative infinity was originally (1,1), pushing the negative numbers out to 101..2 instead of 100..1. That's not what negative infinity is now, and SQL doesn't expose the infinities anyhow.
To find out how a query is executed, create a plan table with UTLXPLAN.SQL, fill the table with explain plan for select ... ;
and then look at the results with a connect-by script, something like this: select substrb(to_char(cost),1,6) cost,
substrb(to_char(cardinality),1,6) card,
substrb(substr(' . , . , . ',1,level) || operation,1,20) type,
substrb(options,1,15) subtype,
substrb(object_owner,1,5) owner,
substrb(object_name,1,5) name,
substrb(object_node,1,5) link from plan_table
connect by parent_id = prior id
start with id = 0;
delete from plan_table;
Insert and update with check option: insert into (select * from emp where deptno = 10 with check option)
values (...);
update (select * from (select sal, empno from emp, dept
where emp.deptno=dept.deptno and loc='AKRON')
where sal > 100000 with check option)
set sal = :1 where empno = :2;
Mutating/Constraining errors. Before 8i:
You only get these when you modify a table being read or you read a table being modified. Foreign key constraints read some tables implicitly. A table that might be deleted from due to delete cascade counts as modifying.
You only get these errors from per-row triggers, not from after statement or before statement triggers (unless they are under per-row triggers).
Delete cascade enforcement fires statement triggers every time it delete cascades a row, so all those statement triggers are subject to mutating errors too.
When you get the error, use your per-row trigger to fill a PL/SQL table instead. Then use an after-statement trigger to apply the changes based on that PL/SQL table. There are generic packages out there for generating the triggers, PL/SQL tables, and so forth needed to implement update cascade. Often you need more PL/SQL variables to keep track of whether you are already under a trigger, to avoid recursion. After 8i:
How to enable constraints without locking tables for hours on end (using Oracle8 or later). First put all constraints in the ENABLE NOVALIDATE mode, then ENABLE (or VALIDATE) them individually. The ENABLE NOVALIDATE modifies only metadata. The VALIDATE hold no locks, can run in parallel, and many such enables can be run concurrently.
How to store data case sensitive but to index it case insensitive (from 8i on): Set compatible=8.1.0.0.0, query_rewrite_integrity=trusted, and query_rewrite_enabled=true. create table a (a1 varchar2(10));
create index ai on a (upper(a1));
analyze table a compute statistics;
Half the mutating errors go away. Specifically, it is still illegal to read or modify a mutating table, but modifying a table that is being read is fine. This allows the obvious before-row trigger implementation of update cascade, for example. The problem of delete cascade firing statement triggers under row triggers is fixed in Oracle8i too.
This will use the cost-based optimizer. From what I've heard, that's OK. As of Oracle8i the cost-based optimizer is a good thing, and it can use several access methods the rule-based optimizer doesn't know about.