创建用户并授权：
CREATE USER hsbcbill IDENTIFIED BY hsbcbill;
grant create session to hsbcbill;
GRANT connect, resource TO hsbcbill;
grant create table to citi;
grant create tablespace to hsbcbill;
grant create view to citi;
GRANT CREATE SEQUENCE TO hsbcbill;
grant dba to hsbcbill;
revoke dba from hsbcbill;
alter user 用户名称 account unlock;
create tablespace DEV_DATA_TBS logging datafile ‘D:\oracleinstall\app\oracle\oradata\XE\DATA_DEV.dbf’ size 50m autoextend on next 32m extent management local;
查看all existing user，tablespace：
connect system/system as sysdba
select * from all_users;
select * from all_tables;
select * from dba_tablespaces;
alter session set current_schema=schema –更新session
set define off;–解决符号问题
select SCHEMANAME,OSUSER,PADDR FROM V$SESSION WHERE SCHEMANAME =’MIZUHOECDS’
select count() from v$process;
select count() from v$session;– 查看数据库当前会话
grant all on peCdapp1.SEQ_BMS_INTEREST_RATE_INFO to pecdapp1
show parameter processes
show parameter sessions
set long 2000
create tablespace HKCCTP_DATA logging datafile ‘D:\oracle10\newTableSpace\HKCCTP_DATA.dbf’ size 500m autoextend on next 32m maxsize 2048m extent management local;
to_timestamp(‘2010-06-27’, ‘syyyy-mm-dd’)
update tnsname.ora, use following command to make it effective
lsnrctl reload
spool on
spool d:\Spool_flatquery.txt
select * from user_sequences;
备份
exp cititmp/cititmp@orcl rows=y indexes=n compress=n buffer=65536 feedback=100000 owner=cititmp file=exp_cititmp_20100703.dmp log=exp_cititmp_20100703.log
恢复
imp citi/citi@orcl fromuser=citidb touser=citi rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=y file=exp_citi_20091112.dmp log=imp_citi3_20091112.log
sqlplus sys/sys@orcl as sysdba
show sga;
修改字符集：
sqlplus sys/sys@orcl as sysdba
sql>shutdown immediate;
set NLS_LANG=AMERICAN_AMERICA.UTF8
update TBL_SWT_COMMON_DATA set CMON_DT_VAL=’2010-03-01’ where CMON_DT_CD=’ECD01003’; –ECDS系统工作日
update TBL_SWT_COMMON_DATA set CMON_DT_VAL=’2010-02-26’ where CMON_DT_CD=’ECD01004’; –ECDS上一工作日
update TBL_SWT_COMMON_DATA set CMON_DT_VAL=’2010-02-25’ where CMON_DT_CD=’ECD01005’; –ECDS上二工作日
update TBL_SWT_COMMON_DATA set CMON_DT_VAL=’2010-02-02’ where CMON_DT_CD=’ECD01006’; –ECDS下一工作日
oracle 删除用户表
select ‘drop table ‘ || table_name ||’ cascade constraints;’||chr(13)||chr(10) from user_tables;
–delete tables
select ‘drop view ‘ || view_name||’;’||chr(13)||chr(10) from user_views;
–delete views
select ‘drop sequence ‘ || sequence_name||’;’||chr(13)||chr(10) from user_sequences;
–delete seqs
select ‘drop function ‘ || object_name||’;’||chr(13)||chr(10) from user_objects where object_type=’FUNCTION’;
–delete functions
select ‘drop procedure ‘ || object_name||’;’||chr(13)||chr(10) from user_objects where object_type=’PROCEDURE’;
–DELETE PROCEDURE
select ‘drop package ‘ || object_name||’;’||chr(13)||chr(10) from user_objects where object_type=’PACKAGE’;
–delete pags
select ‘create sequence ‘||sequence_name||
‘ minvalue ‘||min_value||
‘ maxvalue ‘||max_value||
‘ start with ‘||last_number||
‘ increment by ‘||increment_by||
(case when cache_size=0 then ‘ nocache’ else ‘ cache ‘||cache_size end) ||’;’
from dba_sequences where sequence_owner=’CITI2’ ;
1、查询当前用户下的所有表
select ‘alter table ‘|| table_name ||’ move tablespace tablespacename;’ from user_all_tables;
2、查询当前用户下的所有索引
select ‘alter index ‘|| index_name ||’ rebuild tablespace tablespacename;’ from user_indexes;
3、在当前用户下将查询结果批处理执行即可！
find /n /i “alert” d:\tmp\alert.txt > d:\tmp\alert2.txt
ORACLE DEV
jdbc:oracle:thin:@130.252.15.39:1521:bill
ORACLE LOCAL
jdbc:oracle:thin:@127.0.0.1:1522:orcl
MBFE SQLSERVER:
jdbc:sqlserver://130.252.15.39:1433;databaseName=CITIBMFE
sa/draftpswd
CREATE USER pecdapp1 IDENTIFIED BY pecdapp1 DEFAULT TABLESPACE HKCCTP_DATA;
grant create session to pecdapp1;
GRANT connect, resource TO pecdapp1;
grant create table to pecdapp1;
grant create tablespace to pecdapp1;
grant create view to pecdapp1;
启动oracle
su oracle
sqlplus /nolog
conn /as sysdba
startup
进入/opt/oracle/product/10/bin
lsnrctl start
关闭数据库
sqlplus /as sysdba
sql>shutdown
查看表空间数据文件及最大TABLESPACE
SELECT TABLESPACE_NAME ,round(maxbytes/(1024*1024),0)||’M’MAXSIZE,FILE_NAME FROM DBA_DATA_FILES;
修改表空间的大小
alter database datafile ‘D:\ORACLE\ORADATA\TESTSPACE.DBF’ autoextend on next 32M maxsize 1048M;
查看表空间数据文件大小，及表空间使用率
select b.tablespace_name tablespace_name,
b.bytes/1048576 as “size(MB)”,
(b.bytes-sum(nvl(a.bytes,0)))/1048576 as “used(MB)”,
(b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100 as “Percent(%)”
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id;
转载请注明来源
