oracle常用命令汇总
system
Wq1995119
1)创建用户:
Create user C##WQ identified by 123456;
2)为用户授权:
grant aq_administrator_role to C##WQ with admin option;
grant dba to C##WQ with admin option;
grant mgmt_user to C##WQ;
grant create materialized view to C##WQ;
grant create table to C##WQ;
grant global query rewrite to C##WQ;
grant select any table to C##WQ;
grant unlimited tablespace to C##WQ with admin option;
//drop user CESHIA cascade;(删除用户)
select sid,serial# from v$session where username='C##WQ'
alter system kill session '70,23621';
alter system kill session '133,62313';
drop user C##WQ cascade;
修改system密码
运行cmd命令行
?
录入
sqlplus /nolog? 无用户名登录
??
conn /as sysdba? 连接到数据本地数据
????
alter user system identified by password;?修改System 密码?为password
或者打开sqlplus软件:
imp CESHI/123456@orcl file=c:\qc.dmp log=dible_db full=y ignore=y
imp C##CESHI/123456@192.168.0.157:1521/orcl file=D:\158.dmp log=dible_db full=y ignore=y(远程倒入)
exp C##WQ/123456@192.168.0.73:1521/orcl file=d:\2019-1-12.dmp feedback=10000 buffer=10240000(远程倒出)
UPDATE SYS_USER set PASSWORD='12bbf9ea33d79e0da04df1fd042c5a3e66a521dc98688ef5e6e23a54' where id='t1'
33 19
WIN-B0GC1KLS48C
123456
-Dfile.encoding=UTF-8"
解决导出空表问题空表
oracle空表导出前,先通过此语句,生成所有空表的分配表空间的sql,把sql语句复制出来,全部执行一遍就可以了
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
exp c##bskj/123456@172.24.240.116:1521/orcl file=c:\all.dmp statistics=none
dbms_lob.substr(remarks)
select ID, VN_ID, PC_NAME, PA_NAME, NAME, FILE_NO, UPDATE_COUNT, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE, DEL_FLAG, USEABLE, COMPANY_ID, SDA_NAME, dbms_lob.substr(remarks), INDEX_TEMP_NAME ,REVISION_DATE from QC_SD_INDEX_TEMP
exp c##nis/yanfabu123@192.168.0.79:1521/orcl tables=(AE%) rows=n file=C:\Users\Administrator\Desktop\ae.dmp 导出什么类型开头的空表
exp c##nis/yanfabu123@192.168.0.79:1521/orcl tables=(AE%) file=C:\Users\Administrator\Desktop\ae.dmp 导出表数据
查看哪些类型的表
SELECT
a.TABLE_NAME,b.COMMENTS
FROM
user_tables a,user_tab_comments b
WHERE
a.TABLE_NAME=b.TABLE_NAME
and a.TABLE_NAME like 'AE_%'
ORDER BY
TABLE_NAME
查看
exp c##test/123456@192.168.0.178:1521/orcl tables=(ae_sys_options,ae_sys_aequestion,ae_sys_questions,ae_sys_content) file=E:\1221.dmp
--处理表空间不足 ORA-01658
alter tablespace USERS add datafile 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS03.DBF' size 20000M;
ALTER DATABASE DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS03.DBF' RESIZE 1024M;
ALTER DATABASE DATAFILE 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS03.DBF' AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;
update ae_rep_info set PAT_YEAR = replace(PAT_YEAR,'岁',''); 批量替换字段中的数据 把字段中的岁,改完空
update ae_sys_options set name = replace(name,'院感科','院感-预报科');
update ae_sys_content set HTML = replace(HTML,'院感科','院感-预报科');
update ae_sys_options set name = replace(name,'药学部','药剂科');
update ae_sys_content set HTML = replace(HTML,'药学部','药剂科');
update ae_sys_options set name = replace(name,'保卫科','安防科');
update ae_sys_content set HTML = replace(HTML,'保卫科','安防科');
update ae_sys_options set name = replace(name,'设备科','设备物资供应科');
update ae_sys_content set HTML = replace(HTML,'设备科','设备物资供应科');
update ae_sys_aename set NAME='1'||NAME