Differences between revisions 3 and 12 (spanning 9 versions)

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

   1 <dependency>
   2     <groupId>com.oracle</groupId>
   3     <artifactId>ojdbc6</artifactId>
   4     <version>11.2.0.3</version>
   5 </dependency>

Get schemas in DB

   1 SELECT DISTINCT USERNAME FROM DBA_USERS;

Get columns from table

   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

   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

   1 SELECT * FROM all_source WHERE name = 'myprocedure'
   2 ORDER BY owner,name,line

Show schema and table

   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

   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

   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

   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;

-  ⇤ ← Revision 3 as of 2014-05-27 14:14:41 → 
  Size: 994
  Editor: 195-23-86-114
  Comment:
+   ← Revision 12 as of 2015-02-16 21:11:35 → ⇥
  Size: 2801
  Editor: 28
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 5:
-JDBC driver for Java 1.6: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html
+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

{{{#!highlight xml
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>
}}}
-Line 15:
+Line 26:
+SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='AAA';
-Line 37:
+Line 50:
+== 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

{{{#!highlight sql
select table_name,partition_name,num_rows
from user_tab_partitions
where table_name='TEST';
}}}

Other views:
 * DBA_TAB_PARTITIONS
 * ALL_TAB_PARTITIONS
 * USER_TAB_PARTITIONS

== Sequence creation ==
{{{#!highlight sql
CREATE SEQUENCE sample_seq  START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE sample_seq2 MINVALUE 1 START WITH 1 INCREMENT BY 1;
SELECT * FROM ALL_SEQUENCES; -- show all sequences
}}}

== User and table space creation in Oracle XE ==
{{{#!highlight sql
-- login with system user
create tablespace christs datafile 'c:\christs.dbf' size 100m;
create user chris identified by 12345678 default tablespace christs;
alter user chris quota unlimited on christs;
grant create session to chris;
grant create table to chris;

-- login with user chris
create table CHRIS.test ( name varchar(255) ) tablespace chris;
insert into CHRIS.test (name) values('Test name 1234');

-- login with system user
-- user alice to just run selects
create user alice identified by 12345678 default tablespace christs;
grant create session to alice;
grant select on CHRIS.test to alice;

-- login with alice
select * from CHRIS.test;

}}}