Plan, Index, TableSpace, User

SET HEADING OFF
SET PAGESIZE 1000

Query Plan

EXPLAIN PLAN FOR
     QUERY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
DELETE from PLAN_TABLE;

Index Scan

/*+ index(index_nm) */

Index Rebuild

ALTER INDEX emp_ename_idx REBUILD TABLESPACE idxs

Index validation check

ANALYZE INDEX idx_name VALIDATE STRUCTURE;
SELECT 
    blocks,  btree_space,  used_space,  pct_used "사용율(%)",  lf_rows,  del_lf_rows "삭제행"
FROM  
    INDEX_STATS;

User Tablespace Info

SELECT 
    username, default_tablespace, temporary_tablespace
FROM DBA_USERS

Tablespace Info

COL FILE_NAME FORMAT A40
COL TABLESPACE_NAME FORMAT A30
SET LINESIZE 150
SELECT  
    b.file_name "FILE_NAME",                               --  DataFile Name
    b.tablespace_name "TABLESPACE_NAME",                   -- TableSpace Name
    b.bytes / 1024 "TOTAL SIZE(KB)",                       -- 총 Bytes
    ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 "USED(KB)",   -- 사용한 용량
    (sum(nvl(a.bytes,0))) / 1024 "FREE SIZE(KB)",          -- 남은 용량
    (sum(nvl(a.bytes,0)) / (b.bytes)) * 100 "FREE %"       -- 남은 %
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

Tablespace size change

alter database datafile '/app/oracle/oradata/ORCL/system01.dbf' resize 15g;

Tablespace create

CREATE TABLESPACE ISM
     DATAFILE 'D:\app\Jin\oradata\JIN\ISM.dbf' SIZE 300M
     DEFAULT STORAGE
          (INITIAL    10K
           NEXT       10K
           MINEXTENTS 2
           MAXEXTENTS 50
           PCTINCREASE 50);

User create

CREATE USER userid identified by password;

User info change

ALTER USER userid INDENTIFIED BY passowrd
DEFAULT TABLESPACE tablespacename
TEMPORARY TABLESPACE temporary_tablespacename
ACCOUNT {LOCK|UNLOCK}

User auth grant, revoke

    GRANT resource, connect, dba to userid;
    GRANT select, insert, update, delete ON schema TO userid; 
    REVOKE select, insert, update, delete ON schema TO userid;
    -- ORA-01950
    ALTER USER userid default tablespace users quota unlimited ON users;

Table Analyze(row)

@./rdbms/admin/catqm.sql
SELECT table_name
     , num_rows     -- 통계정보 건수
     , TO_NUMBER(
       dbms_xmlgen.getxmltype('SELECT COUNT(*) c FROM ' || table_name).Extract('//text()')
       ) num_rows2  -- 실제측정 건수
FROM user_tables