[oracle] 基本操作

–LOCK 卡住
select t2.sid || ‘,’ || t2.serial#,b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b,v$session t2
where b.object_id=l.object_id and l.session_id=t2.sid
alter system kill session ‘1154,41385’;
合體
select ‘alter system kill session ”’ || t2.sid || ‘,’ || t2.serial# || ”’ ;’ sql, object_name
from v$locked_object l, dba_objects b,v$session t2
where b.object_id=l.object_id and l.session_id=t2.sid;
— 密碼過期
set oracle_sid=xe
sqlplus / as sysdba
alter user [username] identified by [password]; (改密碼)
alter user [username] account unlock; (解鎖定)
alter user PUBA identified by puba;
第一次建帳號 需執行以下指令
grant create session to DSB;
alter user PUBA account unlock;
create synonym a17a.wsst835 for fdca.wsst835;
GRANT SELECT ON PUBA.LNDC802 TO FDCA;
— 建 TABLESPACE
CREATE TABLESPACE PUBA_IDX datafile ‘D:/A17A/PUBA_IDX’ size 50m AUTOEXTEND ON NEXT 204800000 MAXSIZE 8192M;
— TABLE 在哪個 TABLESPACE 內
select owner, table_name, tablespace_name
from dba_tables
WHERE TABLE_NAME LIKE ‘LND%’;
— 修正至該對應的 TABLESPACE
select ‘ALTER TABLE ‘ || OWNER || ‘.’ || TABLE_NAME || ‘ MOVE TABLESPACE ‘ || OWNER || ‘_DAT;’ SQL,
owner, table_name,tablespace_name
from dba_tables
WHERE OWNER = ‘PUBA’;
— 修改 TABLESPACE
ALTER TABLE PUBA.LNDC801 MOVE TABLESPACE PUBA_DAT;
TABLESPACE A17A_LND_DAT Pctfree 5 Storage (Initial 1M Next 32M); — PUBA_DAT FDCA_DAT
TABLESPACE A17A_LND_IDX Pctfree 5 Storage (Initial 1M Next 16M); — PUBA_IDX FDCA_IDX
— TABLESPACE 放在哪個資料夾下
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM DBA_DATA_FILES;
— TABLESPACE 的大小
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,
NVL(FLOOR(MAX_EXTENTS/(1024*1024)), ‘0’) || ‘G’ MAX_SIZE, PCT_INCREASE
FROM DBA_TABLESPACES;