oracle12c

--连接数据库
sqlplus "/as sysdba"
conn sys/orcl as sysdba
--查看启动状态
select status from v$instance;


--新建pdb  
CREATE PLUGGABLE DATABASE localdomain ADMIN USER lhjctest IDENTIFIED BY lhjctest  
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)  
DEFAULT TABLESPACE lhjctest  
DATAFILE 'D:\app\orcl\oradata\lhjctest\lhcj\lhjc201.dbf' SIZE 25M AUTOEXTEND ON  
PATH_PREFIX = 'D:\app\orcl\oradata\lhjctest\lhcj\'  
FILE_NAME_CONVERT = ('D\app\orcl\oradata\lhjctest\pdbseed\',   
'D\app\orcl\oradata\lhjctest\lhcj\');  


--删除PDB  
DROP PLUGGABLE DATABASE pdborcl2 INCLUDING DATAFILES; 


--切换pdb
alter session set container=pdborcl;  
--切换cdb
alter session set container=CDB$ROOT;  
--开启pdb
alter pluggable database pdborcl open;  
--关闭pdb
alter pluggable database pdborcl close;  
--关闭cdb
SHUTDOWN IMMEDIATE
--开启cdb
startup; 


--创建临时空间
create temporary tablespace temp1 
tempfile 'D:\app\dataspace\temp1.dbf'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local;
--创建表空间
create tablespace lhjctest_data 
logging 
datafile 'D:\app\dataspace\lhjctest_data.dbf' 
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local;


--删除表空间


DROP TABLESPACE lhjctest_data INCLUDING CONTENTS AND DATAFILES;


--创建用户并指定表空间
create user hblhjc identified by hblhjc 
default tablespace lhjctest_data;


alter user hblhjc quota umlimited  on  lhjctest_data;
--删除用户
drop user lhjc cascade; 
--切换当前登录用户 
grant dba to sys;




--创建角色
    grant create session to hblhjc;--授予zhangsan用户创建session的权限，即登陆权限


　　grant unlimited tablespace to hblhjc;--授予zhangsan用户使用表空间的权限


　　grant create table to hblhjc;--授予创建表的权限


　　grant drop any table to hblhjc;--授予删除表的权限


　　grant insert any table to hblhjc;--插入表的权限


　　grant update any table to hblhjc;--修改表的权限


    grant create any procedure to hblhjc;
     
    grant execute any procedure to hblhjc;
    
    grant create job to hblhjc;
    
    grant manage scheduler to hblhjc;






--导入
imp lhjc/lhj@pdborcl file=D:\back.dmp fromuser=lhjctest touser lhjc 
ignore=y --跳过错误
statistics=none --不导入日志


--pdb跟随cdb启动
CREATE OR REPLACE TRIGGER open_pdbs  
AFTER STARTUP ON DATABASE   
BEGIN  
EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';   
END open_pdbs;  


--删除所有表视图索引的存储过程;    
create or replace procedure P_DROP_ALL_TABLE
as
  tableName varchar(1000);
  ref_t sys_refcursor;
  begin
    --打开游标;
    open ref_t for  select 'drop table ' || table_name ||''||chr(13)||chr(10) as tableName from user_tables
                    union all
                    --delete view
                    select 'drop view ' || view_name||''||chr(13)||chr(10) as tableName from user_views
                    union all
                    --delete seqs
                    select 'drop sequence ' || sequence_name||''||chr(13)||chr(10) as tableName from user_sequences
                    union all
                    --delete functions
                    select 'drop function ' || object_name||''||chr(13)||chr(10) as tableName from user_objects where object_type='FUNCTION'
                    union all
                    --delete package
                    select 'drop package ' || object_name||''||chr(13)||chr(10) as tableName from user_objects where object_type='PACKAGE';
         loop
             --从游标中获取一条记录,放入变量中;
             fetch ref_t into tableName;
             exit when ref_t%notfound;  --退出;
                   execute immediate tableName;
         end loop;
     close ref_t;
  end;


   
--local  导入
imp hblhjc/hblhj@pdborcl file=D:\back.dmp fromuser=lhjctest touser=hblhjc  ignore=y statistics=none 


--导出    
exp system/manager@TEST file=d:\daochu.dmp full=y

•