| Manage Oracle DB |
|---|
| |
| [2015-05-21] |
| |
Oracle Database Documentations
12C Database Administration
-- manage SID
Verify process ps -ef | grep pmon
export ORACLE_SID=dev1
lsnrctl status
lsnrctl stop
lsnrctl start
-- import dmp data
sude to oracle
export ORACLE_SID=dev1
-- find DATA_PUMP_DIR
SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';
/apps/oracle/admin/dev1/dpdump
-- extract dmp ddl to remap both schema and tablespace
impdp system/pswd dumpfile=dev_file.dmp logfile=dev_file_extra.dmp.log directory=DATA_PUMP_DIR sqlfile=dev_file.txt
-- run impdp to import data, remap schema and tablespace
impdp system/pswd directory=DATA_PUMP_DIR REMAP_SCHEMA=user1:newUser REMAP_TABLESPACE=DEV_DATA:NEW_DATA dumpfile=dev_file.dmp logfile=dev_file.dmp.log
-- verify data loaded in database
-- find total database size
select sum(bytes)/1024/1024 MB from
( select sum (bytes) bytes from v$datafile
union
select sum (bytes) from v$tempfile
union
select sum (bytes * members) from v$log
);
-- check database file size
select * from v$datafile
select * from v$tempfile
select * from v$log
-- tablespace and data files
select * from dba_tablespaces;
select * from dba_data_files;
-- find all foreign keys in a schema
select * from USER_CONSTRAINTS where constraint_name like 'FK%';
-- find which column is used by foreign keys for a table
SELECT a.table_name, a.column_name, a.constraint_name, c.owner,
c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND a.table_name = :tableName;
-- check if a procedure is used by others
SELECT * FROM all_source
where UPPER(TEXT) like UPPER('%%');
-- find which session slow
select user, seq#, event, wait_time, seconds_in_wait, state
from v$session order by seconds_in_wait desc;
-- to find a session
select username, sid, serial#, status, osuser, machine, program,
to_char(logon_time, 'YYYYMMDD HH24:MI:SS') login_time
from v$session where username is not null order by username;
-- create kill a session procedure and let user kill their sessions
-- (login as sys)
create or replace procedure kill_session
( v_sid number, v_serial number ) as
v_varchar2 varchar2(100);
begin
execute immediate 'ALTER SYSTEM KILL SESSION '''
|| v_sid || ',' || v_serial || '''';
end;
/
grant execute on kill_session to public;
grant select on v_$session to public;
-- ask user to run find session script, and exec kill_session(v_sid, v_serial)
-- basic privs checks for a user
select * from dba_SYS_PRIVS where grantee='USER1' order by 1;
select * from dba_TAB_PRIVS where grantee='USER1' order by 1;
select * from dba_ROLE_PRIVS where grantee='USER1' order by 1;
-- check and fix invalid objects
select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects
where status = 'INVALID'
and object_type in ('PACKAGE','FUNCTION','PROCEDURE');
-- modify alter statement for other invalid object types
-- to check dbms jobs
select * FROM ALL_SCHEDULER_JOBS;
-- setup a dbms job. drop and recreate the job if exists
DECLARE l_cnt NUMBER;
BEGIN
SELECT COUNT(*) into l_cnt FROM ALL_SCHEDULER_JOBS where job_name = 'MY_JOB';
IF( l_cnt > 0 ) THEN
DBMS_SCHEDULER.drop_job (job_name => 'SCHEMA.JOB_NAME');
END IF;
-- run at every 15 minutes interval
SYS.DBMS_SCHEDULER.create_job (
job_name => 'SCHEMA.JOB_NAME',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN shcema.proc_name; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=MINUTELY;interval=15',
end_date => NULL,
enabled => TRUE,
comments => 'Create job for proc name every 15 mins');
DBMS_SCHEDULER.ENABLE
(name => 'SCHEMA.JOB_NAME');
END;
|
[说明] Manage Oracle Database, SID, import dump file, manage SQLs |
|
| 声明: 海外华人网所有刊载文章仅供参考，不构成投资或行动建议。欢迎免费引用，引用时需注明文章来源：海外华人网(haiwaihuaren.com) |
