Oracle
http://docs.oracle.com/cd/B19306_01/index.htm
JDBC driver for JDK 1.6 (ojdbc6.jar): http://download.oracle.com/otn/utilities_drivers/jdbc/11204/ojdbc6.jar
Maven local repository install
mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.3 -Dpackaging=jar -Dfile=ojdbc6.jar -DgeneratePom=true
Get schemas in DB
Toggle line numbers
1 SELECT DISTINCT USERNAME FROM DBA_USERS;
Get columns from table
Toggle line numbers
1 SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME='XYZ';
2
3 SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='AAA';
Get indexes from table
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1069.htm
Toggle line numbers
1 SELECT index_name, table_owner, table_name, uniqueness FROM USER_INDEXES WHERE table_name = 'XYZ';
2 SELECT index_name, table_owner, table_name, uniqueness FROM ALL_INDEXES WHERE table_name = 'XYZ';
Get code from stored procedure
Toggle line numbers
1 SELECT * FROM all_source WHERE name = 'myprocedure'
2 ORDER BY owner,name,line
Show schema and table
Toggle line numbers
1 SELECT distinct owner,table_name
2 FROM all_tables
3 ORDER BY owner,table_name;
Partinioning
http://www.orafaq.com/wiki/Interval_partitioning
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin005.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm
Toggle line numbers
1 select table_name,partition_name,num_rows
2 from user_tab_partitions
3 where table_name='TEST';
Other views:
- DBA_TAB_PARTITIONS
- ALL_TAB_PARTITIONS
- USER_TAB_PARTITIONS
Sequence creation
Toggle line numbers
1 CREATE SEQUENCE sample_seq START WITH 1 INCREMENT BY 1;
2 CREATE SEQUENCE sample_seq2 MINVALUE 1 START WITH 1 INCREMENT BY 1;
3 SELECT * FROM ALL_SEQUENCES; -- show all sequences
4
User and table space creation in Oracle XE
Toggle line numbers
1 -- login with system user
2 create tablespace christs datafile 'c:\christs.dbf' size 100m;
3 create user chris identified by 12345678 default tablespace christs;
4 alter user chris quota unlimited on christs;
5 grant create session to chris;
6 grant create table to chris;
7
8 -- login with user chris
9 create table CHRIS.test ( name varchar(255) ) tablespace chris;
10 insert into CHRIS.test (name) values('Test name 1234');
11
12 -- login with system user
13 -- user alice to just run selects
14 create user alice identified by 12345678 default tablespace christs;
15 grant create session to alice;
16 grant select on CHRIS.test to alice;
17
18 -- login with alice
19 select * from CHRIS.test;
Oracle Database 11g Express Edition
Download from http://www.oracle.com/technetwork/database/database-technologies/express-edition/overview/index.html
- Run setup.exe
- Next
- Accept
- Next
- c:\oraclexe
- Port TNS 1521
- Port MTS 2030
- Port Http 8081
- Next
- For user sys and system password ********
- Next
- Install
- Finish
Info
- Default SID xe .
- Default JDBC driver JAR ojdbc6.jar .
- JDBC Url jdbc:oracle:thin:@//localhost:1521/XE