Oracle常见命令大全

-- 查询表空间
SELECT * FROM DBA_TABLESPACES dt ;

-- 查询文件
SELECT * FROM DBA_DATA_FILES ddf ;

-- 创建表空间
CREATE TABLESPACE TBS_NFSE_FUSION DATAFILE '/u01/oracle/app/db/crmdb/nfse_fusion_data.dbf' SIZE 10240m;

CREATE TABLESPACE TBS_IDX_NFSE_FUSION DATAFILE '/u01/oracle/app/db/crmdb/nfse_fusion_idx.dbf' SIZE 5120m;

-- 给用户指定默认表空间
ALTER USER NFSE DEFAULT tablespace TBS_IDX_NFSE_FUSION;

-- 给用户授权表空间
GRANT CREATE SESSION, CREATE TABLE,unlimited tablespace TO nfse;

SELECT current_scn FROM v$database;
-- 147415125

SELECT * FROM X.CST_RGHT_ORDR_TBL AS OF scn 147415125;

SELECT sql_id, SQL_TEXT, module, MODULE_HASH,FIRST_LOAD_TIME  FROM v$sqlarea WHERE sql_text LIKE '%cst_rght_ordr_tbl%'
AND (sql_text like '%delete%'OR SQL_text LIKE '%update%')
AND FIRST_LOAD_TIME BETWEEN '2021-05-28/08:34:27' AND '2021-06-03/15:34:27' ORDER BY FIRST_LOAD_TIME;

-- 2021-06-03 14:54:53
SELECT *  FROM v$sqlarea WHERE sql_text LIKE '%cst_rght_ordr_tbl%'
WHERE fi;

SELECT * FROM v$sqlarea WHERE sql_text LIKE '%cst_rght_ordr_tbl%';

SELECT * FROM v$session WHERE sql_id = 'g40hh6trc63xu';

SELECT * FROM v$session;
SELECT * FROM sys."V_$SESSION" WHERE sql_id = 'g40hh6trc63xu';

SELECT * FROM DBA_HIST_SQLSTAT WHERE sql_id = 'g40hh6trc63xu';
SELECT * FROM DBA_HIST_SQLTEXT WHERE sql_id = 'g40hh6trc63xu';
SELECT * FROM DBA_HIST_SQL_PLAN WHERE sql_id = 'g40hh6trc63xu';
SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY WHERE sql_id = 'g40hh6trc63xu';

-- 2021-06-03 14:54:53
SELECT sysdate FROM dual;

SELECT t1.SQL_ID, t1.SQL_TEXT, t1.LAST_ACTIVE_TIME FROM v$sqlarea t1 WHERE t1.SQL_FULLTEXT LIKE '%cst_rght_ordr_tbl%' AND t1.PARSING_SCHEMA_NAME = 'ZXYF';

SELECT * FROM v$session t2  WHERE t2.username='X'  AND t2.program = 'plsqldev.exe' ORDER BY t2.LOGON_TIME desc;


SELECT UNIQUE t1.SQL_ID, t1.SQL_TEXT, t1.LAST_ACTIVE_TIME,t3.MACHINE, t3.PROGRAM, t2.OSUSER
FROM v$sqlarea t1 
INNER JOIN DBA_HIST_ACTIVE_SESS_HISTORY t3 ON t1.SQL_ID=t3.SQL_ID
INNER JOIN v$session t2 ON t3.MACHINE = t2.MACHINE
WHERE t1.SQL_TEXT LIKE '%cst_rght_ordr_tbl%';

SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY;


SELECT  t1.SQL_ID, t1.SQL_TEXT, t1.LAST_ACTIVE_TIME, t1.module FROM v$sqlarea t1 WHERE t1.SQL_TEXT LIKE '%cst_rght_ordr_tbl%' AND t1.SQL_TEXT LIKE '%delete%';

SELECT * FROM v$session t2 WHERE t2.SQL_ID='dbk24dn3dhm74';
SELECT * FROM DBA_HIST_ACTIVE_SESS_HISTORY t3 ORDER BY SQL_EXEC_START desc;

SELECT * FROM v$sqlarea t3 WHERE t3.SQL_TEXT LIKE '%dbk24dn3dhm74%';

SELECT * FROM v$sqlarea t1  WHERE t1.PARSING_SCHEMA_NAME='CRMP32'
ORDER BY t1.LAST_ACTIVE_TIME ;

SELECT * FROM table(dbms_xplan.display);

explain plan FOR SELECT
	*
FROM
	----

GRANT CREATE SESSION TO NFSE;

create directory dumpdir as '/data/backup';greate read,write on directory dumpdir to system;

--expdp system/oracle directory=dumpdir dumpfile=1.dmp logfile=1.log schemas=zxyf

SELECT * FROM DBA_DIRECTORIES dd ;

-- sql语句
SELECT * FROM DBA_DIRECTORIES dd ;
create directory EXPDT as '/home/oracle/dump';greate read,write on directory dumpdir to system;

## 在oracle服务器上执行
expdp \'/ as sysdba\' schemas=zxyf dumpfile=zxyf-2.dmp logfile=zxyf-2.log DIRECTORY=EXPDT;

impdp \'/ as sysdba\' schemas=zxyf dumpfile=zxyf-2.dmp logfile=zxyf-2.log DIRECTORY=EXPDT;

ANALYZE TABLE X.TRAN_SEQ_NO_LOG COMPUTE STATISTICS;