Database

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.

Derby 不能修改列类型 CANNOT Alert Column Data type

Categories: Database; Tagged with: Derby; @ October 6th, 2010 16:03

Derby(10.5)中不能通过Alert修改Column的DataType, 可能需要重建Column才能实现DataType的修改.

(more…)

Newer Posts <-> Older Posts