Oracle Database常用命令集

1.    Oracle 查看数据库字符集和客户端字符集 SQL 语句

        查询数据库字符集：

        select * from nls_database_parameters t where t.parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

        查询客户端字符集：

        SELECT USERENV('language') FROM DUAL;

2.    查看log和logfile大小

        SELECT GROUP#,members,BYTES / 1024 / 1024 AS "SIZE M",STATUS,ARCHIVED FROM v$log;

        SELECT * from v$logfile;

        col member for a45 

        select group#, status, type, member from v$logfile;

        SELECT GROUP#,
 members, BYTES/1024/1024 AS "SIZE M",
 STATUS,
 ARCHIVED
 FROM
 v$log;

3.    数据库查看版本

        set linesize 100

        col CON_ID for a45

        col BANNER for a100

        select * from v$version;       

查看安装了哪些选项
 col PARAMETER format a60
 col VALUE format a10
 select * from sys.v_$option;

4.    数据库勒索病毒查询

select * from dba_objects where object_name like '%DBMS_SUPPORT_INTERNAL%';   

select * from dba_objects where object_name like '%DBMS_ SYSTEM_INTERNAL %';

select * from dba_objects where object_name like '%DBMS_ CORE_INTERNAL%';

select * from dba_objects where object_name like '%DBMS_STANDARD_FUN9%';

select * from dba_objects where object_name like '%DBMS_SUPPORT_INTERNAL%';

select * from dba_objects where object_name like '%DBMS_ SYSTEM_INTERNAL%';

select * from dba_objects where object_name like '%DBMS_ CORE_INTERNAL%';

查看输出是否含有上面7个名称，下条命令相同

select * from dba_objects where object_name like '%INTERNAL%';   

select 'DROP
TRIGGER '||owner||'."'||TRIGGER_NAME||'";' from dba_triggers where
TRIGGER_NAME like  'DBMS_%_INTERNAL%' union all select 'DROP PROCEDURE
'||owner||'."'||a.object_name||'";' from dba_procedures a where
a.object_name like 'DBMS_%_INTERNAL% '; 

正常没输出结果。

5.    数据闪回

        备份数据

        create table au_user_bak as select * from au_user;

        启动行移动
        alter table au_user enable row movement;
        恢复数据
        flashback table au_user to timestamp to_timestamp('2024-01-09 11:00:00', 'YYYY-MM-DD HH24:MI:SS');

        关闭行移动

        alter table au_user disable row movement;

6.    使用logminer查看归档日志

@?/rdbms/admin/dbmslm.sql;
@?/rdbms/admin/dbmslmd.sql;

exec sys.dbms_logmnr.add_logfile(logfilename => '/dbfile/archivelog/1_607438_963928319.arc',options => dbms_logmnr.new);
exec sys.dbms_logmnr.start_logmnr(options => sys.dbms_logmnr.dict_from_online_catalog);

select seg_owner,count(*) from v$logmnr_contents group by seg_owner;

select count(1),substr(sql_redo,1,60) from v$logmnr_contents group by substr(sql_redo,1,60) order by count(1) desc ;

增加日志

exec sys.dbms_logmnr.add_logfile(logfilename=>'/dbfile/archivelog/1_607439_963928319.arc');

create table logmnr_tab as select * from V$LOGMNR_CONTENTS;

查看日志分析结果，说明：日志分析结果只能在当前会话查看。

显示DML分析结果
select operation,sql_redo,sql_undo from v$logmnr_contents where seg_name='TEMP';

显示DDL分析结果
select to_cahr(timestamp,'yyyy-mm-dd hh23:mi:ss') time,sql_redo from v$logmnr_contents where sql_redo like '%create%' or sql_redo like '%create%';

显示在用字典文件
select db_name,filename from v$logmnr_dictionary;

结束LogMiner
execute dbms_logmnr.end_logmnr;

7.    APEX测试Windows AD

# ldapsearch -x -H ldap://192.168.164.2 -D "CN=weishi,CN=Users,DC=wagne,DC=com" -w 1qaz@WSX -b "DC=wagne,DC=com"

8.    --更新terry该GP用戶信息

update TIAN_GSDSN
set MONTH='2024.1',
zxdate = to_date('2012/06/19','YYYY/MM/DD'), --日期格式化
zxuser = DEFAULT --用DEFAULT提供的默认值
where zx01 = 'terry';

desc TIAN_GSDSN

update TIAN_GSDSN set MONTH='2024.1'

delete TIAN_GSDSN where 序号 is NULL and 合同号 is NULL and 物料号 is NULL

select count(*) from TIAN_GSDSN_CP

select * from TIAN_GSDSN where 序号 is NULL