oracle_sql创建和删除表空间、数据库、用户

1、查看1、查看表空间的名称及大小select t.tablespace_name, round(sum(bytes/(1024*1024,0 ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;2、查看表空间物理文件的名称及大小select tablespace_name, file_id, file_name, round(bytes/(1024*1024,0 total_space from dba_dat

2、a_files order by tablespace_name;3、查看回滚段名称及大小select segment_name, tablespace_name, r.status,(initial_extent/1024InitialExtent,(next_extent/1024 NextExtent, max_extents, v.curext CurExtent fromdba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+ order by segment_name ;4、查看控制文件select name fr

3、om v$controlfile;5、查看日志文件select member from v$logfile;6、查看表空间的使用情况select sum(bytes/(1024*1024 as free_space,tablespace_name from dba_free_space group by tablespace_name;SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,(B.BYTES*100/A.BYTES "% USED",(C.BYTES*100/A.BYTES &quo

4、t;% FREE" FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;7、查看数据库库对象select owner, object_type, status, count(* count# from all_objects group by owner, object_type, status;8、查看数据库的版本Select version FROM Pro

5、duct_component_version Where SUBSTR(PRODUCT,1,6='Oracle'9. 查看某个表空间内所占空间大于某个值的段（表或索引）:Select segment_name,bytes FROM dba_segments Where bytes>10000000 ANDtablespace_name='tablespace_name'10. 查看所有表空间的碎片程度（值在30以下表示碎片很多）select tablespace_name,sum(bytes,sum(free,sum(free*100/sum(bytesf

6、rom (select b.file_id file_ID, b.tablespace_name tablespace_name, b.bytes Bytes, (b.bytes-sum(nvl(a.bytes,0 used, sum(nvl(a.bytes,0free, sum(nvl(a.bytes,0/(b.bytes*100 Percent from dba_free_spacea,dba_data_files b where a.file_id=b.file_id group byb.tablespace_name,b.file_id,b.bytes order by b.file_

7、id group by tablespace_name order by sum(free*100/sum(bytes;11. 查看自上次数据库启动以来所有数据文件的读写次数select substr(DF.NAME,1,5 Drive, DF.NAMEfile_name, (fs.phyblkrd+fs.phyblkwrt from v$filestat fs,v$datafile df where df.file#=fs.file#;12. 查看数据库中的所有用户：select * from all_users;/orselect * from dba_users创建1. 创建临时表空间c

8、reate temporary tablespace mytesttempfile 'E:/Program/oracle/product/10.1.0/oradata/orcl/mytest.dbf'size 32mautoextend onnext 32m maxsize 1024m extent management local;/必须指定tempfile 的参数2. 创建数据表空间create tablespace mytest_dataloggingdatafile'E:/Program/oracle/product/10.1.0/oradata/orcl/my

9、test_data.dbf'size 32mautoextend onnext 32m maxsize 1024mextent management local;/必须指定datafile 的参数3. 创建用户并指定表空间create user testaccount identified by testpwd default tablespace mytest_data temporary tablespace mytest;4. 权限/授予message 用户dba 角色的所以权限Grant dba to testaccount;/给用户授予权限grant connect,reso

10、urce to testaccount; (db2：指定所有权限5. 创建一个用户：CREATE USER username IDENTIFIED BY password OR IDENTIFIED EXETERNALLYOR IDENTIFIED GLOBALLY AS CN=userDEFAULT TABLESPACE tablespaceTEMPORARY TABLESPACEtemptablespaceQUOTA integer KM UNLIMITED ON tablespace,QUOTA integer KM UNLIMITED ON tablespacePROFILES pro

11、file_namePASSWORD EXPIREACCOUNT LOCK or ACCOUNT UNLOCK；删除1. 删除用户以及用户所有的对象Drop user zfmi cascade;/cascade 参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删除不了的问题，所以习惯性加上次参数2. 删除表空间Drop tablespace zfmi including contents and datafiles cascade onstraints;/ including contents删除表空间内容/ including datafiles 同时删除tablespace 中表的外键参照如果在删除表空间之前删除了表空间对应的数据文件，会造成数据库无法正常启动和关闭 Filename 是已经被删除的数据文件，Tablespace_name是对应的表空间名称$sqlplus/nologSql>conn/as sysdba;如果数据库已经启动，先执行以