20140327-bb

1.sqlplus / as sysdba--linux中进它创建用户并赋权限

CREATE USER wxm_test IDENTIFIED BY wxm_test
DEFAULT TABLESPACE TBS_RPT_STAT
TEMPORARY TABLESPACE TBS_RPT_TEMP;
GRANT DBA TO wxm_test;
GRANT EXECUTE ANY PROCEDURE TO wxm_test;
GRANT INSERT ANY TABLE TO wxm_test;
GRANT SELECT ANY TABLE TO wxm_test;
GRANT UNLIMITED TABLESPACE TO wxm_test;
GRANT READ ON DIRECTORY CYPRUS TO wxm_test;
GRANT WRITE ON DIRECTORY CYPRUS TO wxm_test;
2.--不带目录导入数据
imp wxm_test/wxm_test@ora11g file=DB.wxm_test.20140303125257.dmp full=y
imp wxm_test/wxm_test fromuser=wxm_test touser=reportdbcy  file=DB.wxm_test.20140303125257.dmp full=y ignore=y
imp wxm_test/wxm_test fromuser=wxm_test touser=reportdbcy  file=DB.wxm_test.201403031252
3.--带目录导入数据
sqlplus wxm_test/wxm_test
create or replace directory cy as '/home/oracle/upgrade/bak03';
impdp wxm_test/wxm_test directory=cy  dumpfile=DB.wxm_test.20140303125257.dmp -- logfile=reportdb_for_tps.log

 

sqlplus wxm_test/wxm_test
& *.sql回车执行sql语句

4.--linux中oracle常用

vi $ORACLE_BASE/product/11g/db/network/admin/tnsnames.ora
echo $ORACLE_SID
lsnrctl stop
lsnrctl status
lsnrctl start;
su - etlgh
ps -ef|grep iweb
ll -t
ll *.dmp
df -h
du -sh *
du -sh bak_wxm_test_20140319.dmp
ls -la DB.wxm_test.20140303125257.dmp
rm -rf *
uname
pwd
more upgrade.log
cat /etc/fstab
5.--如查看存储过程p_wxm_tests_transaction;前面一个语句一定加;(如：t_wxm_test; p_wxm_tests_transaction;),要不然会查看不到

6.PL/SQL客户端中可以通过点击export query reslut...图标选择sql file导出插入语句。这样可以在其他数据中创建相同的表，直接执行导出的sql file，执行commit。

7.select * from user_tables where table_name like 'T_WXM_TEST_%';--(T_WXM_TEST_大写才可以查哦)

8.select *from user_jobs where what like 'P_SAG_JOB_KPI_DISPSAG'

9.select * from t_sys_info_version--查看数据库版本

10.bill-upload-min_table-日结过程到date_table-分表过程到month-table;etl:抽取数据到中间表-结果表