e 60 password_grace_time 10; 5.drop a profile sql> drop profile grace_5 [cascade]; 6.create a profile : resource limit sql> create profile developer_prof limit sessions_per_user 2 sql> cpu_per_session 10000 idle_time 60 connect_time 480; 7. view => resource_cost : alter resource cost dba_Users,dba_profiles 8. enable resource limits sql> alter system set resource_limit=true; 第九章:Managing users 1.create a user: database authentication sql> create user juncky identified by oracle default tablespace users sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default]; 2.change user quota on tablespace sql> alter user juncky quota 0 on users; 3.drop a user sql> drop user juncky [cascade]; 4. monitor user view: dba_users , dba_ts_quotas 第十章:managing privileges 1.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs 2.grant system privilege sql> grant create session,create table to managers; sql> grant create session to scott with admin option; with admin option can grant or revoke privilege from any user or role; 3.sysdba and sysoper privileges: sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile, alter tablespace begin/end backup,recover database alter database archivelog,restricted session sysdba: sysoper privileges with admin option,create database,recover database until 4.password file members: view:=> v$pwfile_users 5.O7_dictionary_accessibility =true restriction access to view or tables in other schema 6.revoke system privilege sql> revoke create table from karen; sql> revoke create session from scott; 7.grant object privilege sql> grant execute on dbms_pipe to public; sql> grant update(first_name,salary) on employee to karen with grant option; 8.display object privilege : view => dba_tab_privs, dba_col_privs 9.revoke object privilege sql> revoke execute on dbms_pipe from scott [cascade constraints]; 10.audit record view :=> sys.aud$ 11. protecting the audit trail sql> audit delete on sys.aud$ by access; 12.statement auditing sql> audit user; 13.privilege auditing sql> audit select any table by summit by access; 14.schema object auditing sql> audit lock on summit.employee by access whenever successful; 15.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts 16.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object, dba_audit_session,dba_audit_statement 第十一章: manager role 1.create roles sql> create role sales_clerk; sql> create role hr_clerk identified by bonus; sql> create role hr_manager identified externally; 2.modify role sql> alter role sales_clerk identified by commission; sql> alter role hr_clerk identified externally; sql> alter role hr_manager not identified; 3.assigning roles sql> grant sales_clerk to scott; sql> grant hr_clerk to hr_manager; sql> grant hr_manager to scott with admin option; 4.establish default role sql> alter user scott default role hr_clerk,sales_clerk; sql> alter user scott default role all; sql> alter user scott default role all except hr_clerk; sql> alter user scott default role none; 5.enable and disable roles sql> set role hr_clerk; sql> set role sales_clerk identified by commission; sql> set role all except sales_clerk; sql> set role none; 6.remove role from user sql> revoke sales_clerk from scott; sql> revoke hr_manager from public; 7.remove role sql> drop role hr_manager; 8.display role information view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs, role_sys_privs,role_tab_privs,session_roles 第十二章: BACKUP and RECOVERY 1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat 2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size 3. Monitoring Parallel Rollback > v$fast_start_servers , v$fast_start_transactions 4.perform a closed database backup (noarchivelog) > shutdown immediate > cp files /backup/ > startup 5.restore to a different location > connect system/manager as sysdba > startup mount > alter database rename file ''''/disk1/../user.dbf'''' to ''''/disk2/../user.dbf''''; > alter database open; 6.recover syntax --recover a mounted database >recover database; >recover datafile ''''/disk1/data/df2.dbf''''; >alter database recover database; --recover an opened database >recover tablespace user_data; >recover datafile 2; >alter database recover datafile 2; 7.how to apply redo log files automatically >set autorecovery on >recover automatic datafile 4; 8.complete recovery: --method 1(mounted databae) >copy c:\backup\user.dbf c:\oradata\user.dbf >startup mount >recover datafile ''''c:\oradata\user.dbf; >alter database open; --method 2(opened database,initially opened,not system or rollback datafile) >copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline) >recover datafile ''''c:\oradata\user.dbf'''' or >recover tablespace user_data; >alter database datafile ''''c:\oradata\user.dbf'''' online or >alter tablespace user_data online; --method 3(opened database,initially closed not system or rollback datafile) >startup mount >alter database datafile ''''c:\oradata\user.dbf'''' offline; >alter database open >copy c:\backup\user.dbf d:\oradata\user.dbf >alter database rename file ''''c:\oradata\user.dbf'''' to ''''d:\oradata\user.dbf'''' >recover datafile ''''e:\oradata\user.dbf'''' or recover tablespace user_data; >alter tablespace user_data online; --method 4(loss of data file with no backup and have all archive log) >alter tablespace user_data offline immediate; >alter database create datafile ''''d:\oradata\user.dbf'''' as ''''c:\oradata\user.dbf'''''''' >recover tablespace user_data; >alter tablespace user_data online 5.perform an open database backup > alter tablespace user_data begin backup; > copy files /backup/ > alter database datafile ''''/c:/../data.dbf'''' end backup; > alter system switch logfile; 6.backup a control file > alter database backup controlfile to ''''control1.bkp''''; > alter database backup controlfile to trace; 7.recovery (noarchivelog mode) > shutdown abort > cp files > startup 8.recovery of file in backup mode >alter database datafile 2 end backup; 9.clearing redo log file >alter database clear unarchived logfile group 1; >alter database clear unarchived logfile group 1 unrecoverable datafile; 10.redo log recovery >alter database add logfile group 3 ''''c:\oradata\redo03.log'''' size 1000k; >alter database drop logfile group 1; >alter database open; or >cp c:\oradata\redo02.log'''' c:\oradata\redo01.log >alter database clear logfile ''''c:\oradata\log01.log'''';------------------------------- 1、查看当前所有对象 SQL> select * from tab; 2、建一个和a表结构一样的空表 SQL> create table b as select * from a where 1=2; SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2; 3、察看数据库的大小,和空间使用情况 SQL> col tablespace format a20 SQL> select b.file_id 文件ID, b.tablespace_name 表空间, b.file_name 物理文件名, b.bytes 总字节数, (b.bytes-sum(nvl(a.bytes,0))) 已使用, sum(nvl(a.bytes,0)) 剩余, sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name / dba_free_space --表空间剩余空间状况 dba_data_files --数据文件空间占用情况 4、查看现有回滚段及其状态 SQL> col segment format a30 SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS; 5、查看数据文件放置的路径 SQL> col file_name format a50 SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 6、显示当前连接用户 SQL> show user 7、把SQL*Plus当计算器 SQL> select 100*20 from dual; 8、连接字符串 SQL> select 列1||列2 from 表1; SQL> select concat(列1,列2) from 表1; 9、查询当前日期 SQL> select to_char(sysdate,''''yyyy-mm-dd,hh24:mi:ss'''') from dual; 10、用户间复制数据 SQL> copy from user1 to user2 create table2 using select * from table1; 11、视图中不能使用order by,但可用group by代替来达到排序目的 SQL> create view a as select b1,b2 from b group by b1,b2; 12、通过授权的方式来创建用户 SQL> grant connect,resource to test identified by test; SQL> conn test/test
上一页 [1] [2] 没有相关教程
|