oracle连接问题

如果代码连接不上查看对应监听配置sqlnet.ora的允许连接属性

SQLNET.AUTHENTICATION_SERVICES = (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT)

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.ALLOWED_LOGON_VERSION=8

plsql连接不上

ORACLE_HOME=D:\plsql\instantclient_11_2

PATH=D:\plsql\instantclient_11_2

TNS_ADMIN=D:\plsql\instantclient_11_2

 NLS_LANG=AMERICAN_AMERICA.ZHS16GBK/SIMPLIFIED CHINESE_CHINA.ZHS16GBK/ AMERICAN_AMERICA.AL32UTF8

修改数据库编码NLS_CHARACTERSET= ALTER /ZHS16GBK

监听配置

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

数据库连接不上启动提示 ORA-03113: 通信通道的文件结尾

 startup mount；

select * from v$log;报错得话执行下边

alter database open resetlogs; 提示选项仅在不完全数据库恢复后有效

recover database until time '2010-11-22 14:26:02';

alter database open resetlogs;

重新启动

***从本地导出远程数据库的dmp文件

先在本地数据库tnsnames.ora文件里边添加配置远程数据库的信息

然后用命令导出exp jyglxxpt/jyglxxpt2018@linkjsyt file=E:/sampleDB.dmp full=y

或者exp jyglxxpt/jyglxxpt2018@10.83.3.220:1521/orcl file=E:/sampleDB.dmp full=y

都行

只导表结构/只导数据 content=metadata_only/all/data_only

execute dbms_metadata_util.load_stylesheets

默认导出到安装路径下

不创建用户，指定未定义用户

imp u/p@orcl file='E:\dmp.dmp' log='E:\log.log' ignore=y rows=y fromuser=amlstpc2 feedback=10000 commit=y buffer=1024000  owner=user rows=n 最后加rows=n代表不要数据行

query='"where rownum < 10"'或者用colname !=xx

imp testtest/123456 indexfile=xxxx.sql  indexes=n grants=n constraints=n statistics=none file=data.dmp log=data.dmp.log fromuser=u1  touser=u2 parallel=8

INCLUDE/exclude=table:\"in/not in\(\'TEST_TABLE\')"不导出数据

EXCLUDE=TABLE:\"IN\'TTT\'\)\"排除表

query=test_user:\"where test_id=1\" 

query=\"where rownum\<2\" 

CONTENT=METADATA_ONLY只导出表结构

impdp system/123456@ocr  DUMPFILE=DATA.DMP  remap_schema=usersrc:test remap_tablespace=CRM:BYLZL table_exists_action=replace/skip/truncate directory=BYLZL

数据泵导入

expdp testtest/123456@ocr dumpfile=data.dmp version=11.2.0.1.0 network_link=www

expdp jyglxxpt/jyglxxpt2018@10.83.3.220:1521/orcl  full=y dumpfile=e:/wgfullexp.dmp  version=11.2.0.0;

EXCLUDE=TABLE:\"NOT IN ('xxxxx', 'wwwwww')\"导出方案只适合expdp

导出远程 （network_link=www）

新建xxx.par  执行expdp u/p xxx.par

directory=oracled dumpfile=GSZC.dmp logfile=GSZC.log SCHEMAS=aim3_gszccs reuse_dumpfiles=y network_link=www

客户端sqlplus下导入sql sqlplus amldb/amldb@orcl @C:\Users\sxshe\Desktop\eee.sql

https://zhidao.baidu.com/question/560259266835400684.html

修改用户名

SELECT * FROM user$

SELECT * FROM user$ WHERE user#=71

UPDATE USER$ SET NAME='新的用户名' WHERE user#=71;

COMMIT;

ALTER SYSTEM CHECKPOINT;

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER USER 新用户名 IDENTIFIED BY '密码';

--查看用户角色SELECT * FROM USER_ROLE_PRIVS;

--查看当前用户权限：select * from session_privs;

--查看所有用户所拥有的角色SELECT * FROM DBA_ROLE_PRIVS;

select * from USER_TAB_PRIVS

select * from dba_role_privs where grantee='GYRX_PLM';

 

查询历史执行过的sql

select * from v$sqlarea a where lower(a.SQL_TEXT) like '%delete%';

select * from v$sqltext a,v$sqlarea b where a.SQL_ID=b.SQL_ID and b.SQL_ID in('a') order by b.LAST_ACTIVE_TIME desc;

select * from sys.v_$session l,sys.v_$sql s where s.SQL_ID='cq53826tk4u3c' and l.USERNAME is not null;