Oracle

Oracle 11 XE & JDBC

Categories: Java; Tagged with: Java • JDBC • Oracle; @ May 1st, 2013 0:51

How to get Oracle 11 XE for Windows 64?

Download the Oracle 11 XE 32 bit and install it.

No problem for me ( Win7 64)

Forgot the sys password, how to reset?

Conn / as sysdba

passw system

input new password.

[http://rolfje.wordpress.com/2007/01/16/lost-oracle-sys-and-system-password/]

How to get the version of Oracle?

select * from v$version

How to Add Oracle JDBC Driver in Loacl Maven Repo?

mvn install:install-file -Dfile=C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0 -Dpackaging=jar
then add modify your pom:

<dependency>
	<groupId>com.oracle</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>11.2.0</version>
</dependency>

[http://www.mkyong.com/maven/how-to-add-oracle-jdbc-driver-in-your-maven-local-repository/]

Quick test using JDBC

	public static void main(String[] args) throws Throwable {
		
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection connection = null;
		connection = DriverManager.getConnection(
			"jdbc:oracle:thin:@localhost:1521:xe","root","root");
		System.out.println(connection.getMetaData().getDatabaseMajorVersion());
		connection.close();
	}

Oracle: 扩充TableSpace语句

Categories: Database; Tagged with: Oracle • SQL; @ November 13th, 2011 13:31

Step1: check current status and get all file path
`SELECT B.FILE_NAME FILENAME, B.TABLESPACE_NAME TABLESPACENAME, B.BYTES/1024/1024 SIZE_MB, (B.BYTES-SUM(NVL(A.BYTES,0)))/1024/1024 USED_MB, substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) Percentage from dba_free_space a,dba_data_files b where a.file_id=b.file_id GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.BYTES order by b.tablespace_name;`

Step2: enlarge table space file
`alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XX\USERS.DBF' resize 2000m;`

Oracle SQLs: Get system date; count user tables. etc,.

Categories: Database; Tagged with: Oracle • Select system date; @ October 22nd, 2011 19:32

Get system date

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual; -- Get the formatted system date.
select sysdate from dual; -- Get date

Count user tables:

SELECT OWNER,COUNT(*) FROM ALL_TABLES GROUP BY OWNER;  -- List all user and table number they own;

select count(*) from dba_tables t where t.owner='NAME'; -- Count tables of user with the name of "NAME"
select count(table_name) from user_tables; -- Count tables of current user;

Oracle SQL Developper cannot show connections view/cannot create new connection

Categories: Database; Tagged with: Oracle; @ October 15th, 2011 12:44

I installed Oracle 10g XE and SQL Developper 2 in my PC, they works perfect. Yesterday, I upgrade to 11g r2 XE, after restar my PC, the SQL Developper 2 cannot work:

oracle.dbtools.raptor.timesten.TTAddin
Failed to create addin oracle.dbtools.raptor.report.addin.ReportAddin

I removed all data, but cannot work as before, based on my research, I think it’s because the SQL Developper is too old.

so I download a new version of SQL Developper, and it works!

For more info about this issue: https://forums.oracle.com/forums/message.jspa?messageID=9491523

修改Oracle 10g XE WEB服务器端口 – Modify the Oracle XE web console server port

Categories: Database; Tagged with: Oracle • Oracle 端口; @ August 21st, 2011 13:06

we need to change one port when using Oracle 10g XE and Tomcat/JBoss, because they all use the 8080 port.
It’s very easy to change the web server port, just need to update some config xml file.
For Oracle, we just need to execute a SQL:

After connect to the server as sys admin, execute the following SQL:
begin
dbms_xdb.sethttpport(‘8888’);
end;
/

The port will be updated to 8888, no need to restart the oracle service.

Older Posts