一、创建用户和表空间

—创建表空间
CREATE TABLESPACE MAXDATA DATAFILE ‘C:\app\oradata\ORCL\orclpdb\MAXDATA.DBF’ SIZE 4096M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
CREATE TABLESPACE MAXINDEX DATAFILE ‘C:\app\oradata\ORCL\orclpdb\MAXINDEX.DBF’ SIZE 2096M AUTOEXTEND ON NEXT 200M;
CREATE TEMPORARY TABLESPACE MAXTEMP TEMPFILE ‘C:\app\oradata\ORCL\orclpdb\MAXTEMP.DBF’ SIZE 2096M AUTOEXTEND ON MAXSIZE unlimited;

—给表空间添加数据文件
ALTER TABLESPACE MAXDATA ADD DATAFILE ‘D:\app\Administrator\oradata\orcl\MAXDATA1.DBF’ SIZE 30G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
ALTER TABLESPACE MAXDATA ADD DATAFILE ‘D:\app\Administrator\oradata\orcl\MAXDATA2.DBF’ SIZE 30G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;

--2、查看表空间物理文件的名称及大小
select * from dba_data_files;
1.查询不包含临时表空间的使用率
SELECT Upper(F.TABLESPACE_NAME) "TablespaceName",
D.TOT_GROOTTE_MB "Total(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "Used(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "Used%",
F.TOTAL_BYTES "Free(M)",
F.MAX_BYTES "max_block(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1
2.查看临时表空间使用率
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

—删除表空间
drop tablespace ORCLMAXDATA including contents and datafiles cascade constraint;
drop tablespace ORCLMAXINDEX including contents and datafiles cascade constraint;
drop tablespace MAXTEMP including contents and datafiles cascade constraint;

CREATE TABLESPACE MAXDATA DATAFILE ‘/u01/app/oracle/oradata/orcl/MAXDATA.DBF’ SIZE 4096M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
CREATE TABLESPACE MAXINDEX DATAFILE ‘/u01/app/oracle/oradata/orcl/MAXINDEX.DBF’ SIZE 4096M AUTOEXTEND ON NEXT 200M;
CREATE TEMPORARY TABLESPACE MAXTEMP TEMPFILE ‘/u01/app/oracle/oradata/orcl/MAXTEMP.DBF’ SIZE 4096M AUTOEXTEND ON MAXSIZE unlimited;

—集群下创建表空间
CREATE TABLESPACE MAXDATA DATAFILE ‘+DATA/maximo/datafile/MAXDATA.DBF’ SIZE 4096M AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;
CREATE TABLESPACE MAXINDEX DATAFILE ‘+DATA/maximo/datafile/MAXINDEX.DBF’ SIZE 4096M AUTOEXTEND ON NEXT 200M;
CREATE TEMPORARY TABLESPACE MAXTEMP TEMPFILE ‘+DATA/maximo/datafile/MAXTEMP.DBF’ SIZE 4096M AUTOEXTEND ON MAXSIZE unlimited;

—创建用户并授权
create user maximo identified by maximo;
alter user maximo default tablespace MAXDATA quota unlimited on MAXDATA;
alter user maximo quota unlimited on MAXINDEX;
alter user maximo temporary tablespace MAXTEMP;
grant create job to maximo;
grant create trigger to maximo;
grant create session to maximo;
grant create sequence to maximo;
grant create synonym to maximo;
grant create table to maximo;
grant create view to maximo;
grant create procedure to maximo;
grant alter session to maximo;
grant execute on ctxsys.ctx_ddl to maximo;
grant dba to maximo;
GRANT create session to maximo with ADMIN OPTION;
GRANT alter user to maximo;
GRANT create job to maximo;
GRANT OEM_MONITOR to maximo;
GRANT EXP_FULL_DATABASE TO maximo;
GRANT IMP_FULL_DATABASE TO maximo;

—创建完maximo用户后再登录maximo用户，然后下面的进行脚本初始化：

call ctx_ddl.create_preference('default_lexer','basic_lexer');
call ctx_ddl.create_preference('english_lexer','basic_lexer');
call ctx_ddl.create_preference('chinese_lexer','chinese_lexer');
call ctx_ddl.create_preference('japanese_lexer','japanese_lexer');
call ctx_ddl.create_preference('korean_lexer','korean_morph_lexer');
call ctx_ddl.create_preference('german_lexer','basic_lexer');
call ctx_ddl.create_preference('dutch_lexer','basic_lexer');
call ctx_ddl.create_preference('swedish_lexer','basic_lexer');
call ctx_ddl.create_preference('french_lexer','basic_lexer');
call ctx_ddl.create_preference('italian_lexer','basic_lexer');
call ctx_ddl.create_preference('spanish_lexer','basic_lexer');
call ctx_ddl.create_preference('portu_lexer','basic_lexer');
call ctx_ddl.create_preference('global_lexer', 'multi_lexer');
call ctx_ddl.add_sub_lexer('global_lexer','default','default_lexer');
call ctx_ddl.add_sub_lexer('global_lexer','english','english_lexer','en');
call ctx_ddl.add_sub_lexer('global_lexer','simplified chinese','chinese_lexer','zh');
call ctx_ddl.add_sub_lexer('global_lexer','japanese','japanese_lexer',null);
call ctx_ddl.add_sub_lexer('global_lexer','korean','korean_lexer',null);
call ctx_ddl.add_sub_lexer('global_lexer','german','german_lexer','de');
call ctx_ddl.add_sub_lexer('global_lexer','dutch','dutch_lexer',null);
call ctx_ddl.add_sub_lexer('global_lexer','swedish','swedish_lexer','sv');
call ctx_ddl.add_sub_lexer('global_lexer','french','french_lexer','fr');
call ctx_ddl.add_sub_lexer('global_lexer','italian','italian_lexer','it');
call ctx_ddl.add_sub_lexer('global_lexer','spanish','spanish_lexer','es');
call ctx_ddl.add_sub_lexer('global_lexer','portuguese','portu_lexer',null);
call ctx_ddl.drop_preference('global_lexer');
call ctx_ddl.drop_preference('default_lexer');
call ctx_ddl.drop_preference('english_lexer');
call ctx_ddl.drop_preference('chinese_lexer');
call ctx_ddl.drop_preference('japanese_lexer');
call ctx_ddl.drop_preference('korean_lexer');
call ctx_ddl.drop_preference('german_lexer');
call ctx_ddl.drop_preference('dutch_lexer');
call ctx_ddl.drop_preference('swedish_lexer');
call ctx_ddl.drop_preference('french_lexer');
call ctx_ddl.drop_preference('italian_lexer');
call ctx_ddl.drop_preference('spanish_lexer');
call ctx_ddl.drop_preference('portu_lexer');
commit;

多个实例下链接指定实例

例如链接系统管理员(这里是以oracle19为背景)：
sqlplus /@localhost:1522/maximo as sysdba;

二、导出/导入操作

先将DMP文件放入数据库安装目录的oradata文件下MAXIMO实例文件夹下：例如：/db/app/oracle/oradata/MAXIMO
—然后执行导出操作：例如下面的执行语句
expdp maximo/maximo@maximo dumpfile=JA210123.DMP schemas=maximo
导入
impdp maximo/maximo@orcl dumpfile=eamppddb210716.dmp schemas=maximo logfile=impdp.log

导入时排除指定的表
[oracle@d8292ce03ead ~]$ impdp remap_schema=maximo:jsmaximo EXCLUDE=TABLE:\"IN \'ST_PI_TASK_ITEM\'\'INTERFACELOG\'\" 
Import: Release 11.2.0.1.0 - Production on Tue May 11 10:53:06 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: jsmaximo
--------------------------------------------------
导入多个dmp文件
[oracle@d8292ce03ead ~]$ impdp directory=DATA_PUMP_DIR  dumpfile=expdat%U.dmp remap_schema=maximo:jsmaximo EXCLUDE=TABLE:\"IN \'ST_PI_TASK_ITEM\'\'INTERFACELOG\'\" 
----------------------
分段导出
expdp maximo/maximo@eamppddb directory=DATA_PUMP_DIR  dumpfile=expdat%U.dmp remap_schema=maximo:jsmaximo filesize=5000m

数据泵impdp参数：
version：指定导出版本
例如：expdp system/oracle@ip:1521/dbname directory=dmp11g logfile=xxx.log dumpfile=xxx_%U.dmp parallel=8 schemas=user1,user2 compression=all version=11.2.0.0.0 cluster=n

? ? 1.REMAP_DATAFILE
? ? ? ? 该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬移表空间时需要该选项.
? ? ? ? REMAP_DATAFILE=source_datafie:target_datafile?

? ??2.REMAP_SCHEMA
? ? ? ? 该选项用于将源方案的所有对象装载到目标方案中.
? ? ? ? REMAP_SCHEMA=source_schema:target_schema

? ??3.REMAP_TABLESPACE
? ? ? ? 将源表空间的所有对象导入到目标表空间中
? ? ? ? REMAP_TABLESPACE=source_tablespace:target:tablespace?

? ? 4.REUSE_DATAFILES
? ? ? ? 该选项指定建立表空间时是否覆盖已存在的数据文件.默认为N
? ? ? ? REUSE_DATAFIELS={Y | N}?

? ? 5.SKIP_UNUSABLE_INDEXES
? ? ? ? 指定导入是是否跳过不可使用的索引,默认为N
?
? ? 6,sqlfile ?参数允许创建DDL 脚本文件
? ? ? ? impdp scott/tiger directory=dump_scott dumpfile=a1.dmp sqlfile=c.sql
? ? ? ? 默认放在directory下，因此不要指定绝对路径
?
? ? 7.STREAMS_CONFIGURATION
? ? ? ? 指定是否导入流元数据(Stream Matadata),默认值为Y.?

??? 8.TABLE_EXISTS_ACTION
? ? ? ? 该选项用于指定当表已经存在时导入作业要执行的操作,默认为SKIP
? ? ? ? TABBLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE |?REPLACE?}?
? ? ? ? 当设置该选项为SKIP时,导入作业会跳过已存在表处理下一个对象;
? ? ? ? 当设置为APPEND时,会追加数据;
? ? ? ? 当设置为TRUNCATE时,导入作业会截断表,然后为其追加新数据;
? ? ? ??当设置为REPLACE时,导入作业会删除已存在表,重建表并追加数据;

三、连接容器/切换容器

oracle从12开始就有可插拔数据库，一个数据库实例可以有多个容器，每个容器数据都是隔离的
显示当前容器名
show con_name;
CDB$ROOT是顶层容器

显示所有容器
select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
PDB$SEED

切换容器，PDB01是数据库容器名
ALTER SESSION SET CONTAINER = PDB01;

开启容器
alter pluggable database ORCLPDB open;
切换到指定容器
alter session set container=ORCLPDB;

create user maximo identified by maximo;
#授权资源、连接权限到pdbadmin
grant connect,resource to maximo;
#添加创建资源权限到pdbadmin
grant create view to maximo;
#修改pdbamdin账号表空间
alter user maximo default tablespace MAXDATA;
#添加pdbadmin账号的系统权限
grant unlimited tablespace to maximo;

从容器中导出dmp文件

先用管理员方式进入容器

C:\app\>sqlplus /nolog
SQL> conn /as sysdba;
SQL> show pdbs;
--切换到指定容器
SQL> alter session set container = pdborcl;
--将dba权限的数据泵给maximo用户
SQL> grant dba to maximo;
--创建一个数据泵目录dp_dir，
SQL> create or replace directory dp_dir as  'C:\data\oracle';
SQL> exit

修改tnsnames.ora文件，添加如下内容

PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

oracle12c（容器数据库）的完整配置tnsnames.ora文件

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\12.1.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

导出测试
expdp maximo/maximo@pdborcl directory=dp_dir dumpfile=ads.dmp schemas=maximo logfile=export.log;

导入语句：
impdp maximo/maximo@pdborcl directory=dp_dir dumpfile=ads.dmp schemas=maximo logfile=ads.log;

四、取消密码过期安全策略

运行SQLPlus命令行工具, 输入:
connect as sysdba;
输入dba的用户名和密码后进行.

SELECT * FROM dba_profiles WHERE profile=’DEFAULT’ AND resource_name=’PASSWORD_LIFE_TIME’
查询密码的有效期设置，LIMIT字段是密码有效天数。
在密码将要过期或已经过期时可通过如下语句进行修改密码，密码修改后该用户可正常连接数据库。
ALTER USER 用户名 IDENTIFIED BY 密码 ;

如果想去除180天的密码生存周期的限制可通过如下SQL语句将其关闭
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
如上SQL语句将口令有效期默认值180天修改成了“无限制”。但是出于数据库安全性考虑，不建议将PASSWORD_LIFE_TIME值设置成UNLIMITED，建议大家定期修改数据库用户口令。

五、创建索引

Oracle错误ORA-29861，是索引问题

查看哪些索引出现了这种问题：

select owner,index_name,table_name from all_indexes where domidx_status!='VALID' or domidx_opstatus!='VALID'

将出现这种问题的索引通过以下方式进行重建：

alter index  索引名  rebuild online parameters('sync')

重建时会出现以下错误
ORA-29874：执行ODCIINDEXALTER例行程序中警告

ORA-29960：行1，Check Oracle Error <13201>

1、创建索引
create index 索引名 on 表名(列名);
2、删除索引
2.1 先设置索引不可见
alter index jingyu.IDX_T_01 invisible;
drop index 索引名;
在索引名上加双引号: drop index “索引名”;
删除索引后对重新创建的索引设置可见
alter index jingyu.IDX_T_01 visible;
3、创建组合索引
create index 索引名 on 表名(列名1,列名2);
4、查询索引
—根据索引名，查询表索引字段
select from user_ind_columns where index_name=’索引名’;
—根据表名，查询一张表的索引
select from user_indexes where table_name=’表名’;
查询表的索引包含的字段

select a.uniqueness 索引类型,b.index_name 索引名称,b.column_name 字段 from user_indexes a ,user_ind_columns b
where a.table_name=b.table_name and a.index_name = b.index_name 
and a.table_owner=upper('MAXIMO') and  a.table_name='HIS_BANKMENTRUST' order by a.uniqueness desc;

[

](https://blog.csdn.net/sinat_28633913/article/details/75246407)

Java学习笔记

oracle数据库操作