oracle儲存管理之 segment和space管理(server.102 b14231)

--create shcema 組合create table /ceate view /grant語句，可以回滾
CREATE SCHEMA AUTHORIZATION scott
CREATE TABLE dept (
deptno NUMBER(3,0) PRIMARY KEY,
dname VARCHAR2(15),
loc VARCHAR2(25))
CREATE TABLE emp (
empno NUMBER(5,0) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5,0),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3,0) NOT NULL
CONSTRAINT dept_fkey REFERENCES dept)
CREATE VIEW sales_staff AS
SELECT empno, ename, sal, comm
FROM emp
WHERE deptno = 30
WITH CHECK OPTION CONSTRAINT sales_staff_cnst
GRANT SELECT ON sales_staff TO human_resources;
--收集統計資訊
DBMS_STATS
GATHER_INDEX_STATS
GATHER_TABLE_STATS
GATHER_SCHEMA_STATS
GATHER_DATABASE_STATS

ANALYZE TABLE emp VALIDATE STRUCTURE;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE; -- 分析table + index
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;
--Listing Chained Rows of Tables and Clusters
UTLCHAIN.SQL or UTLCHN1.SQL
ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE TABLE order_hist LIST CHAINED ROWS;
--消除行遷移、行連結步驟如下：
1. Use the ANALYZE statement to collect information about migrated and chained
rows.
ANALYZE TABLE order_hist LIST CHAINED ROWS;
2. Query the output table:
SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96

The output lists all rows that are either migrated or chained.
3. If the output table shows that you have many migrated or chained rows, then you
can eliminate migrated rows by continuing through the following steps:
4. Create an intermediate table with the same columns as the existing table to hold
the migrated and chained rows:
CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
5. Delete the migrated and chained rows from the existing table:
DELETE FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');
6. Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist
SELECT *
FROM int_order_hist;
7. Drop the intermediate table:
DROP TABLE int_order_history;
8. Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';
9. Use the ANALYZE statement again, and query the output table.


ALTER TABLE emp
DROP PRIMARY KEY KEEP INDEX,
DROP CONSTRAINT dept_fkey;

DBA_CONSTRAINTS
ALL_CONSTRAINTS
USER_CONSTRAINTS
DBA_CONS_COLUMNS
ALL_CONS_COLUMNS
USER_CONS_COLUMNS

DBA_OBJECTS
ALL_OBJECTS
USER_OBJECTS
DBA_CATALOG
ALL_CATALOG
USER_CATALOG
DBA_DEPENDENCIES
ALL_DEPENDENCIES
USER_DEPENDENCIES

DBMS_METADATA.GET_DDL
DBMS_SERVER_ALERT.SET_THRESHOLD
DBA_OUTSTANDING_ALERTS

DBMS_ADVISOR
(
CREATE_TASK
CREATE_OBJECT
SET_TASK_PARAMETER
EXECUTE_TASK
)
--手動取得段指導建議
variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='Manual_Employees';
descr:='Segment Advisor Example';
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);
dbms_advisor.create_object (
task_name => name,
object_type => 'TABLE',
attr1 => 'HR',
attr2 => 'EMPLOYEES',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
dbms_advisor.set_task_parameter(
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task(name);
end;
end;
/
With Enterprise Manager
By querying the DBA_ADVISOR_* views
By calling the DBMS_SPACE.ASA_RECOMMENDATIONS procedure
--獲取segment建議
select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
--線上縮小segment (high water mark)
ALTER TABLE ... ENABLE ROW MOVEMENT

DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS
--檢視空間使用情況
DBMS_SPACE.UNUSED_SPACE
DBMS_SPACE.FREE_BLOCKS
DBMS_SPACE.SPACE_USAGE

ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;
--示例：檢視 emp table的空間使用情況
VARIABLE total_blocks NUMBER
VARIABLE total_bytes NUMBER
VARIABLE unused_blocks NUMBER
VARIABLE unused_bytes NUMBER
VARIABLE lastextf NUMBER
VARIABLE last_extb NUMBER
VARIABLE lastusedblock NUMBER
exec DBMS_SPACE.UNUSED_SPACE('SCOTT', 'EMP', 'TABLE', :total_blocks, :total_bytes,:unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock);
--相關 空間使用情況 檢視
DBA_SEGMENTS
USER_SEGMENTS
DBA_EXTENTS
USER_EXTENTS
DBA_FREE_SPACE
USER_FREE_SPACE

SELECT a.SEGMENT_NAME, a.SEGMENT_TYPE, a.TABLESPACE_NAME, a.OWNER
FROM DBA_SEGMENTS a
WHERE a.NEXT_EXTENT >= (SELECT MAX(b.BYTES)
FROM DBA_FREE_SPACE b
WHERE b.TABLESPACE_NAME = a.TABLESPACE_NAME)
OR a.EXTENTS = a.MAX_EXTENTS
OR a.EXTENTS = 'data_block_size' ;

--估算空間使用情況和趨勢
DBMS_SPACE.CREATE_TABLE_COST
DBMS_SPACE.OBJECT_GROWTH_TREND
DBMS_SPACE.CREATE_INDEX_COST

總結：oem看到的圖形介面都是後臺的dbms包+job提供支援，瞭解和熟悉dbms包對oracle深入管理大有裨益。例如：

DBMS_STATS
DBMS_METADATA
DBMS_SERVER_ALERT
DBMS_ADVISOR
DBMS_SPACE

[@more@]