oracle 每3位加逗号,ORACLE 一句话问答（三）

1、如何按执行顺序查看执行计划：

select id,parent_id,operation

from (select level lvl,id,parent_id,lpad('',level) || operation || '' || options || '' || object_name as operation

fromplan_table

start with id = 0

connect by prior id = parent_id

)

order by lvl desc,id;

2、查看最近执行的sql语句：

select /* recentsql */ sql_id,child_number,hash_value,address,executions,sql_text

from v$sql

where parsing_user_id = (select user_id from all_users where username = 'SCOTT')

and command_type in (2,3,6,7,189) and UPPER(sql_text) not like UPPER('%recentsql%');

3、v$sql,v$sqlarea,v$sqltext这三个视图的区别：

v$sql视图存放当前存放在library cache中的语句；v$sqlarea 可以看做是v$sql根据sqltext做了group by之后的信息；

而v$sqltext存放的是完整的sql以及HASH_VALUE。

select sql_text ,sql_id,child_number,hash_value,address,executions from v$sql where upper(sql_text) like '%EMPLOYEES%';

select * from v$sqltext where hash_value='3621103299' order by piece ;

4、通过CONNECT BY生成序列：

select cast(trunc(dbms_random.value(1,999),2) as varchar2(10)) from dual connect by rownum <= 100;

5、如何修改表字段类型：

当字段没有数据或者要修改的新类型和原类型兼容时，可以直接modify修改；当字段有数据并用要修改的新类型和原类型不兼容时，要先新建字段再转移字段内容。

6、如何将满足条件的数据拼接成字符串(逗号隔开)：

select parent,max(wm_concat_id) from

(select parent,WMSYS.WM_CONCAT(ID) OVER(PARTITION BY PARENT ORDER BY ID) wm_concat_id

from a_organ

)

group by parent

order by parent

7、如何结束争用同一资源的进程：

1)查询资源进程：

select s.username,s.osuser,s.sid,s.serial#,p.spidfromv$session s,v$process p

where s.paddr=p.addr and s.username is not null;

2)杀死进程：

alter system kill session 'sid,serial#';

LINUX:kill -9 spid;

WINDOWS:orakill sid thread;

8、如何增大闪回恢复区：

SQL> show parameter db_recovery_file_dest_size;

SQL> alter system set db_recovery_file_dest_size=3G;

9、如何降低高水平位：

高水线直接决定了全表扫描所需要的I/O开销。使用delete操作不会降低高水位线，但使用truncate 会重置高水位线。定期使用alter table tab_name shrink space cascade 有效减少该对象上的I/O开销。

10、使用impdp导入时提示表空间不存在：

使用TRANSFORM选项去掉表空间和存储子句，这样表、索引等对象都导入到用户默认表空间。

impdp topicis/topicis@hbgbk schemas=topicis directory=dir_s_dp dumpfile =xnjjaj.dmp logfile=xnjjaj.log transform=segment_attributes:n table_exists_action=truncate

11、如何为一条sql语句生成awr报告：

运行脚本为$ORACLE_HOME/RDBMS/ADMIN/awrsqrpt.sql，按提示输入特定SQL的ID值即可。

12、 如何获取数据库DDL的创建语句：

IMPDP工具给我们提供了SQLFILE的命令行选项，只获取DDL语句，并未真正的执行数据导入：

impdp hr/hr directory=mig_dir dumpfile=expdp_hr.dmp logfile=impdp_hr.log schemas=hr sqlfile=get_ddl.sql;

IMP工具使用show=y log=scripts.sql的方式,可以看到清晰的ddl脚本：

imp hr/hr file=expdp_hr.dmp show=y log=get_ddl.sql buffer=20480000fromuser=hrtouser=sm

13、如何修改用户密码：

select username,password from dba_users;

alter user system identified by manager;

alter user system identified by values '2D5Array4E86FArray3B17A1';

14、如何禁用操作系统认证登录：

SYS用户是Oracle中权限最高的用户，而SYSTEM是一个用于数据库管理的用户。SYS登录认证分为操作系统认证和口令文件认证。

要将认证方式设置为操作系统认证：

1)修改参数REMOTE_LOGIN_PASSWORDFILE为NONE；

2)修改SQLNET.ORA文件，添加这一行：SQLNET.AUTENTICATION_SERVICES=(NTS)；

3)重新启动数据库。

要将认证方式设置为口令文件认证：

1)修改参数REMOTE_LOGIN_PASSWORDFILE为EXCLUSIVE或SHARED。其中，exclusive表示仅有一个实例可以使用口令文件。shared表口令文件可以供多个实例使用；

2)修改SQLNET.ORA文件，在SQLNET.AUTENTICATION_SERVICES=(NTS)前加#号，即#SQLNET.AUTENTICATION_SERVICES=(NTS)；

3)重新启动数据库。

15、如何恢复被drop的表：

drop后的表被放在回收站user_recyclebin里，而不是直接删除掉。通过查询回收站user_recyclebin获取被删除的表信息，然后使用语句：

flashback table to before drop [rename to ];

将回收站里的表恢复。若要彻底删除表，则使用语句：drop table purge;

16、日志中提示Thread 1 cannot allocate new log：

增加日志容量或日志组：

select * from v$logfile;

alter database add logfile group 4 ('e:\app\administrator\oradata\hbgbk\redo04_1.log','e:\app\administrator\oradata\hbgbk\redo04_2.log') size 500m;

alter system switch logfile;

17、如何解决归档日志过多的问题：

如果归档位置放在flash_recovery_area下，超出最大空间后需要清除对应的归档日志，在删除归档日志后，必须用RMAN维护控制文件，否则空间显示仍然不释放。

删除五天之前的所有archivelog：

rman>delete archivelog until time 'sysdate-5' ;

18、如何切换归档模式：

如果切换数据库到归档模式，切换后需要做一次备份，非归档日志模式下产生的备份日志对于归档模式已经不可用?

select name,log_mode from v$database;

archive log list;

startup mount;

alter database archivelog; (切换到非归档模式：noarchivelog)

19、解决IMP导出不导出空表问题：

show parameter deferred_segment_creation;

alter system set deferred_segment_creation=false;

20、如何快速复制表结构：

create table tab_name as select * from old_tab_name where 1=2;

create table as 时，表上的索引、触发器等不会同时被复制，非空约束和defealt值也会丢失。要得到表完整的结构最好还是使用dbms_metadata.get_ddl。