Oracle 11g : howto and notes
oct 7th, 2008 by Prune
This new page will contain every cool tips I found regarding using Oracle, mainly 11g.
Index :
- timing a sqlplus query
- insert 1.000.000 rows in a table with random values
- select by date
- list table stats for full schema
- create/drop stats for table/index
- limit the number of rows returned
- connect to a RAC instance and find which one
- find informations on the Listener
- searching for index
- drop every tables from a schema
Timing a sqlplus query
Juste use :
SET TIMING ON
select * from mytable
…. Results here….
Elapsed: 00:00:00.15
Thanks to Brent V
Insert 1.000.000 rows in a table with random values
CREATE TABLE test_normal (empno number(10), ename varchar2(30), sal number(10)); Begin FOR i IN 1..1000000 Loop INSERT INTO test_normal VALUES(i, dbms_random.string('U',30), dbms_random.value(1000,7000)); IF mod(i, 10000) = 0 then Commit; End IF; End loop; End; CREATE TABLE test_random AS SELECT /*+ append */ * FROM test_normal ORDER BY dbms_random.random;
select by Date
In Oracle databases you have to convert the “string” to a date (TO_DATE), or a date to a string (TO_CHAR). The best is to use an example :
SELECT count(*) FROM portaluser WHERE CREATED_AT > TO_DATE('2008-09-24 12:00:00', 'YYYY-MM-DD HH24:MI:SS');
The Date format is like :
| MM | Numeric month (e.g., 07) |
| MON | Abbreviated month name (e.g., JUL) |
| MONTH | Full month name (e.g., JULY) |
| DD | Day of month (e.g., 24) |
| DY | Abbreviated name of day (e.g., FRI) |
| YYYY | 4-digit year (e.g., 1998) |
| YY | Last 2 digits of the year (e.g., 98) |
| RR | Like YY, but the two digits are “rounded” to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906 |
| AM (or PM) | Meridian indicator |
| HH | Hour of day (1-12) |
| HH24 | Hour of day (0-23) |
| MI | Minute (0-59) |
| SS | Second (0-59) |
List table stats for full schema
This will give you the cardinality of stats of each tables. It can be helpfull to see if your optimizer is up to date.
SET linesize 256 SET pagesize 0 SELECT * FROM USER_TAB_COL_STATISTICS;
Resulting in a large list of every table/colums, with the stats:
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE GLO USE AVG_COL_LEN HISTOGRAM ------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ----------- --------- ----------- --- --- ----------- --------------- PORTALUSER GENDER 3 80 C103 2.5444E-07 4 3 10-OCT-08 1965083 YES NO 3 FREQUENCY PORTALUSER BIRTHDATE 26633 776E0101010101 78770C05010101 .000037547 5338 1 10-OCT-08 1959749 YES NO 11 NONE PORTALUSER LASTNAME 1818434 096769726F7420090909 C3B9C3B9C3B9 5.4992E-07 41 1 10-OCT-08 1965046 YES NO 11 NONE PORTALUSER FIRSTNAME 1708987 09456D616E75656C6C6509090909 E282AC6C2140 5.8514E-07 1950 1 10-OCT-08 1963137 YES NO 11 NONE
Create/drop stats for table/index
Whatever you need to do with stats, use the dbms_stats package.
Stats creation/update:
exec dbms_stats.gather_table_stats('DB_USER','TABLE_NAME',cascade => true, force => true, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');
Options are not necessary. Cascade will rebuild for every column and index in the table. Force will rebuild stats that are already up to date.
Let’s go with an example :
exec dbms_stats.gather_table_stats('REVOLUTION_DEV','PORTALUSER_DOMAIN',cascade => true, force => true, method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');
If you want to remove every stats, use :
exec dbms_stats.delete_table_stats ( ownname=> 'REVOLUTION_DEV', tabname=> 'PORTALUSER_DOMAIN', cascade_parts=> true, cascade_columns=> true, cascade_indexes=> true);
Limit the number of row returned
While you can use “limit 10″ on Mysql, you can’t do that with Oracle.
With Oracle you have to add a WHERE statement with a limit on ROWNUM:
SELECT id, nickname FROM portaluser WHERE ROWNUM <= 10;
While I made some testing without any problem, I read to NOT use ORDER BY and ROWNUM at the same time. So, this is not recommended :
SELECT id, nickname FROM portaluser WHERE ROWNUM <= 10 ORDER BY id;
Instead, you have to use a double select :
SELECT * FROM (SELECT id,nickname FROM portaluser ORDER BY id) WHERE ROWNUM <= 10;
I really don’t know the efficience or not of such a query for now. Please, if you know, comment. Also, comment if you have information of this sort by thing and the differences between oracle 9, 10 or 11 release.
I had help from http://dotnet.org.za/thea/archive/2005/02/22/14715.aspx and http://www.ispirer.com/doc/sqlways38/Output/SQLWays-1-044.html
Connect to a RAC instance and find which one
connect system/***@ORACLE_RAC
SQL> select instance_name, host_name from v$instance ;
INSTANCE_NAME HOST_NAME
--------------------- ---------------------
ORACLE_RAC2 host2Find informations on the Listener
lsnrctl can give you a lot of details on the listener itself, on it’s configuration, and many more. Just check it out. Run it on both nodes, as Oracle user :
$ lsnrctl STATUS LSNRCTL FOR Solaris: Version 11.1.0.6.0 - Production ON 05-NOV-2008 10:27:01 Copyright (c) 1991, 2007, Oracle. ALL rights reserved. Connecting TO (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER_RACBAY0 Version TNSLSNR FOR Solaris: Version 11.1.0.6.0 - Production Start Date 28-FEB-2008 12:09:33 Uptime 250 days 22 hr. 17 min. 28 sec Trace Level off Security ON: LOCAL OS Authentication SNMP OFF Listener Parameter File /opt/oracle/product/11/oracle_base/asm_1/network/admin/listener.ora Listener Log File /opt/oracle/product/11/oracle_base/diag/tnslsnr/racbay0/listener_racbay0/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.249.14)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.40)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.4.40)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", STATUS READY, has 1 handler(s) FOR this service... Service "+ASM_XPT" has 1 instance(s). Instance "+ASM1", STATUS READY, has 1 handler(s) FOR this service... Service "BAYDB" has 1 instance(s). Instance "BAYDB1", STATUS READY, has 1 handler(s) FOR this service... Service "BAYDBXDB" has 1 instance(s). Instance "BAYDB1", STATUS READY, has 1 handler(s) FOR this service... Service "BAYDB_XPT" has 1 instance(s). Instance "BAYDB1", STATUS READY, has 1 handler(s) FOR this service... The command completed successfully
searching for index
A solution when you are trying to get some information about indexes in your database is to use the “dba_indexes” table. If you are only a user, query “user_indexes” and replace “OWNER” by “TABLE_OWNER” :
SELECT TABLE_NAME, OWNER, INDEX_NAME, INDEX_TYPE, UNIQUENESS, DISTINCT_KEYS FROM dba_indexes WHERE TABLE_NAME='PORTALUSER' ORDER BY OWNER; TABLE_NAME OWNER INDEX_NAME INDEX_TYPE UNIQUENES DISTINCT_KEYS ------------------------------ ------------------------------ ------------------------------ --------------------------- --------- ------------- PORTALUSER REVOLUTION_BENCH PORTALUSERDOMAINNICKNAME NORMAL UNIQUE 16 PORTALUSER REVOLUTION_BENCH PORTALUSER_NICKNAME_IDX NORMAL NONUNIQUE 16 PORTALUSER REVOLUTION_BENCH SYS_C0080558 NORMAL UNIQUE 16 PORTALUSER REVOLUTION_BENCH PORTALUSERDOMAINEMAIL NORMAL UNIQUE 16 PORTALUSER REVOLUTION_DEMO PORTALUSERDOMAINEMAIL NORMAL UNIQUE 25 PORTALUSER REVOLUTION_DEMO PORTALUSER_NICKNAME_IDX NORMAL NONUNIQUE 25 PORTALUSER REVOLUTION_DEMO SYS_C0064055 NORMAL UNIQUE 25 PORTALUSER REVOLUTION_DEMO PORTAL_LOWER_NICKNAME_IDX FUNCTION-BASED NORMAL NONUNIQUE 23 PORTALUSER REVOLUTION_DEMO PORTAL_CREATED_AT_DESC_IDX FUNCTION-BASED NORMAL NONUNIQUE 25 PORTALUSER REVOLUTION_DEMO PORTALUSER_EMAIL_INDEX NORMAL NONUNIQUE 25 PORTALUSER REVOLUTION_DEMO PORTUSRREGISTRSTATE_BITMAP_IDX BITMAP NONUNIQUE 2 TABLE_NAME OWNER INDEX_NAME INDEX_TYPE UNIQUENES DISTINCT_KEYS ------------------------------ ------------------------------ ------------------------------ --------------------------- --------- ------------- PORTALUSER REVOLUTION_DEMO PORTALUSERDOMAINNICKNAME NORMAL UNIQUE 25 PORTALUSER REVOLUTION_DEV PORTAL_LOWER_NICKNAME_IDX FUNCTION-BASED NORMAL NONUNIQUE 1965083 PORTALUSER REVOLUTION_DEV PORTAL_CREATED_AT_DESC_IDX FUNCTION-BASED NORMAL NONUNIQUE 296631 PORTALUSER REVOLUTION_DEV SYS_C0062869 NORMAL UNIQUE 1965086 PORTALUSER REVOLUTION_DEV PORTALUSER_NICKNAME_INDEX NORMAL NONUNIQUE 1965086 PORTALUSER REVOLUTION_DEV PORTALUSER_EMAIL_INDEX NORMAL NONUNIQUE 1965086 PORTALUSER REVOLUTION_DEV PORTALUSERDOMAINNICKNAME NORMAL UNIQUE 1965086 PORTALUSER REVOLUTION_DEV PORTALUSERDOMAINEMAIL NORMAL UNIQUE 1965086 PORTALUSER REVOLUTION_DEV PORTALUSER_DOMAINID_INDEX NORMAL NONUNIQUE 1 PORTALUSER WEBPORTALS_UAT PORTAL_LOWER_NICKNAME_IDX FUNCTION-BASED NORMAL NONUNIQUE 339672 PORTALUSER WEBPORTALS_UAT PORTAL_CREATED_AT_DESC_IDX FUNCTION-BASED NORMAL NONUNIQUE 331207
Then you can learn more of the index with the query :
SELECT index_name, table_name, column_name FROM user_ind_columns ORDER BY table_name, index_name,column_position;
Drop every tables from a schema
Even when using impdp to import data, the old schema is not removed. You only get new schema change and data. Then you need to remove everything before importing, including constraints.
One solution is to use a cursor loop as the schema user :
DECLARE cursor usertables IS SELECT table_name FROM user_tables; BEGIN FOR cur IN usertables loop EXECUTE IMMEDIATE 'DROP TABLE "WEBPORTALS_UAT".'||cur.table_name||' CASCADE CONSTRAINTS'; end loop; END; /
You can drop every sequences by replacing the table select by :
SELECT sequence_name FROM user_sequences;
-
Links
http://justgeeks.blogspot.com/
http://www.oracle.com/technology/pub/articles/sharma_indexes.html
http://www.psoug.org/library.html
http://fadace.developpez.com/oracle/rac/spfile/