查詢Oracle Database 空間的指令－史特林研究室

用下面這個 SQL 查一下目前 TABLESPACE ，

SELECT A.TABLESPACE_NAME, A.BYTES / 1024 / 1024 "Sum MB",
(A.BYTES - B.BYTES) / 1024 / 1024 "used MB", B.BYTES / 1024 / 1024 "free MB",
ROUND(((A.BYTES - B.BYTES) / A.BYTES) * 100, 2) "percent_used"
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, MAX(BYTES) LARGEST
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY ((A.BYTES - B.BYTES) / A.BYTES) DESC

用下面這個 SQL 可以查到資料庫的實體檔案，

SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY
FROM DBA_DATA_FILES
ORDER BY FILE_ID DESC;

查到之後，就可以用下面的語法去增加檔案的大小。

ALTER DATABASE
DATAFILE 'C:\APP\FRANKIE\ORADATA\XXXXXXXX.DBF'
RESIZE 400M;

Oracle - 資料表筆數、大小的計算方式
/* 資料筆數 */
SELECT TABLE_NAME, AVG_ROW_LEN, NUM_ROWS
FROM DBA_TABLES
WHERE NUM_ROWS > 1000000
ORDER BY NUM_ROWS DESC;

/* Table size */
SELECT SEGMENT_NAME AS TABLE_NAME,
ROUND(SUM(BYTES) /(1024*1024*1024),2) AS TABLE_SIZE_GB
FROM USER_EXTENTS
GROUP BY SEGMENT_NAME
HAVING ROUND(SUM(BYTES) /(1024*1024*1024),2) > 0.5
ORDER BY ROUND(SUM(BYTES) /(1024*1024*1024),2) DESC;

/* Data size */
SELECT TABLE_NAME, AVG_ROW_LEN, NUM_ROWS,
ROUND (AVG_ROW_LEN * NUM_ROWS /(1024*1024*1024),2) AS DATA_SIZE_GB
FROM DBA_TABLES
WHERE ROUND(AVG_ROW_LEN * NUM_ROWS /(1024*1024*1024),2) > 0.1
ORDER BY ROUND(AVG_ROW_LEN * NUM_ROWS /(1024*1024*1024),2) DESC;

Oracle - 密碼忘記怎麼處理？
C:\Users\Frankie>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 11月 15 17:55:12 2012

連線到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user sys identified by chen1976$;

已更改使用者.

SQL> alter user system identified by chen1976$;

已更改使用者.

SQL>