Oracle命令行工具基本操作及SQL命令

命令行工具基本操作及SQL命令1.基本概念1.1.数据类型基本数据类型（NUMBER，VARCHAR2，DATE）ORACEL支持下列内部数据类型：VARCHAR2变长字符串，最长为2000字符。NUMBER数值型。LONG变长字符数据，最长为2G字节。DATE日期型。RAW二进制数据，最长为255字节。LONGRAW变长二进制数据，最长为2G字节。ROWID二六进制串，表示表的行的唯一地址。CHAR定长字符数据，最长为255。2.SQL*PLUS这是个Oracle提供的最常用，也是最好用的sql命令执行工具。2.1.数据库系统管理2.1.1.登录C:sqlpluswC:sqlplus/nologSQLconnusername/password@Oranet如，system登录第2页共9页SQLconnsystem/systempwd@whfc如果要行一些只有sysdba才能执行的命令，必须以sysdba特权登录：SQLconnsys/syspwd@whfcassysdba2.1.2.创建表空间必须有CREATETABLESPACE特权的用户才能创建表空间，比如system和sys用户。SQLconnsystem@whfc01请输入口令:已连接。SQLcreatetablespacets_testdatafile'/data2/oradata/ciis/ts_test01.dbf'size10m;表空间已创建。2.1.3.添加数据文件SQLaltertablespacets_testadddatafile'/data2/oradata/ciis/ts_test02.dbf'size10m;表空间已更改。2.1.4.查看表空间大小SQLDESCDBA_DATA_FILES名称是否为空?类型------------------------------------------FILE_NAMEVARCHAR2(513)FILE_IDNUMBERTABLESPACE_NAMEVARCHAR2(30)BYTESNUMBERBLOCKSNUMBER第3页共9页STATUSVARCHAR2(9)RELATIVE_FNONUMBERAUTOEXTENSIBLEVARCHAR2(3)MAXBYTESNUMBERMAXBLOCKSNUMBERINCREMENT_BYNUMBERUSER_BYTESNUMBERUSER_BLOCKSNUMBERSQLSELECTTABLESPACE_NAME,SUM(BYTES)/1024/1024M2FROMDBA_DATA_FILES3GROUPBYTABLESPACE_NAME;TABLESPACE_NAMEM------------------------------DEVELOP18000DEVELOP214336DEVELOPINDEX4106DRSYS20EXAMPLE145.625INDX25ODM20SYSTEM1024TOOLS10TS_CI_134094TS_CI_324094TS_CI_332047TS_II_132047TS_II_322047TS_PI_13012047第4页共9页TS_PI_13022047TS_PI_32012047TS_TEST20UNDOTBS15048USERS25XDB38.125已选择21行。SQLSELECTTABLESPACE_NAME,SUM(BYTES)/1024/1024M2FROMDBA_DATA_FILES3WHERETABLESPACE_NAME='TS_TEST'4GROUPBYTABLESPACE_NAME;TABLESPACE_NAMEM------------------------------TS_TEST202.1.5.查看自由（剩余）表空间大小SQLdescDBA_FREE_SPACE名称是否为空?类型---------------------------------------------TABLESPACE_NAMEVARCHAR2(30)FILE_IDNUMBERBLOCK_IDNUMBERBYTESNUMBERBLOCKSNUMBERRELATIVE_FNONUMBER第5页共9页SQLSELECTTABLESPACE_NAME,SUM(BYTES)/1024/1024M2FROMDBA_FREE_SPACE3WHEREtablespace_name='TS_TEST'4GROUPBYTABLESPACE_NAME;TABLESPACE_NAMEM------------------------------TS_TEST19.68752.1.6.创建新用户SQLcreateusertestidentifiedbytestdefaulttablespacets_testtemporarytablespacetemp;用户已创建2.1.7.给用户角色特权SQLgrantconnect,resourcetotest;授权成功。2.2.用户数据对象2.2.1.查看当前用户表名SQLselect*fromtab;TNAMETABTYPECLUSTERID-----------------------------------------------BONUSTABLECCTABLEDEPTTABLE第6页共9页EMPTABLEEMP_IOTTABLESALGRADETABLE已选择6行。2.2.2.创建数据表SQLCREATETABLEbook(2bookidNUMBER(18),3booknameVARCHAR2(80)NOTNULL,4authorVARCHAR2(40),5priceNUMBER(6,2)6);表已创建。2.2.3.创建索引SQLCREATEINDEXidx_book_bookidONbook(bookname);索引已创建。2.2.4.创建主键约束SQLALTERTABLEbookADDCONSTRAINTpk_book_bookidPRIMARYKEY(bookid);表已更改。2.2.5.显示表结构SQLdescbook名称是否为空?类型------------------------------------------------------------第7页共9页BOOKIDNOTNULLNUMBER(18)BOOKNAMENOTNULLVARCHAR2(80)AUTHORVARCHAR2(40)PRICENUMBER(6,2)2.2.6.查看表的索引SQLcolumnindex_nameformata30SQLselecttable_name,index_namefromuser_indexes;TABLE_NAMEINDEX_NAME------------------------------------------------------BOOKIDX_BOOK_BOOKNAMEBOOKPK_BOOK_BOOKID2.2.7.查看索引列SQLselecttable_name,index_name,column_name,column_positionfromuser_ind_columns;TABLE_NAMEINDEX_NAMECOLUMN_NAMECOLUMN_POSITION--------------------------------------------------------------BOOKPK_BOOK_BOOKIDBOOKID1BOOKIDX_BOOK_BOOKNAMEBOOKNAME12.2.8.查看数据段占空间大小数据段包括表、索引、分区等。SQLdescuser_segments名称是否为空?类型----------------------------------------------------第8页共9页SEGMENT_NAMEVARCHAR2(81)PARTITION_NAMEVARCHAR2(30)SEGMENT_TYPEVARCHAR2(18)TABLESPACE_NAMEVARCHAR2(30)BYTESNUMBERBLOCKSNUMBEREXTENTSNUMBERINITIAL_EXTENTNUMBERNEXT_EXTENTNUMBERMIN_EXTENTSNUMBERMAX_EXTENTSNUMBERPCT_INCREASENUMBERFREELISTSNUMBERFREELIST_GROUPSNUMBERBUFFER_POOLVARCHAR2(7)SQLselectsegment_name,segment_type,bytesfromuser_segments;SEGMENT_NAMESEGMENT_TYPEBYTES------------------------------------------------------BOOKTABLE65536IDX_BOOK_BOOKNAMEINDEX65536PK_BOOK_BOOKIDINDEX65536第9页共9页2.2.9.查看表占空间大小SQLselectsegment_name,segment_type,bytesfromuser_segmentswheresegment_type='TABLE';SEGMENT_NAMESEGMENT_TYPEBYTES------------------------------------------------------BOOKTABLE65536