1 安装

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
docker volume create --driver local oracle-data

docker run -d --name oracle \
-p 1521:1521 \
-e ORACLE_PWD=password \
-e ORACLE_CHARACTERSET=AL32UTF8 \
-v oracle-data:/opt/oracle/oradata \
container-registry.oracle.com/database/free:23.3.0.0


docker exec -it oracle sqlplus / as sysdba
docker exec -it oracle sqlplus sys/password@FREE as sysdba
docker exec -it oracle sqlplus system/password@FREE
docker exec -it oracle sqlplus pdbadmin/password@FREEPDB1

# 创建测试用户
docker exec -it oracle sqlplus sys/password@FREE as sysdba
show pdbs;
alter session set container=FREEPDB1;
grant dba to pdbadmin;
exit;
docker exec -it oracle sqlplus pdbadmin/password@FREEPDB1
create tablespace test datafile '/opt/oracle/oradata/FREE/FREEPDB1/test.dbf' size 1G AUTOEXTEND ON NEXT 100M;
create user test identified by password default tablespace test;
grant 
  connect,
  unlimited tablespace,
  create table,
  create view,
  create sequence,
  create procedure to test;

exit;

docker exec -it oracle sqlplus test/password@FREEPDB1

2 查看告警日志

1
select value from v$diag_info where name='Diag Alert';
1
2
cd /u01/app/oracle/diag/rdbms/XE/XEPDB1/alert
tail -f log.xml

3 查看表空间大小

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
--查看所有表空间大小
select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"  
from  
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,  
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b  
where   a.tablespace_name=b.tablespace_name  
order   by   ((a.bytes-b.bytes)/a.bytes)   desc;

select file_id,file_name from dba_data_files where tablespace_name='SYSAUX';

SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_space_mb, SUM(maxbytes)/1024/1024 AS max_space_mb FROM dba_data_files GROUP BY tablespace_name;

SELECT file_id, file_name, bytes/1024/1024 AS size_mb FROM dba_data_files WHERE tablespace_name = 'SYSAUX';
-- ALTER DATABASE DATAFILE '/opt/oracle/oradata/XE/XEPDB1/sysaux01.dbf' RESIZE 5000M;
ALTER TABLESPACE SYSAUX ADD DATAFILE '/u01/app/oracle/oradata/XE/XEPDB1/sysaux02.dbf' SIZE 32000M AUTOEXTEND ON NEXT 300M;

4 数据导入导出

1
2
3
4
5
6
impdp test/password@FREEPDB1 \
    directory=expdp_dir \
    dumpfile=expdp_20240827.dmp \
    logfile=impdp_20240827.log \
    tables=test.test_table \
    remap_table=test.test_table:test_table_0827