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;