oracle 相关命令 及 问题汇总

使用sqlplus登陆

sqlplus /nolog

 

查看用户

select USERNAME from dba_users;

select default_tablespace from dba_users;

 

连接数据库方式 

sqlplus / as sysdba

创建表空间

 

创建临时表空间
CREATE TEMPORARY TABLESPACE PPP_TEST_TMP
TEMPFILE '/data/oracle/oradata/orcl11g/PPP_TEST_TMP_01.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;


创建表空间

create tablespace PPP_TEST
logging
datafile  '/data/oracle/oradata/orcl11g/PPP_TEST_01.dbf'
size 716M
autoextend on
next 307M maxsize 3276M
autoallocate             
extent management local 
DEFAULT NOCOMPRESS
segment space management auto; 


创建用户 PPP_TEST 密码 PPP_TEST

create user PPP_TEST identified by "PPP_TEST" default tablespace PPP_TEST temporary tablespace PPP_TEST_TMP profile DEFAULT;


给用户授权

GRANT 
CREATE SESSION,CREATE ANY TABLE,CREATE ANY VIEW,CREATE ANY INDEX,CREATE ANY PROCEDURE,
	ALTER ANY TABLE,ALTER ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,DROP ANY INDEX,DROP ANY PROCEDURE,
	SELECT ANY TABLE,INSERT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE,SELECT ANY SEQUENCE,
	CREATE ANY SEQUENCE,DROP ANY SEQUENCE,create trigger
TO PPP_TEST;

ALTER user PPP_TEST QUOTA UNLIMITED ON PPP_TEST;

 

 

远程访问

开放端口访问后, 本机可以连上远程的oracle 服务服务

 

发现报警目录非常大,占了快500G

/date/data/oracle/diag/rdbms/orcl/orcl/alert

 

ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 bytes is 100.00% used, and has 0 remaining bytes available.

解决办法 , 改大成8g

show parameter db_recovery_file_dest_size
alter system set db_recovery_file_dest_size=100g;



查询 archivelog  占用大小
select * from V$FLASH_RECOVERY_AREA_USAGE;


找到目录,删除大文件, 这个应该可以解决 

ORA-00257: archiver error. Connect internal only, until freed.
/date/data/oracle/fast_recovery_area/ORCL/archivelog 


在删除归档日志后，必须用RMAN维护控制文件，否则空间显示仍然不释放。
rman target sys/pass  没有验证成功,原因是无法安装
crosscheck archivelog all
delete expired archivelog all;
delete archivelog until time ‘sysdate-1’ ; 删除截止到前一天的所有archivelog



针对trace目录文件太多一直在增加的问题, 关闭trace

show parameter trace;
alter system set trace_enabled = false;

 

 

数据导出导入

 

查看逻辑目录

sqlplus / as sysdba
select * from dba_directories;

看到已经有共享目录 

OWNER                          DIRECTORY_NAME    DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            DUMP          /u02/oradata/backup

 

 

参考网上的5种导出方式,未验证

 

sqlplus / as sysdba
select * from dba_directories;

数据目录 /u02/oradata/backup

expdp \'/ as sysdba\' dumpfile=fulldata.dmp directory=DUMP full=y logfile=expfull20191203.log;


# 下面按用户导出
expdp ehl_analysis/ehl1234@orcl schemas=ehl_analysis dumpfile=ehl_analysis.dmp directory=DUMP logfile=ehl_analysis_20191204.log;


expdp EHL_PUBLIC/ehl1234@orcl schemas=EHL_PUBLIC dumpfile=EHL_PUBLIC.dmp directory=DUMP logfile=EHL_PUBLIC_20191204.log;



（2）用expdp导出dmp，有五种导出方式：

　　　　第一种：“full=y”，全量导出数据库；

expdp user/passwd@orcl dumpfile=expdp.dmp directory=data_dir full=y logfile=expdp.log;
　　　　第二种：schemas按用户导出；

expdp user/passwd@orcl schemas=user dumpfile=expdp.dmp directory=data_dir logfile=expdp.log;
　　　　第三种：按表空间导出；

expdp sys/passwd@orcl tablespace=tbs1,tbs2 dumpfile=expdp.dmp directory=data_dir logfile=expdp.log;
　　　　第四种：导出表；

expdp user/passwd@orcl tables=table1,table2 dumpfile=expdp.dmp directory=data_dir logfile=expdp.log;
　　　　第五种：按查询条件导；

expdp user/passwd@orcl tables=table1='where number=1234' dumpfile=expdp.dmp directory=data_dir logfile=expdp.log;

创建逻辑目录

oracle用户下

mkdir /data/oracle/dump

sqlplus / as sysdba

create directory dump as '/data/oracle/data';

select * from dba_directories;

select default_tablespace from dba_users;

导入数据

impdp sys/oracle@orcl directory=DATA_PUMP_DIR  dumpfile=expdat.dmp

impdp \'/ as sysdba\' directory=dump dumpfile=fulldata.dmp full=y logfile=import20191204.log

impdp \'/ as sysdba\' TRANSFORM=SEGMENT_ATTRIBUTES:N directory=dump dumpfile=fulldata.dmp full=y logfile=import20191204.log





select default_tablespace from dba_users;


create tablespace EHL_PUBLIC
logging
datafile  '/data/oracle/oradata/orcl11g/EHL_PUBLIC.dbf'
size 716M
autoextend on
next 307M maxsize 3276M
autoallocate             
extent management local 
segment space management auto; 


创建用户 EHL_PUBLIC 密码 ehl1234

create user EHL_PUBLIC identified by "ehl1234" default tablespace EHL_PUBLIC;


给用户授权

GRANT 
CREATE SESSION,CREATE ANY TABLE,CREATE ANY VIEW,CREATE ANY INDEX,CREATE ANY PROCEDURE,
	ALTER ANY TABLE,ALTER ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,DROP ANY INDEX,DROP ANY PROCEDURE,
	SELECT ANY TABLE,INSERT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE,SELECT ANY SEQUENCE,
	CREATE ANY SEQUENCE,DROP ANY SEQUENCE,create trigger
TO EHL_PUBLIC;

ALTER user EHL_PUBLIC QUOTA UNLIMITED ON EHL_PUBLIC;


grant read,write on directory dump to EHL_PUBLIC;

同名用户导入

impdp EHL_PUBLIC/ehl1234 TRANSFORM=SEGMENT_ATTRIBUTES:N schemas=EHL_PUBLIC directory=dump dumpfile=EHL_PUBLIC.dmp logfile=impdp2.log;




create tablespace EHL_PUBLIC
logging
datafile  '/data/oracle/oradata/orcl11g/EHL_PUBLIC.dbf'
size 716M
autoextend on
next 307M maxsize 3276M
autoallocate             
extent management local 
segment space management auto; 


创建用户 EHL_PUBLIC 密码 ehl1234

create user EHL_PUBLIC identified by "ehl1234" default tablespace EHL_PUBLIC;


给用户授权

GRANT 
CREATE SESSION,CREATE ANY TABLE,CREATE ANY VIEW,CREATE ANY INDEX,CREATE ANY PROCEDURE,
	ALTER ANY TABLE,ALTER ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,DROP ANY INDEX,DROP ANY PROCEDURE,
	SELECT ANY TABLE,INSERT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE,SELECT ANY SEQUENCE,
	CREATE ANY SEQUENCE,DROP ANY SEQUENCE,create trigger
TO EHL_PUBLIC;

ALTER user EHL_PUBLIC QUOTA UNLIMITED ON EHL_PUBLIC;


grant read,write on directory dump to EHL_PUBLIC;

同名用户导入

impdp EHL_PUBLIC/ehl1234 TRANSFORM=SEGMENT_ATTRIBUTES:N schemas=EHL_PUBLIC directory=dump dumpfile=EHL_PUBLIC.dmp logfile=impdp2.log;







==========================================================
create tablespace EHL_ANALYSIS_DEFAULT
logging
datafile  '/data/oracle/oradata/orcl11g/EHL_ANALYSIS_DEFAULT.dbf'
size 716M
autoextend on
next 307M maxsize 3276M
autoallocate             
extent management local 
segment space management auto; 


创建用户 EHL_ANALYSIS 密码 ehl1234

create user EHL_ANALYSIS identified by "ehl1234" default tablespace EHL_ANALYSIS_DEFAULT;


给用户授权

GRANT 
CREATE SESSION,CREATE ANY TABLE,CREATE ANY VIEW,CREATE ANY INDEX,CREATE ANY PROCEDURE,
	ALTER ANY TABLE,ALTER ANY PROCEDURE,DROP ANY TABLE,DROP ANY VIEW,DROP ANY INDEX,DROP ANY PROCEDURE,
	SELECT ANY TABLE,INSERT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE,SELECT ANY SEQUENCE,
	CREATE ANY SEQUENCE,DROP ANY SEQUENCE,create trigger
TO EHL_ANALYSIS;

ALTER user EHL_ANALYSIS QUOTA UNLIMITED ON EHL_ANALYSIS_DEFAULT;


grant read,write on directory dump to EHL_ANALYSIS;

同名用户导入

导入前确保字符集一样,参考下面的字符集错误解决办法

impdp EHL_ANALYSIS/ehl1234 TRANSFORM=SEGMENT_ATTRIBUTES:N schemas=EHL_ANALYSIS directory=dump dumpfile=ehl_analysis.dmp logfile=impdp3.log;

字符集不对导入导致部分数据没进去,清空表空间重新导入

drop tablespace EHL_ANALYSIS_DEFAULT including contents and datafiles cascade constraint;
然后再重建,参考上方的建命名空间的命令

重新导入
impdp EHL_ANALYSIS/ehl1234 TRANSFORM=SEGMENT_ATTRIBUTES:N schemas=EHL_ANALYSIS directory=dump dumpfile=ehl_analysis.dmp logfile=impdp3.log;


==================================================================











参考 
https://www.cnblogs.com/promise-x/p/7477360.html
记得设置权限
grant read,write on directory data_dir to user;


3）用impdp命令导入，对应五种方式：

　　　　第一种：“full=y”，全量导入数据库；

impdp user/passwd directory=data_dir dumpfile=expdp.dmp full=y;
　　　　第二种：同名用户导入，从用户A导入到用户A；

impdp A/passwd schemas=A directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;
　　　　第三种：①从A用户中把表table1和table2导入到B用户中；

impdp B/passwdtables=A.table1,A.table2 remap_schema=A:B directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;
　　　　　　　　②将表空间TBS01、TBS02、TBS03导入到表空间A_TBS，将用户B的数据导入到A，并生成新的oid防止冲突；

impdp A/passwd remap_tablespace=TBS01:A_TBS,TBS02:A_TBS,TBS03:A_TBS remap_schema=B:A FULL=Y transform=oid:n 
directory=data_dir dumpfile=expdp.dmp logfile=impdp.log
　　　　第四种：导入表空间；

impdp sys/passwd tablespaces=tbs1 directory=data_dir dumpfile=expdp.dmp logfile=impdp.log;
　　　　第五种：追加数据；

impdp sys/passwd directory=data_dir dumpfile=expdp.dmp schemas=system table_exists_action=replace logfile=impdp.log; 
--table_exists_action:导入对象已存在时执行的操作。有效关键字:SKIP,APPEND,REPLACE和TRUNCATE


导单张表

imp ehldata/ehl1234 file=/date/data/oracle/backup/6in1_db_bak/61.dmp tables=DRIVINGLICENSE ignore=y

oracle 彻底删除表
drop table DRIVINGLICENSE purge;
 

 

 

报错 : 

ORA-02374: conversion error loading table "EHL_ANALYSIS"."T_TIAP_ENTER_FIRST"

ORA-12899: value too large for column HPHM (actual: 11, maximum: 10)

ORA-02372: data for row: HPHM : 0X'B9F04B47353132B9D2'

 

 

解决办法:

sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET ZHS16GBK;

报错
ALTER DATABASE CHARACTER SET ZHS16GBK;
*ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
此时报错，不必慌张。提示新字符集必须是超集，此时INTRENAL_USE指令不对字符集超集进行检查。输入以下命令即可：

ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
SHUTDOWN IMMEDIATE;
STARTUP;
此时完成字符集转换。


服务器上是这个
AMERICAN_AMERICA.ZHS16GBK

测试服务器是这个
AMERICAN_AMERICA.AL32UTF8

 

报错

ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

 

暂未解决:

此时发现 /data/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora 的内容

ORCL11G =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl11g.us.oracle.com)
    )
  )
Service Name并不是orcl, 使用 orcl11g.us.oracle.com 这个当做serviceName即可,怎么修改它,尝试了如下命令不起作用

alter system set service_names='orcl';
exit
lsnrctl reload
lsnrctl status

后来重启也不行

尝试直接修改tnsnames.ora, 修改 orcl11g.us.oracle.com 重启后也不起作用