PL&SQL学习笔记1

startd:\a.sql@d:\a.sqledit[d:\a.sql]spoold:\a.sql将屏上内容输出到指定文件spooloffsetlinesize320setpagesize100setautoprintonsetserveroutputonvariablev1refcursorexpimp备份恢只读事务settransactionreadonly用处，设置只读事务后，其他用户提交的事务在这里不可见，用处就是用于统计，但又不想取得统计时发生的事务提交用户管理:登陆：sqlplusscott/tigersqlplussystem/rootassysdbasystemsyssysdbascott;;sys超级管理员，具有角色dba;system是系统管理员，角色dbaoper,比sys低一级，没有createdatabase权限创建用户createuserjixiufidentifiedbyjixiuf_passwd;createuseruserNameidentifiedbyyourPasswroddefaulttablespaceuserstemporarytablespacetempquota50Monusersquota400Kontemp;createuseruserNameidentifiedbyyourPasswroddefaulttablespacets1temporarytablespacets2unlimitedonts1;切换用户connsystem/root;disconnect显示当前用户showuser更改密码passworduserName删除用户:dropuserjixiuf[cascade]，如果jixiuf用户已经创建过一些表，加cascade级联删除权限分系统权限和对象权限，系统权限是用户对数据库的控制权，对象权限是用户对其它用户所拥有数据对象的操作权限对象权限：如select,updatedelete,createindex系统权限：如createsession即连接到数据库grant[系统特权名][角色]to[用户名列表][public][withadminoption]grantconnecttojixiuf;角色connect赋予jixiuf此用户可以连接到数据库connect,resource,dba三个重要角色,拥有resource可以在表空间建表，grantresourcetojixiuf授权：grantselectontableNametojixiuf[withgrantoption];具有了select*fromuserName.tableNamegrantselect,update,deleteonuserName.tableNametojixiuf;grantallonuserName.tabletojixiuf;增删改查权如果是对象权限可以加withgrantoption，若是系统权限则带withadminoption如:grantconnnecttojixiufwithadminoption;收回权限：revokeselectonempfromjixiuf;如若加了withgrantoption则revoke级联收回其他人的权限，withadminoption好像不收回建立角色：createroler1[notidentified];常用createroler2[identifiedbypassword];角色授权系统权限grantcreatesessiontor1[withadminoption];grantconectontor1;把connect角色的权限copy一份给r1;select*fromROLE_ROLE_PRIVS;对象权限GRANTSELECTONSCOTT.EMPTOR1;数据字典：SELECT*FROMDICTWHERETABLE_NAMELIKE'%ROLE%';使用profile管理用户口令，profile是口令限制，资源限制的命令集合，当建立数据库时，oracle会自动建立名称为default的profile,当建立用户没有指定profile，则默认用此项分配给用户(1)帐户锁定：指定用户登陆时最多可以输入口令的次数，指定锁定时间，用dba身份执行此命令createprofileprofile_name_lock_userlimitfailed_login_attempts3password_lock_time2;最多尝试3次，3次登陆不成功则不能继续登陆，不成功后允许下次登陆时间为2天后alteruserjixiufprofileprofile_name_lock_user;createuserjixiufidentifiedbyjixiudfprofileprofile_name_lock_user;解锁：alteruserjixiufacoountunlock;定期修改密码createprofilechange_passwordlimitpassword_life_time10password_grace_time2一个密码用10天后必须修改，宽限期2天，这两天会提示用户修改密码口令历史：用户不能使用以前用过的密码createprofilepassword_historylimitpassword_life_time10password_grace_time2password_reuse_time15;15天后可以重用以前的密码删除profiledropprofilepassword_history；对用户作的限制作废connsystem/rootshutdown;ORA-01031:insufficientprivilegesconnsystem/rootassysdba只有作为sysdba登陆时才有startupshutdown权限shutdown;关闭数据库startup;启动数据库---------------------------------备份与恢复-----------------------------------------------------备份与恢复(导入导出)导出:三类,导出表，导出方案(一个用户对应一个方案)，导出数据库用exp命令在导入和导出的时候要用到C:\oracle\product\10.1.0\Db_1\BIN\exp.exeexphelp=y有帮助提示（1）导出表(也可以直接输入exp命令，以交互式进行备份)1导出自已的表：expuserid=scott/tiger@orcltables=(tableName1,tableName2)file=d:\tableName.dmp;2导出别人的表expuserid=scott/tiger@orcltables=(userName.tableName1,userName.tableName2)file=d:\tableName.dmp;3导出表结构，（加一个rows=n）nmeansnoexpuserid=scott/tiger@orcltables=(userName.tableName1,userName.tableName2)file=d:\a.dmprows=n4直接导出方式:比常规导出速度快（加一个direct=y,专门用于导出大表）expuserid=scott/tiger@orcltables=(userName.tableName1,userName.tableName2)file=d:\a.dmpdirect=y(2)导出方案1导出自己方案expuserid=scott/tiger@orclowner=scottfile=d:\scott.dmp2导出其他的人方案expuserid=system/root@orclowner=(system,scott)file=d:\scott.dmp(3)导出数据库(须具有dba权限，或者exp_full_database权限full=yinctype=complete增量备份(第一次complete)expuserid=system/root@orclfull=yinctype=completefile=d:\scott.dmp导入imp(选项：useridtablesfromusertouserfile=d:\a.dmpfull=yinctype=complete增量备份rows=n不导入数据ignore=y若表存在则只导数据)1导入表(1)导入自已的表impuserid=scott/tiger@orclfile=d:\scott.dmptables=emp;impuserid=system/root@orclfile=d:\d.dmpfromuser=scotttouser=jixiuftables=emp;把scott.emp导入到用户jixiuf名下(前提是emp没有外键关联到其他表，否则，因为它关联的表并不在jixiuf中，1法实现主外键关联)(2)只导入表的结构impuserid=scott/tiger@orcltables=(emp)file=d:\scott.dmprows=n(3)导入数据：impuserid=scott/tiger@orcltables=(emp)file=d:\scott.dmpignore=y2导入方案(1)导入自身方案impuserid=scott/tigerfile=d:\scott.dmp(2)导入他人方案impuserid=system/rootfromuser=scotttouser=jixiuffile=d:\scott.dmp(3)导入数据库impuserid=system/rootfull=yfile=d:\scott.dmp注意导入的数据可能会与已有的数据重复（如果原来的数据没丢失，却运行了导入一次命令则可能数据重复，慎!!!）-------------------------数据字典-----------------------------------------------------user_xxx,all_xxx,dba_xxx如user_tablesdba_rolesdba_users,dba_sys_privsdba_tab_privsdba_col_privsdba_role_privsselectusername,user_id,passwordfromdba_users;查用户的信息select*fromdba_role_privswheregrantee='JIXIUF';查jixiuf所具有的roleselect*fromdba_roles查oracle具有的role查一个角色具有的权限（系统权限，对象权限）descdba_sys_privsselect*fromdba_sys_privswheregrantee='CONNECT';或者select*fromrole_sys_privswhererole='CONNECT'后者以assysdba连接，才可以显示全，？？？select*fromdba_tab_privswheregrantee='RESOURCE';数据字典的数据字典dictselect*fromdictwherecommontslike'%TABLES%'SELECT*FROMGLOBAL_NAME;查询当前使用的数据库orcl----------------表空间-----------------------------------段区块createtablespacetsName1datafile'd:\a.dbf'size20muniformsize128k大小20M区的大小128kcreatetablet(idint)tablespacetsName1;select*fromall_tableswheretablespace_name='TSNAME1'