Oracle db studay

Oracle db studay first queries the free space select tablespace_name, file_id, block_id, bytes, blocks from dba_free_space; www.2cto.com adds the Oracle tablespace query data file name, size, and path information select tablespace_name, file_id, bytes, file_name from dba_data_files; the statement for modifying the file size is as follows: alter database datafile the path of the data file to be added, that is, the path 'resize 800 M; create an Oracle tablespace create tablespace test datafile '/home/app/oracle/oradata/oracle8i/test01.dbf' size 8 M autoextend on next 5 M max Size 10 M; www.2cto.com create tablespace sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M autoextend on next 50 M maxsize unlimited is not limited by the size of create tablespace sales datafile' /home/app/oracle/oradata/oracle8i/sales01.dbf 'size 800 M autoextend on next 50 M maxsize 1000 M extent management local uniform; unform indicates that the partition size is the same. The default value is 1 M create tablespace sales datafile '/h. Ome/app/oracle/oradata/oracle8i/sales01.dbf 'size 800 M autoextend on next 50 M maxsize 1000 M extent management local uniform size 500 K; unform size 500 K indicates the same partition size, for 500 K create tablespace sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M autoextend on next 50 M maxsize 1000 M extent management local autoallocate; autoallocate indicates that the partition size is automatically and dynamically changed with the table size. Large tables use big tables and small tables use cells create tablespace sale. S datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M autoextend on next 50 M maxsize 1000 M temporary; temporary create dictionary management temporary tablespace create temporary tablespace sales tempfile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800 M autoextend on next 50 M maxsize 1000 M add data to the tablespace file: alter tablespace sales add datafile '/home/app/oracle/oradata/oracle8i/sales02.dbf' size 800 M autoextend on next 50 M maxsize 1000 M; modify the auto scaling attribute: alter database datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf ', '/home/app/oracle/oradata/oracle8i/sales02.dbf'/home/app/oracle/oradata/oracle8i/sales01.dbf autoextend off; CONNECT Role (connection Role) temporary users, especially those who do not need to create tables, usually only give them CONNECTrole. CONNECT is a simple permission for using Oracle. This permission can be meaningful only when you have access to tables of other users, including select, insert, update, and delete. Users with CONNECT role can also create tables, views, sequences, clusters, synonym, sessions, and links with other databases) RESOURCE Role can be granted to more reliable and formal database users. RESOURCE provides users with additional permissions to create their own tables, sequences, procedures, triggers, indexes, and clusters ). DBA Role (database administrator role) DBA Role has all the system permissions, including unlimited space limit and the ability to grant various permissions to other users. SYSTEM is owned by DBA users. The following describes some typical permissions frequently used by DBAs. (1) grant (authorization) command grant connect, resource to user_name; (2) revoke (UNDO) Permission revoke connect, resource from user_name; in addition to the three system roles CONNECT, RESOURCE, and DBA described above, you can also create your own role in Oracle. A role created by a user can be composed of a table or system permission or a combination of the two. To CREATE a role, you must have the create role system permission. Create role role_name; grant select on object_name to role_name; Delete the role drop role role_name; A user with a DBA role can revoke the CONNECT, RESOURCE, and DBA permissions of any other users or even other DBAs. Of course, this is very dangerous. Therefore, unless necessary, DBA permissions should not be granted to general users who are not very important. Revoking all permissions of a user does not mean that the user is deleted from Oracle, nor does it damage any table created by the user; it simply disallow access to these tables. Other users who want to access these tables can access these tables as before. Create user user_name identified by user_password; create user tickets identified by "123456" default tablespace tablespace_name temporary tablespace temp_tablespace_name; modify user alter user user_name identified by user_new_password; delete user drop user user_name; if you have an object, you cannot delete it directly. Otherwise, an error value is returned. You can specify the keyword cascade to delete all objects of a user, and then delete the user drop user user_name cascade. The Oracle user unlocks alter user scott account unlock. This password may be required after unlocking: alter user scott identified by tiger; user locking method: alter user test account lock; sqlplus> host command Force write data file alter system checkpoint; clear cache data dictionary information alter system flush shared_pool; oracle query user tablespace select * from user_all_tables Oracle query all functions and stored procedures select * from user_source Oracle query all users select * from all_users.select * from dba_users Oracle view current user connection select * from v $ session Oracle view current user permissions select * from session_privs query change Oracle user name select user #, name, password from user $ where name = 'user _ name'; select. file_id "FileNo",. tablespace_name "Tablespace_name",. bytes "Bytes",. bytes-sum (nvl (B. bytes, 0) "Used", sum (nvl (B. bytes, 0) "Free", sum (nvl (B. bytes, 0)/. bytes * 100 "% free" from dba_data_files a, dba_free_space B where. file_id = B. file_id (+) group by. tablespace_name,. file_id,. bytes order by. tablespace_name; list the names and status information of all control files in an instance V $ CONTROLFILE list the location and status information of all parameters V $ PARAMETER list part of the information recorded in the control file V $ CONTROLFILE_RECORD_SECTION list the information of the Control File name, status, and location show parameter CONTROL_FILES www.2cto.com back up the control file to the flat file alter database backup controlfile to trace as '/u01/app/oracle/ctl.txt '; reduce the control file to an alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl' scope = spfile; add control files (you can modify them in nomount state) alter system set control_files = '/u01/app/oracle/oradata/orcl/control01.ctl', '/u01/app/oracle/oradata/orcl/control02.ctl ', '/u01/app/oracle/oradata/orcl/control03.ctl' scope = spfile; the hot backup control file alter database backup controlfile to '<dir>' obtains the control file creation script alter database backup controlfile to trace as '<dir>' for recovery in archive mode, directly overwrite the control file alter database backup controlfile to '/u01/app/oracle/control. bak '; used to reconstruct the control file alter database backup controlfile to trace as'/u01/app/oracle/recreate_controlfile.txt '; view the archive log list; startup mount; alter database archivelog; alter database open; switch log alter system switch logfile; view instance and database Information select instance_name, version, status, archiver, database_status from v $ instance; select dbid, name, log_mode from v $ database; view the data file and status information select file_name, tablespace_name, status, online_status from dba_data_files; view the data file select name from v $ datafile; view the temporary file select name from v $ tempfile; view the log file select member from v $ logfile; view the control file select name from v $ controlfile;