ORACE APEX 18.2 之二(创建MPAPEX实例)

ORACE APEX 18.2 之二(创建MPAPEX实例)

  1. dbca命令创建database
    [oracle@xag182 ~]$ dbca

  2. 查看表空间

[oracle@xag182 ~]$ sql / as sysdba

SQL> set sqlformat ansiconsole

SQL> 
select tablespace_name,file_name,bytes/1024/1024 mb from dba_temp_files 
union all 
select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files;

TABLESPACE_NAME  FILE_NAME                          MB   
TEMP             /u02/oradata/MPAPEX/temp01.dbf     131  
USERS            /u02/oradata/MPAPEX/users01.dbf    5    
UNDOTBS1         /u02/oradata/MPAPEX/undotbs01.dbf  70   
SYSTEM           /u02/oradata/MPAPEX/system01.dbf   820  
SYSAUX           /u02/oradata/MPAPEX/sysaux01.dbf   520  

  1. UNDO梳理
#此处为测试环境故设置100M,生产环境要放大10G以上
SQL> ALTER DATABASE DATAFILE '/u02/oradata/MPAPEX/undotbs01.dbf' RESIZE 100M;
SQL> ALTER DATABASE DATAFILE '/u02/oradata/MPAPEX/undotbs01.dbf' AUTOEXTEND ON NEXT 50M;
  1. TEMP 梳理
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE
'/u02/oradata/MPAPEX/TEMP1_1.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M  MAXSIZE 1G,
'/u02/oradata/MPAPEX/TEMP1_2.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M  MAXSIZE 1G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER TABLESPACE TEMP1 TABLESPACE GROUP TEMP_GP;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GP;
#如无法删除则需重启db
drop tablespace TEMP including contents and datafiles;

select * from dba_tablespace_groups
  1. 整理用户表空间
create tablespace XAG_UD datafile 
'/u02/oradata/MPAPEX/XAG_UD1.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,                  
'/u02/oradata/MPAPEX/XAG_UD2.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED 
default storage (initial 128K next 2M pctincrease 0);

Create tablespace I_XAG_UD datafile 
'/u02/oradata/MPAPEX/I_XAG_UD1.dbf' size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED,            
'/u02/oradata/MPAPEX/I_XAG_UD2.dbf' size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED  
default storage (initial 128K next 2M pctincrease 0);

ALTER DATABASE DEFAULT TABLESPACE XAG_UD;

drop tablespace USERS including contents and datafiles;
  1. 创建用户(建議不要創建)
CREATE USER C##XAG IDENTIFIED BY "123456" DEFAULT TABLESPACE XAG_UD TEMPORARY TABLESPACE TEMP_GP;

GRANT DBA to C##XAG;
grant connect,resource,unlimited tablespace to C##XAG;
grant create any directory to C##XAG;
grant drop any directory to C##XAG;

#修改密码(可选)
alter  user  C##XAG identified  by "xag123";
#账户锁定后解锁命令(可选) sys or system
alter  user C##XAG  account unlock;
#设置用户密码无限次尝试登录
alter profile default limit failed_login_attempts unlimited;
#设置用户密码不过期:
alter profile default limit password_life_time unlimited;
#查看配置的参数
select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where 
RESOURCE_NAME in('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME') and profile='DEFAULT';
  1. 創建測試自動插入job(system用户下)
begin
  dbms_network_acl_admin.create_acl
  (     
    acl        => 'utl_http.xml',          -- 文件名称
    description => 'HTTP Access',          -- 描述
    principal  => 'SYSTEM',                -- 授权或者取消授权账号,大小写敏感
    is_grant    => TRUE,                   -- 授权还是取消授权
    privilege  => 'connect',               -- 授权或者取消授权的权限列表
    start_date  => null,                   -- 起始日期
    end_date    => null                    -- 结束日期
  );
end;

begin
  dbms_network_acl_admin.add_privilege (    -- 添加访问权限列表项
    acl        => 'utl_http.xml',          -- 刚才创建的acl名称
    principal  => 'SYSTEM',                -- 授权或取消授权用户
    is_grant  => TRUE,                    -- 与上同
    privilege  => 'resolve',                -- 权限列表
    start_date => null,                   
    end_date  => null
  );
end;

begin
  dbms_network_acl_admin.assign_acl(acl => 'utl_http.xml', host =>'*');
end;

create table my_healt_check
as
select utl_inaddr.get_host_address as host_address
,utl_inaddr.get_host_name as host_name
,sys_context('USERENV','CON_NAME') as db_name
,sysdate as create_time from dual;

create or replace procedure pr_my_healt_check_insert as
begin
  delete from my_healt_check a where a.create_time<sysdate-1/24;
  insert into my_healt_check(host_address,host_name,db_name,create_time)
  select utl_inaddr.get_host_address,utl_inaddr.get_host_name,sys_context('USERENV','CON_NAME'),sysdate from dual;
  commit;
end;

Declare job1 Number;
Begin
  dbms_job.submit(job1,'pr_my_healt_check_insert;',Sysdate,'sysdate + (1/(24*60))');
  Commit;
End;

CREATE OR REPLACE VIEW V_XAG_JOB1 AS
SELECT job,to_char(LAST_DATE,'yyyy-mm-dd hh24:mi:ss') as last_date
,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as next_date,to_char(total_time,9999999999) as total_time
,to_char(case when failures is null then 0 else failures end,9999999999)  as failures
,broken,what,interval FROM user_jobs order by job;

CREATE OR REPLACE VIEW V_XAG_JOB2 AS
SELECT JOB_NAME,JOB_ACTION job,to_char(START_DATE,'yyyy-mm-dd hh24:mi:ss') as START_DATE
,REPEAT_INTERVAL,ENABLED,to_char(LAST_START_DATE,'yyyy-mm-dd hh24:mi:ss') as LAST_START_DATE
,to_char(NEXT_RUN_DATE,'yyyy-mm-dd hh24:mi:ss') as NEXT_RUN_DATE
,COMMENTS
FROM user_scheduler_jobs order by JOB_NAME;

select * from V_XAG_JOB1

select * from V_XAG_JOB2

select * from my_healt_check order by create_time desc
  1. DB启动时 open all pdb
--conn / as sysdba
CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
  EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;

9.创建pdb1

[oracle@xag182 ~]$ sql sys/123456@192.168.40.182:1521/MPAPEX as sysdba

SQL> help set SQLFORMAT
SET SQLFORMAT
  SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}   

SQL> SET SQLFORMAT ansiconsole
SQL> 
SQL> SELECT name,DECODE(cdb,'YES', 'Multitenant Option enabled','Regular 12c Database:') as "Multitenant Option",open_mode,con_id FROM v$database;
NAME    Multitenant Option          OPEN_MODE   CON_ID  
MPAPEX  Multitenant Option enabled  READ WRITE  0 

SQL> select con_id,name,open_mode from v$pdbs;
CON_ID  NAME      OPEN_MODE   
2       PDB$SEED  READ ONLY   
  1. 基于PDB$SEED演示创建PDB
SQL> alter system set db_create_file_dest='/u02/oradata/';

SQL> create pluggable database pdb1 admin user admin identified by 123456 roles=(connect) file_name_convert=('/u02/oradata/MPAPEX/pdbseed','/u02/oradata/MPAPEX/pdb1');
  1. 查看创建后的状态,刚刚创建的pdb status列为NEW
SQL> select pdb_id,pdb_name,status,creation_time from cdb_pdbs;
PDB_ID  PDB_NAME  STATUS  CREATION_TIME  
3       PDB1      NEW     02-NOV-18      
2       PDB$SEED  NORMAL  31-OCT-18 
#如下查询,当前的数据库处于mount状态
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID  DBID        NAME      OPEN_MODE  
2       988450796   PDB$SEED  READ ONLY  
3       2853971070  PDB1    MOUNTED  
  1. 将pdb数据库切换到open状态
SQL> alter pluggable database PDB1 open;
or
SQL>alter session set container=PDB1 ;
SQL> startup;

SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID  DBID        NAME      OPEN_MODE   
2       988450796   PDB$SEED  READ ONLY   
3       2853971070  PDB1      READ WRITE 

  1. 使用公共用户sys连接到刚刚创建的pdb数据库
SQL> conn sys/123456@192.168.40.182:1521/pdb1 as sysdba;

SQL> show user;
USER is "SYS"

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3   PDB1                         READ WRITE    NO
  1. UNDO梳理
SQL> select tablespace_name,file_name,bytes/1024/1024 mb from dba_temp_files union all select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files;

TABLESPACE_NAME  FILE_NAME                               MB   
TEMP             /u02/oradata/MPAPEX/pdb1/temp01.dbf     64   
SYSTEM           /u02/oradata/MPAPEX/pdb1/system01.dbf   250  
SYSAUX           /u02/oradata/MPAPEX/pdb1/sysaux01.dbf   350  
UNDOTBS1         /u02/oradata/MPAPEX/pdb1/undotbs01.dbf  100  
USERS            /u02/oradata/MPAPEX/pdb1/users01.dbf    5

#此处为测试环境故设置100M,生产环境要放大10G以上
SQL> ALTER DATABASE DATAFILE '/u02/oradata/MPAPEX/pdb1/undotbs01.dbf' RESIZE 100M;
SQL> ALTER DATABASE DATAFILE '/u02/oradata/MPAPEX/pdb1/undotbs01.dbf' AUTOEXTEND ON NEXT 50M;
  1. TEMP 梳理
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE
'/u02/oradata/MPAPEX/pdb1/TEMP1_1.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M  MAXSIZE 1G,
'/u02/oradata/MPAPEX/pdb1/TEMP1_2.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M  MAXSIZE 1G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER TABLESPACE TEMP1 TABLESPACE GROUP TEMP_GP;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GP;
#如无法删除则需重启db
drop tablespace TEMP including contents and datafiles;
select * from dba_tablespace_groups;
  1. 整理用户表空间
create tablespace PDB1_UD datafile 
'/u02/oradata/MPAPEX/pdb1/PDB1_UD1.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,                  
'/u02/oradata/MPAPEX/pdb1/PDB1_UD2.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED 
default storage (initial 128K next 2M pctincrease 0);

Create tablespace I_PDB1_UD datafile 
'/u02/oradata/MPAPEX/pdb1/I_PDB1_UD1.dbf' size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED,            
'/u02/oradata/MPAPEX/pdb1/I_PDB1_UD2.dbf' size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED  
default storage (initial 128K next 2M pctincrease 0);

ALTER DATABASE DEFAULT TABLESPACE PDB1_UD;

drop tablespace USERS including contents and datafiles;
  1. 对用户授权
GRANT DBA to ADMIN;
grant connect,resource,unlimited tablespace to ADMIN;
grant create any directory to ADMIN;
grant drop any directory to ADMIN;

#修改密码(可选)
alter  user  ADMIN identified  by "xag123";
#账户锁定后解锁命令(可选) sys or system
alter  user ADMIN  account unlock;
#设置用户密码无限次尝试登录
alter profile default limit failed_login_attempts unlimited;
#设置用户密码不过期:
alter profile default limit password_life_time unlimited;
#查看配置的参数
select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where 
RESOURCE_NAME in('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME') and profile='DEFAULT';
  1. 使用pdb本地管理员账户连接到pdb数据库
SQL> conn admin/xag123@192.168.40.182:1521/pdb1
Connected.
SQL> show user;
USER is "ADMIN"
SQL> show pdbs;
CON_ID  CON_NAME  OPEN MODE   RESTRICTED  
3       MYPDB1    READ WRITE  YES         
#查看当前用户的角色
SQL> select * from user_role_privs;
USERNAME  GRANTED_ROLE  ADMIN_OPTION  DELEGATE_OPTION  DEFAULT_ROLE  OS_GRANTED  COMMON  INHERITED  
ADMIN     CONNECT       NO            NO               YES           NO          NO      NO         
ADMIN     DBA           NO            NO               YES           NO          NO      NO         
ADMIN     PDB_DBA       YES           NO               YES           NO          NO      NO         
ADMIN     RESOURCE      NO            NO               YES           NO          NO      NO         
#查看当前用户的权限
SQL> select * from session_privs;
#查看当前的默认表空间
SQL> SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME ='DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE  
PDB1_UD 
  1. 创建新用户
[oracle@XAG143 ~]$ sql admin/xag123@192.168.40.182:1521/pdb1
SQL> CREATE USER XAG IDENTIFIED BY "123456" DEFAULT TABLESPACE PDB1_UD TEMPORARY TABLESPACE TEMP_GP;

SQL> GRANT DBA to XAG;

grant connect,resource,unlimited tablespace to XAG;
grant create any directory to XAG;
grant drop any directory to XAG;

#修改密码(可选)
alter  user  XAG identified  by "123456";
#账户锁定后解锁命令(可选) sys or system
alter  user XAG account unlock;
#设置用户密码无限次尝试登录
alter profile default limit failed_login_attempts unlimited;
#设置用户密码不过期:
alter profile default limit password_life_time unlimited;
#查看配置的参数
select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where 
RESOURCE_NAME in('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME') and profile='DEFAULT';

SQL> conn XAG/123456@192.168.40.182:1521/pdb1
  1. CDB下查询默认表空间
[oracle@XAG143 ~]$ sql sys/123456@192.168.40.182:1521/MPAPEX as sysdba

SQL> SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME ='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE                                                                                                                                                                                                                   
-----------------------------------
XAG_UD 
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 212,222评论 6 493
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,455评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,720评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,568评论 1 284
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,696评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,879评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,028评论 3 409
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,773评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,220评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,550评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,697评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,360评论 4 332
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,002评论 3 315
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,782评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,010评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,433评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,587评论 2 350

推荐阅读更多精彩内容