请问如何分辨某个用户是从哪台机器登陆oracle的?
select machine , terminal from v$session;
怎样计算一个表占用的空间的大小？
select owner,table_name,
num_rows,
blocks*aaa/1024/1024 "size m",
empty_blocks,
last_analyzed
from dba_tables
where table_name='xxx';
here: aaa is the value of db_block_size ;
xxx is the table name you want to check
如何查看系统被锁的事务时间？
select * from v$locked_object ;
怎么获取有哪些用户在使用数据库
select username from v$session;
如何解锁?
alter system kill session ‘sid,serir#’;
如何在字符串里加回车？
select 'welcome to visit'||chr(10)||'www.csdn.net' from dual ;
中文是如何排序的？
oracle9i之前，中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置nls_sort值
schinese_radical_m 按照部首（第一顺序）、笔划（第二顺序）排序
schinese_stroke_m 按照笔划（第一顺序）、部首（第二顺序）排序
schinese_pinyin_m 按照拼音排序
如何改变win中sql*plus启动选项？
sql*plus自身的选项设置我们可以在$oracle_home/sqlplus/admin/glogin.sql中设置。
怎样修改oracel数据库的默认日期?
alter session set nls_date_format='yyyymmddhh24miss';
or
可以在init.ora中加上一行
nls_date_format='yyyymmddhh24miss'
如何将小表放入keep池中?
alter table xxx storage(buffer_pool keep);
如何检查是否安装了某个patch?
check that orainventory
如何使select语句使查询结果自动生成序号?
select rownum,col from table;
怎么可以快速做一个和原表一样的备份表?
create table new_table as (select * from old_table);
怎样解除procedure被意外锁定?
alter system kill session ,把那个session给杀掉，不过你要先查出她的session id
or
把该过程重新改个名字就可以了。
sql reference是个什么东西？
是一本sql的使用手册，包括语法、函数等等，oracle官方网站的文档中心有下载.
如何查看数据库的状态?
unix下
ps -ef | grep ora
windows下
看服务是否起来
是否可以连上数据库
怎样查看oracle中有哪些程序在运行之中？
查看v$sessions表
怎么可以看到数据库有多少个tablespace?
select * from dba_tablespaces;
如何修改oracle数据库的用户连接数？
修改initsid.ora，将process加大，重启数据库.
如何查出一条记录的最后更新时间?
可以用logminer 察看
如何在pl/sql中读写文件？
utl_file包答应用户通过pl/sql读写操作系统文件。
怎样把“&”放入一条记录中？
insert into a values (translate ('at{&}t','at{}','at'));
exp 如何加query参数？
exp user/pass file=a.dmp tables(bsempms)
query='"where emp_no="'s09394"'"" ﹔
关于oracle8i支持简体和繁体的字符集问题？
zhs16gbk可以支
data guard是什么软件？
就是standby的换代产品
如何创建spfile?
sql> connect / as sysdba
sql> select * from v$version;
sql> create pfile from spfile;
sql> create spfile from pfile='e:"ora9i"admin"eygle"pfile"init.ora';
文件已创建。
sql> create spfile='e:"ora9i"database"spfileeygle.ora' from pfile='e:"ora9i"admin"eygle"pfile"init.ora';
文件已创建。
如何单独备份一个或多个表？
exp 用户/密码 tables=(表1,…,表2)
如何单独备份一个或多个用户？
exp system/manager owner=(用户1,用户2,…,用户n) file=导出文件
如何对clob字段进行全文检索？
select * from a where dbms_lob.instr(a.a,'k',1,1)>0;
如何显示当前连接用户?
show user
如何查看数据文件放置的路径 ?
col file_name format a50
sql> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
如何查看现有回滚段及其状态 ?
sql> col segment format a30
sql> select segment_name,ccbzzp,tablespace_name,segment_id,file_id,status from dba_rollback_segs
登陆sqlplus
sqlplus / as sysdba
查找用户
select * from dba_users;
-- 查看所有用户：
select * from all_users;
-- 查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
-- 查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;
-- 查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
-- 查看所有角色：
select * from dba_roles;
-- 查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
-- 查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS;
-- 查看当前有哪些用户正在使用数据
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
查找工作空间的路径
select * from dba_data_files;
删除当前用户下的所有表
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE
SQL> alter system set deferred_segment_creation=false;
系统已更改。
SQL> show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE
SQL> select 'drop table '||table_name||';' from cat where table_type='TABLE';
删除用户及表空间
-- 删除用户
drop user 用户名称 cascade;
-- 删除表空间
drop tablespace 表空间名称 including contents and datafiles cascade constraint;
--例如：删除用户名成为LYK，表空间名称为LYK
--删除用户，及级联关系也删除掉
drop user LYK cascade;
--删除表空间，及对应的表空间文件也删除掉
drop tablespace LYK including contents and datafiles cascade constraint;
drop tablespace LYK_TMP including contents and datafiles cascade constraint;
查询数据库连接SID
SELECT sid,serial#,username FROM v$session WHERE username = 'SYSTEM';
ALTER SYSTEM KILL SESSION '148, 2329';
查看数据库连接
--查看数据库连接
SELECT * FROM V$SESSION;
--查看数据库的连接数
select count(*) from v$session;
--查询oracle的并发连接数
select count(*) from v$session where status='ACTIVE';
--数据库允许的最大连接数
select value from v$parameter where name = 'processes' ;
--修改最大连接数:
alter system set processes = 300 scope = spfile;
--查看不同用户的连接数
select username,count(username) from v$session where username is not null group by username;
查询oracle哪个表被锁
-- 查询oracle那个表被锁
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
查询正在执行的sql
-- 查询正在执行的sql
select a.program, b.spid, c.sql_text, c.SQL_ID
from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value
and a.username is not null;
查询某PID占用资源
--查询那个PID占用资源
SELECT sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN
(SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr =
(SELECT addr FROM v$process c WHERE c.spid = '26025'))
ORDER BY piece ASC;
数据库导入导出
-- 导出数据库时可能操作系统编码和数据库不一致会导致EXP-00091错误，
select *
from nls_database_parameters t
where t.parameter = 'NLS_CHARACTERSET';
select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
-- 根据⑴查出的NLS_CHARACTERSET（AL32UTF8）来设定exp的环境变量：
set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
echo $NLS_LANG
-- 导出数据库
exp HM_DEV/123456 owner=HM_DEV file=HM_DEV_2019_01_17.dmp;
exp green/light@MSSPS file=e:\ren.dmp tables=(tab_mobilephone_recharge,trade_detail,terminals,shops,locations,citys) query=\"where rownum<=5000\"
-- 导入数据库
imp HM_DEV/123456@orcl file=HM_DEV_2019_01_17.dmp ignore=y FULL=Y;
数据导出
1、 将数据库test完全导出,用户名system 密码manager 导出到d:/daochu.dmp中
exp system/manager@test file=/u01/app/daochu.dmp full=y
2 、将数据库中system用户与sys用户的表导出
exp system/manager@test file=/u01/app/daochu.dmp owner=\(system,sys\);
3 、将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@testdb2 file=/u01/app/newsmgnt.dmp tables=\(inner_notify,notify_staff_relat\);
4 、将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@test file=/u01/app/daochu.dmp tables=\(table1\) query=/" where filed1 like 00%/"
上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。 也可以在上面命令后面 加上 compress=y 来实现。
数据导入
1 、将/u01/app/daochu.dmp 中的数据导入 test数据库中。
imp aichannel/aichannel@hust full=y file=/u01/app/newsmgnt.dmp ignore=y
上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。 在后面加上 ignore=y 就可以了。
2 、将/u01/app/aochu.dmp中的表table1 导入
imp system/manager@test file=/u01/appdaochu.dmp tables=(table1)
基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入
解决oracle11g 空表不能exp导出的问题
show parameter deferred_segment_creation
NAME TYPE VALUE
------------------------------------ ----------- -------
deferred_segment_creation boolean TRUE
-- 数据库参数deferred_segment_creation=true，将导致dmp备份无法导出空表结构；
alter system set deferred_segment_creation=false;
-- 此方法只对以后的表有效，之前的表还是没分配空间。
-- 1、先查询一下当前用户下的所有空表 ,优先使用segment_created = 'NO'
select * from user_tables where segment_created = 'NO';
--select table_name from user_tables where NUM_ROWS=0;
-- 2、批量生成修改语句
select 'alter table ' || table_name || ' allocate extent;' from user_tables where segment_created = 'NO';
--select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
-- 3、执行修改语句
alter table 表名 allocate extent;
ORACLE删除表
Oracle如果开启了 flash 功能, 当我们删除表drop table HM_TABLE; 后会出现类似BIN$dJ4cxvVNZVHgQAB/AQBx3w==$0表,
这个表还BIN$dJ4cxvVNZVHgQAB/AQBx3w==$0存在，可以使用命令：来恢复至删除前。
-- 来恢复至删除前
flashback table "BIN$dJ4hg1pva6/gQAB/AQByAw==$0" to before drop;
commit;
-- 如果想彻底删除一个表
drop table HM_TABLE purge;
commit;
-- 如果想清空flash存储已经删除的表，用命令：
purge recyclebin;
commit;
-- 如果想清除flash中指定的表，可以使用命令：
purge table HM_TABLE;
commit;
查询用户下的所有表名
select * from user_tab_comments;
查询用户下所有字段名
select * from user_col_comments;
导出当前用户的所有备注（包含表和字段）
SELECt 'COMMENT ON TABLE ' || TABLE_NAME || ' IS ' || '''' ||
nvl(COMMENTS, '') || ''';'
from user_tab_comments
UNION all
SELECt 'COMMENT ON COLUMN ' || TABLE_NAME || '.' || COLUMN_NAME || ' IS ' || '''' ||
nvl(COMMENTS, '') || ''';'
from user_col_comments;
Oracle查询SQL语句执行的耗时
select a.sql_text SQL语句,
b.etime 执行耗时,
c.user_id 用户ID,
c.SAMPLE_TIME 执行时间,
c.INSTANCE_NUMBER 实例数,
u.username 用户名,
a.sql_id SQL编号
from dba_hist_sqltext a,
(select sql_id, ELAPSED_TIME_DELTA / 1000000 as etime
from dba_hist_sqlstat
-- where ELAPSED_TIME_DELTA / 1000000 >= 1
) b,
dba_hist_active_sess_history c,
dba_users u
where a.sql_id = b.sql_id
and u.username = 'EIS_DEV'
and c.user_id = u.user_id
and b.sql_id = c.sql_id
and a.sql_text like '%SELECT%'
order by SAMPLE_TIME desc, b.etime desc;
执行过的SQL
SELECT SQL_ID, HASH_VALUE, ADDRESS, B.SQL_FULLTEXT, B.FIRST_LOAD_TIME
FROM V$SQLAREA B
ORDER BY B.FIRST_LOAD_TIME DESC;
正在执行的SQL
SELECT SSN.USERNAME,
SSN.SID,
SSN.SQL_ID,
SAA.ADDRESS,
SAA.HASH_VALUE,
SAA.SQL_FULLTEXT
FROM V$SESSION SSN, V$SQLAREA SAA
WHERE SSN.SQL_ADDRESS = SAA.ADDRESS
AND SSN.SQL_HASH_VALUE = SAA.HASH_VALUE;
读取磁盘次数最多的SQL
SELECT *
FROM (SELECT SQL_ID,
ADDRESS,
HASH_VALUE,
COMMAND_TYPE,
PARSING_USER_ID,
PARSING_SCHEMA_NAME,
EXECUTIONS,
SORTS,
DISK_READS,
BUFFER_GETS,
CPU_TIME,
SQL_FULLTEXT
FROM V$SQLAREA
ORDER BY DISK_READS DESC)
WHERE ROWNUM < 10;
消耗CPU时间最多的SQL
SELECT *
FROM (SELECT SQL_ID,
ADDRESS,
HASH_VALUE,
COMMAND_TYPE,
PARSING_USER_ID,
PARSING_SCHEMA_NAME,
EXECUTIONS,
SORTS,
DISK_READS,
BUFFER_GETS,
CPU_TIME,
SQL_FULLTEXT
FROM V$SQLAREA
ORDER BY CPU_TIME DESC)
WHERE ROWNUM < 10;
用户表空间及使用率
select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%'use
from (
select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name ) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name;
查看表空间是否为online
select * from v$tempfile;
查看普通数据文件是否扩展
select d.file_name,d.tablespace_name,d.autoextensible from dba_data_files d;
查看临时表空间是否可以扩展
select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;
临时表空间和数据表空间
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from dba_temp_files
union all
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from dba_data_files;
修改数据表空间大小
alter database datafile '/u01/app/oracle/product/11.2.0/db_1/EIS_QA.dbf' resize 2048m;
删除表空间
alter database datafile '/u01/app/oracle/product/11.2.0/db_1/EIS.dbf' drop;
修改临时表空间大小
alter database tempfile '/u01/app/oracle/product/11.2.0/db_1/EIS_TEMP_01.dbf' drop;
临时表空间添加新的临时数据文件
alter tablespace EIS add tempfile '/u01/app/oracle/product/11.2.0/db_1/EIS_ADD_01.dbf' size 400m;
临时文件自动扩展
alter database tempfile '/u01/app/oracle/product/11.2.0/db_1/EIMS_TMP.dbf' autoextend on next 5m maxsize unlimited;
关闭(启动)临时文件的自动增长
alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' autoextend off(on);
oracle中用户删除不了，ORA-01940提示 “无法删除当前已连接用户”
先锁定用户、然后查询进程号，最后删除对应的进程、在删除对应的用户
alter user EIS account lock;
SELECT * FROM V$SESSION WHERE USERNAME='EIS'；
alter system kill session 'xx, xx'
drop user xx cascade
Oracle 表添加索引
-- 查询索引
select index_name from all_indexes where table_name = 'BELL_ENT_UPDATE';
-- 创建索引
create index bell_ent_update_subid_idx on BELL_ENT_UPDATE(SUB_ID);
-- 删除索引
drop index bell_ent_update_subid_idx;
评论