oracle創(chuàng)建用戶,分配權(quán)限以及建立變空間

1、一、創(chuàng)建表空間及用戶并授權(quán)1:創(chuàng)建臨時(shí)表空間create temporary tablespace user_temptempfile 'Q:oracleproduct10.2.0oradataTestxyrj_temp.dbf'size 50mautoextend onnext 50m maxsize 20480mextent management local;2:創(chuàng)建數(shù)據(jù)表空間create tablespace user_dataloggingdatafile 'Q:oracleproduct10.2.0oradataTestxyrj_data.dbf'si

2、ze 50mautoextend onnext 50m maxsize 20480mextent management local;第3步:創(chuàng)建用戶并指定表空間create user username identified by passworddefault tablespace user_datatemporary tablespace user_temp;第4步:給用戶授予權(quán)限grant connect,resource,dba to username;第二部分:刪除用戶及表空間/刪除用戶以及用戶所有的對象drop user zfmi cascade;/cascade參數(shù)是級聯(lián)刪除該用戶

3、所有對象,經(jīng)常遇到如用戶有對象而未加此參數(shù)則用戶刪不了的問題,所以習(xí)慣性的加此參數(shù)/刪除表空間前提:刪除表空間之前要確認(rèn)該表空間沒有被其他用戶使用之后再做刪除drop tablespace zfmi including contents and datafiles cascade onstraints;/including contents 刪除表空間中的內(nèi)容,如果刪除表空間之前表空間中有內(nèi)容,而未加此參數(shù),表空間刪不掉,所以習(xí)慣性的加此參數(shù)/including datafiles 刪除表空間中的數(shù)據(jù)文件/cascade constraints 同時(shí)刪除tablespace中表的外鍵參照二、o

4、racle數(shù)據(jù)庫的導(dǎo)入和導(dǎo)出下面介紹的是導(dǎo)入導(dǎo)出的實(shí)例,向?qū)雽?dǎo)出看實(shí)例基本上就可以完成,因?yàn)閷?dǎo)入導(dǎo)出很簡單。數(shù)據(jù)導(dǎo)出:1 將數(shù)據(jù)庫TEST完全導(dǎo)出,用戶名system 密碼manager 導(dǎo)出到D:daochu.dmp中exp system/managerTEST file=d:daochu.dmp full=y2 將數(shù)據(jù)庫中system用戶與sys用戶的表導(dǎo)出exp system/managerTEST file=d:daochu.dmp owner=(system,sys3 將數(shù)據(jù)庫中的表table1 、table2導(dǎo)出exp system/managerTEST file=d:daoc

5、hu.dmp tables=(table1,table24 將數(shù)據(jù)庫中的表table1中的字段filed1以"00"打頭的數(shù)據(jù)導(dǎo)出exp system/managerTEST file=d:daochu.dmp tables=(table1 query=" where filed1 like '00%'"上面是常用的導(dǎo)出,對于壓縮我不太在意,用winzip把dmp文件可以很好的壓縮。不過在上面命令后面加上compress=y 就可以了數(shù)據(jù)的導(dǎo)入1 將D:daochu.dmp 中的數(shù)據(jù)導(dǎo)入TEST數(shù)據(jù)庫中。imp system/manage

6、rTEST file=d:daochu.dmp上面可能有點(diǎn)問題,因?yàn)橛械谋硪呀?jīng)存在,然后它就報(bào)錯(cuò),對該表就不進(jìn)行導(dǎo)入。在后面加上ignore=y 就可以了。2 將d:daochu.dmp中的表table1 導(dǎo)入imp system/managerTEST file=d:daochu.dmp tables=(table1基本上上面的導(dǎo)入導(dǎo)出夠用了。不少情況我是將表徹底刪除,然后導(dǎo)入。注意:你要有足夠的權(quán)限,權(quán)限不夠它會提示你。數(shù)據(jù)庫時(shí)可以連上的?？梢杂胻nsping TEST 來獲得數(shù)據(jù)庫TEST能否連上。數(shù)據(jù)導(dǎo)出:exp hkb/hkbboss_14 full=y file=c:oraback

7、uphkbfull.dmp log=c:orabackuphkbfull.log; 導(dǎo)出注意事項(xiàng):導(dǎo)出的是當(dāng)前用戶的的數(shù)據(jù),當(dāng)前用戶如果有DBA的權(quán)限,則導(dǎo)出所有數(shù)據(jù)!同名用戶之間的數(shù)據(jù)導(dǎo)入:imp hkb/hkbxe file=c:orabackuphkbfull.dmp log=c:orabackuphkbimp.log full=y不同名之間的數(shù)據(jù)導(dǎo)入:imp system/testxe fromuser=hkb touser=hkb_new file=c:orabackuphkbfull.dmplog=c:orabackuphkbimp.log;三、相關(guān)的更改語句(alter的用法/0

8、.重命名/0.1 表:rename dept to dt;rename dt to dept;/0.2 列:alter table dept rename column loc to location;alter table dept rename column location to loc;/1.添加約束/1.1 primary keyalter table employee_info add constraint pk_emp_info primary key(empno; /1.2 foreign keyalter table employee_info add constraint

9、fk_emp_info foreign key(deptnoreferences dept(deptno;/1.3 checkalter table employee_info add constraint ck_emp_info check(sex in ('F','M'/1.4 not nullalter table employee_info modify phone constraint not_null_emp_info not null; /1.5 uniquealter table employee_info add constraint uq_e

10、mp_info unique(phone;/1.6 defaultalter table employee_info modify sex char(2 default 'M'/2.添加列alter table employee_info add id varchar2(18;alter table employee_info add hiredate date default sysdate not null;/3.刪除列alter table employee_info drop column introduce;/3.修改列/3.1 修改列的長度alter table d

11、ept modify loc varchar2(50;/3.2 修改列的精度alter table employee_info modify empno number(2;/3.3 修改列的數(shù)據(jù)類型alter table employee_info modify sex char(2;/3.4 修改默認(rèn)值alter table employee_info modify hiredate default sysdate+1;/4.禁用約束alter table employee_info disable constraint uq_emp_info;/5.啟用約束alter table empl

12、oyee_info enable constraint uq_emp_info;/6.延遲約束alter table employee_info drop constraint fk_emp_info;alter table employee_info add constraint fk_emp_info foreign key(deptno references dept(deptnodeferrable initially deferred;/7.向表中添加注釋comment on table employee_info is 'information of employees&#

13、39;/8.向列添加注釋comment on column employee_info.ename is 'the name of employees' comment on column dept.dname is 'the name of department'/9.清除表中所有數(shù)據(jù)truncate table employee_info;/10.刪除表drop table employee_info;/11修改session默認(rèn)時(shí)間格式alter session set nls_date_format ='yyyy-mm-dd'alter

14、session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'/12修改用戶密碼Alter user user_name identified by password./13 修改最大連接數(shù)第一步,在cmd命令行,輸入sqlplus第二步源碼天空,根據(jù)提示輸入用戶名與密碼1. 查看processes和sessions參數(shù)SQL> show parameter processesNAME TYPE VALUEdb_writer_processes integer 1gcs_server_processes integer 0job

15、_queue_processes integer 10log_archive_max_processes integer 2processes integer 50SQL> show parameter sessionsNAME TYPE VALUElicense_max_sessions integer 0license_sessions_warning integer 0logmnr_max_persistent_sessions integer 1sessions integer 60shared_server_sessions integer2. 修改processes和sess

16、ions值SQL> alter system set processes=300 scope=spfile;系統(tǒng)已更改。SQL> alter system set sessions=335 scope=spfile;系統(tǒng)已更改。3. 修改processes和sessions值必須重啟oracle服務(wù)器才能生效ORACLE的連接數(shù)(sessions與其參數(shù)文件中的進(jìn)程數(shù)(process有關(guān),它們的關(guān)系如下: sessions=(1.1*process+5摘(二查詢數(shù)據(jù)庫當(dāng)前進(jìn)程的連接數(shù):select count(* from v$process;查看數(shù)據(jù)庫當(dāng)前會話的連接數(shù):elect

17、 count(* from v$session;查看數(shù)據(jù)庫的并發(fā)連接數(shù):select count(* from v$session where status='ACTIVE'查看當(dāng)前數(shù)據(jù)庫建立的會話情況:select sid,serial#,username,program,machine,status from v$session;查詢數(shù)據(jù)庫允許的最大連接數(shù):select value from v$parameter where name = 'processes'或者:show parameter processes;修改數(shù)據(jù)庫允許的最大連接數(shù):alter

18、system set processes = 300 scope = spfile;(需要重啟數(shù)據(jù)庫才能實(shí)現(xiàn)連接數(shù)的修改重啟數(shù)據(jù)庫:shutdown immediate;startup;查看當(dāng)前有哪些用戶正在使用數(shù)據(jù):select osuser,a.username,cpu_time/executions/1000000|'s',sql_fulltext,machine from v$session a,v$sqlarea b where a.sql_address = b.address order by cpu_time/executions desc; 備注：UNIX 1 個(gè)用戶 session 對應(yīng)一個(gè)操作系統(tǒng) process，而 Windows 體現(xiàn)在線程。 啟動 oracle su - oracle sqlplus system/pwd as sysdba /進(jìn)入