Useful oracle SQL commands
Fra Harald Grovens wiki
Here is a scrap book of SQL statements that's useful.
[rediger] Connect to an external Oracle databse
C:\ sqlplus username/password@IPadress / Lokal
lokal is the name of the DB
[rediger] List all tables in an Oracle database
SQL> SELECT table_name as name, tablespace_name as tablespace, num_rows as rader FROM sys.all_tables;
[rediger] Simple search in the 1900 census
SQL> SELECT fornvn, ettnvn FROM folketellinger.person_1900 WHERE ettnvn='Groven';
[rediger] Change default tablespace in 10g
ALTER DATABASE DEFAULT TABLESPACE <tsname>;
[rediger] Change ownership of tables
Q: I have a couple of tables with 1000+ rows in each and I want to give them, the ownership, to another user. I don't want to do a export/import. Is that possible or not?
A: Sorry, Oracle doesn't have a "change owner" procedure. If you're brave and you have a test database to work with, you could try an update of the sys.obj$ table. Look up the object_id first in dba_objects, then look up the user_id's in dba_users.Then with those pieces of information, you could try to update the row(s) for the object(s) you want to change ownership of. If you do this for one or more tables, you may want to check for indexes, views, triggers and synonyms based on those tables also, and change the ownership for them too. You would also need to check packages, procedures and functions that depend on the tables, views, and/or synonyms affected. You may need to change and/or recompile those as well. Keep in mind that I'm not recommending this, nor does Oracle support this, so if you try it, it is at your own risk, but it may work.
[rediger] Grant permissions to run Oracle migration Workbench
- GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM TO harald WITH ADMIN OPTION;
- GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE, ALTER TABLESPACE, ALTER ANY TRIGGER, COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY SEQUENCE, DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER, DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE TO harald;
[rediger] Enable fuzzy searching
CREATE INDEX HARALD.gatenavnsnormalisering ON HARALD.BOSTED1900B_SPLIT (GTNAVNNORM) indextype is ctxsys.context;
[rediger] Give user right to make indexes
GRANT EXECUTE ON CTX_DDL TO harald;
[rediger] Create index for fuzzy search
begin ctx_ddl.create_index_set('gatecatsearch'); ctx_ddl.add_index('gatecatsearch', 'GTNAVNNORM'); end;
[rediger] Oracle equivalent of GROUP_CONCAT
In order to create frequency lists for RL, you need to create list of numbers of entries that have a specific value.
SQL> select d.dname 2 , NVL(concat_all(concat_expr(e.ename,',')),'None') enames 3 from emp e 4 , dept d 5 where e.deptno(+) = d.deptno 6 group by d.dname 7 / DNAME ENAMES -------------- -------------------------------------------------- ACCOUNTING CLARK,KING,MILLER OPERATIONS None RESEARCH SMITH,JONES,SCOTT,ADAMS,FORD SALES ALLEN,MARTIN,TURNER,WARD,JAMES,BLAKE
