oracle用户表空间的创建

1、删除用户及其所有表：

drop user MMSP_CMS cascade;

drop user MMSP_BMS cascade;

drop user mmsp_aaa cascade;

drop user mmsp_portal cascade;

drop user mmsp_iscg cascade;

drop user mmsp_mc cascade;

drop user mmsp_cache cascade;

 

drop tablespace MMSP_CMS including contents and datafiles;

drop tablespace MMSP_BMS including contents and datafiles;

drop tablespace MMSP_AAA including contents and datafiles;

drop tablespace MMSP_PORTAL including contents and datafiles;

drop tablespace MMSP_ISCG including contents and datafiles;

drop tablespace MMSP_MC including contents and datafiles;

drop tablespace MMSP_CACHE including contents and datafiles;

drop TEMPORARY tablespace TEMP_MMSP including contents and datafiles;

 

2、创建表空间

create tablespace MMSP_CMS  logging datafile '/oracle/MMSP_CMS.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_BMS  logging datafile '/oracle/MMSP_BMS.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_AAA  logging datafile '/oracle/MMSP_AAA.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_PORTAL  logging datafile '/oracle/MMSP_PORTAL.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_ISCG  logging datafile '/oracle/MMSP_ISCG.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_MC  logging datafile '/oracle/MMSP_MC.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create tablespace MMSP_CACHE  logging datafile '/oracle/MMSP_CACHE.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

create TEMPORARY tablespace TEMP_MMSP  logging datafile '/oracle/TEMP_MMSP.dbf' size 512m autoextend on next 50m maxsize 20480m extent management local;

 

CREATE TEMPORARY TABLESPACE TEMP_MMSP TEMPFILE '/oracle/TEMP_MMSP.dbf' SIZE 200m AUTOEXTEND ON NEXT 32m MAXSIZE 2048m EXTENT MANAGEMENT LOCAL;

 

3、创建用户

create user mmsp_cms identified by "111111" default tablespace MMSP_CMS temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_cms;

grant unlimited tablespace to mmsp_cms;

 

create user mmsp_bms identified by "111111" default tablespace MMSP_BMS temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_bms;

grant unlimited tablespace to mmsp_bms;

 

create user mmsp_aaa identified by "111111" default tablespace MMSP_AAA temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_aaa;

grant unlimited tablespace to mmsp_aaa;

 

create user mmsp_portal identified by "111111" default tablespace MMSP_PORTAL temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_portal;

grant unlimited tablespace to mmsp_portal;

 

create user mmsp_iscg identified by "111111" default tablespace MMSP_ISCG temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_iscg;

grant unlimited tablespace to mmsp_iscg;

 

create user mmsp_mc identified by "111111" default tablespace MMSP_MC temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_mc;

grant unlimited tablespace to mmsp_mc;

 

create user mmsp_cache identified by "111111" default tablespace MMSP_CACHE temporary tablespace TEMP_MMSP profile DEFAULT;

grant dba to mmsp_cache;

grant unlimited tablespace to mmsp_cache;

 

4、导入dmp文件

imp mmsp_cms/111111 file=mmsp_cms.dmp ignore=y full=y

imp mmsp_bms/111111 file=mmsp_bms.dmp ignore=y full=y

imp mmsp_aaa/111111 file=mmsp_aaa.dmp ignore=y full=y

imp mmsp_portal/111111 file=mmsp_portal.dmp ignore=y full=y

imp mmsp_iscg/111111 file=mmsp_iscg.dmp ignore=y full=y

imp mmsp_mc/111111 file=mmsp_mc.dmp ignore=y full=y

imp mmsp_cache/111111 file=mmsp_cache.dmp ignore=y full=y

 

 

5、清除表垃圾数据

--数据库：CMS

truncate table CMS_ASSET_LOCATION;

truncate table CMS_CAPTURE_TASK;

truncate table CMS_CATEGORY;

truncate table CMS_CATEGORY_CONTENT_REF;

truncate table CMS_CHANNEL_NODE;

truncate table CMS_CHANNEL_SERVICE_MAPPING;

truncate table CMS_CONP_CONTENT_RELA;

truncate table CMS_CONTENT;

truncate table CMS_CONTENT_INJECT_RESPONSE;

truncate table CMS_CONTENT_SPECIFICATION;

truncate table CMS_DELETE_BOCONTENT;

truncate table CMS_INTERFACE_LOG;

truncate table CMS_MOVIE_FILE;

truncate table CMS_MOVIE_LOCATION;

truncate table CMS_MOVIE_PACKAGE;

truncate table CMS_MOVIE_POSTER;

truncate table CMS_MOVIE_TITLE;

truncate table CMS_OPERATE_LOG;

truncate table CMS_ORG;

truncate table CMS_OTT_CAPTURE_LOG;

truncate table CMS_RTM_ASSET_LOCATION;

truncate table CMS_RTM_CAPTURE_ASSET;

truncate table CMS_RTM_CHANNEL;

truncate table CMS_RTM_CHANNEL_OTT;

truncate table CMS_RTM_CHANNEL_OTT_RATE;

truncate table CMS_RTM_PROGRAM;

update cms_seq_table t set t.seq_value = 1;

truncate table CMS_SIGN;

truncate table CMS_STORAGE_NODE;

truncate table CMS_TASK;

truncate table CMS_TEMP_BGPROCQUEUE;

truncate table CMS_TEMP_REQUEST;

truncate table CMS_TEMP_TRANSFER_CONTENT;

truncate table CMS_TMP;

truncate table cms_upload_log;

truncate table CMS_VODCONTENT_TO_OTT_TASK;

 

--数据库：ISCG

truncate table ISCG_ASSET_LOCATION;

truncate table ISCG_OPERATE_LOG;

truncate table ISCG_SEND_ERROR_QUEUE;

truncate table ISCG_STORAGE;

truncate table USG_CHANNEL;

truncate table USG_CHANNEL_OTT;

truncate table USG_CHANNEL_OTT_RATE;

truncate table USG_CONTENT;

truncate table USG_FILE_MOVIE;

truncate table USG_PROGRAM;

truncate table USG_PT;

truncate table USM_RTSP_LOG;

truncate table USM_SESSION;

truncate table USM_SESSION_HISTORY;

 

--数据库：BMS

truncate table T_BILL_RECORD;

truncate table T_BUSINESS;

truncate table T_BUSINESS_SERVICE_RELA;

truncate table T_CHANNEL;

truncate table T_CONP_CONTENT_RELA;

truncate table T_CONTENT;

truncate table t_customer;

truncate table T_INTERFACE_LOG;

truncate table T_LOGIN_USER;

truncate table T_MOD;

truncate table T_OPERATION_LOG;

truncate table T_PRODUCT;

truncate table T_PRODUCT_CONTENT_RELA;

truncate table T_PRODUCT_OFFER;

truncate table T_PRODUCT_OFFER_PRODUCT_RELA;

update t_seq_bms t set t.seq_value = 1;

truncate table T_SYNC_MESSAGE;

truncate table t_user;

 

--数据库：AAA

truncate table T_BUSINESS;

truncate table T_CHANNEL;

truncate table T_CONTENT;

truncate table T_CONTENTPACKAGE_CONTENT;

truncate table T_CONTENT_PACKAGE;

truncate table T_DEVICE;

truncate table T_LOGIN_INFO;

truncate table T_LOGIN_USER;

truncate table T_MESSAGESYNC_HIS;

truncate table T_PRODUCT;

truncate table T_PRODUCT_CONTENT;

truncate table T_PRODUCT_OFFER;

truncate table T_PRODUCT_OFFER_PROD_REF;

truncate table T_USER_DEVICE_REF;

 

--数据库：PORTALMS

truncate table imp_column;

truncate table T_CATEGORY_CACHES_TMP;

truncate table T_ASSET_TRANSITION;

truncate table T_BOOKMARK;

truncate table T_BUSINESS_INFO;

truncate table T_CATEGORY;

truncate table T_CATEGORYINFO;

truncate table T_CATEGORY_BACKUP;

truncate table T_CHANNEL;

truncate table T_CHANNEL_EPG;

truncate table T_CHANNEL_EPG_AFTER;

truncate table T_CONTENT;

truncate table T_CONTENTPACKAGE;

truncate table T_FOREST;

truncate table T_GRADE;

truncate table t_key_words;

truncate table T_MOVIE_STOPPOINT;

truncate table T_NPVR;

truncate table t_play_count;

truncate table T_PRODUCT;

truncate table T_PRODUCT_OFFERING;

truncate table T_PRODUCT_OFFER_PROD_REF;

truncate table T_RECOMMEND;

truncate table t_recommend_category_rel;

truncate table T_RECOMMEND_PAGE;

truncate table T_RELA_BUSINESS_SERVICE;

truncate table T_RELA_CONPACK_CONTENT;

truncate table T_RELA_PRODUCT_CONTENT;

truncate table T_SEARCH_RECORD;

update t_seq_category t set t.seq_value = 1;

update t_seq_portal t set t.seq_value = 1;

truncate table T_SYSTEM_MESSAGE;

truncate table T_USER_GRADE;

truncate table T_USER_PLAY;

truncate table t_operate_log;