Basic Oracle database operation statements

Basic Oracle database operation statements

This article mainly introduces the basic statements and concepts of Oracle database management.

1. How to query the permissions of a role?
A. System permissions contained in a role
Select * from dba_sys_privs where grantee = 'connection'
Or select * from role_sys_privs where role = 'connect'
B. object permissions contained in a role
Select * from dba_tab_privs where grantee = 'connection'
Or select * from role_tab_privs where role = 'connection'
2. How many roles does oracle have?
Select * from dba_roles
3. How can I view the roles of a user?
Select * from dba_role_privs where grantee = 'sys'
4. How to display all data dictionary views that the current user can access?
Select * from dict where comments like '% grant %'
5. How to display the full name of the current database?
Select * from global_name
6. Know the tablespace name and display all tables included in the tablespace.
Select * from all_tables where tablespace_name = 'tablespace name'
7. Know the table name and display the tablespace to which the table belongs.
Select tablespace_name, table_name from user_tables where table_name = 'table name'
8. view the size of the SGA, PGA, sorting area, large pool, and JAVA pool of oracle.
Show parameter sga_max_size;
Show parameter pga_aggregate_target;
Show parameter sort_area_size;
Show parameter large_pool_size;
9. oracl Storage Structure: instance structure:
1. Physical storage structure (data file *. dbf, control file *. ctl, redo log file *. log) 1. Memory Structure
2. Logical Storage Structure 2. Process Structure
10. Data File *. dbf
Save all the data in the table, such as records and indexes,
View data file information select file_name from dba_data_files;
11. Control File *. ctl
Binary file, very small, stores data files and Log File Information
View Control File Information select name from V $ controlfile
12. Redo the log file *. log
Files that record database modification Information
13. tablespace
A tablespace is the largest logical Storage Structure in oracle. It corresponds to a physical active data file. Each oracle Database
At least one tablespace is owned. The tablespace size is equal to the total size of all data files in the tablespace.
All content created in the database. When creating a table, you can specify a tablespace to store the table. If no tablespace is specified
The system will store the content created by the user to the default tablespace.
14. default tablespace created by the System
1. system is used to store system data dictionaries, system management information, and user data tables.
2. The sysaux auxiliary system tablespace is used to reduce the load on the system tablespace and improve the system operation efficiency.
3. temp temporary tablespace
4. undotbsl undo tablespace
5. users user tablespace, used to store permanent user objects and private information
View the system tablespace select tablespace_name from dba_tablespaces;
15. Data Dictionary
Stores information about database objects and segments, such as tables, views, indexes, packages, stored procedures, users, permissions, roles, audits, and constraints.
Data Dictionary view type:
1. user View: records user object information, such as user_tables
2. all view: records the information of user objects and the information of authorized objects, such as all_synonyms.
3. dba view: records information of all objects in the database instance, such as dba_tables.
4. V $ view: records dynamic performance statistics related to database activities, such as v $ datafile
5. GV $ view: records the dynamic information of all instances in a distributed environment, such as the gv $ lock view.
Basic Data Dictionary;
Dba_tables: information about all tables of all users
Dab_tab_columns: Information about fields in all user tables
Dba_views: all views of all users
Dba_synonyms: synonym information for all users
Dba_sequences: sequence information of all users
Dba_constraints: Constraints of all user tables
Dba_indexes: Brief index information for all users' tables
Dba_ind_columns: Index field information of all users
16. view the default connected database
Select name from v $ database;
17. Three ways to connect to the database
1. Connect to the database through sqlplus
2. Connect to the database through cmd, such as sqlplus system/123456 @ XE
3. Switch the login user mode after logon, such as connect sys/123456 as sysdba;
How can I check who is currently logged on to the console? Select user from dual;
18. SQL * plus command
1. help: view how to use the command, such as help desc
2. host switches from the SQL * plus environment to the operating system environment to execute operating system commands
3. clear screen
4. show [all | user | sga | errors | release | parameter] view all system variables of SQL * Plus. Currently, the user
When SQL * Plus is used, the SGA size, error information, database version number, and system initialization parameter information are displayed.
5. view the structure of objects such as tables, views, stored procedures, functions, and packages.
6. A [PPEND] text appended to the current line
7. CL [EAR] BUFF [ER] clears all rows in the buffer
8. C [HANGE]/old/new replace old with new in the current row
9. I [NPUT text inserts the specified text
10. DEL m n deletes the command line from line m to line n.
11. L [IST] n: List row n
12. R [UN] Or/display the statements in the buffer and run these statements
13. n treats row n as the current row
14. n text replaces line n with text
15.0 text insert text before the first line
16. save SAV [E] [FILE] file_name [gr [ATE] | REP [accept] | APP [END] save the buffer content
For example, save E: \ query. SQL
17. GET [FILE] file_name [LIST | NOLIST] GET the save command
18. START reads the file content to the buffer and runs such as start e: \ query. SQL or replace START @ E: \ query. SQL @.
19. ED [IT] [file_name] Open notepad and edit the buffer File
20. SPO [OL] [file_name [re [ATE] | REP [distinct] | APP [END] | OFF | OUT] convert the output results in SQL * PLUS
Copy to another specified file, such as spool d: \ query. SQL append
19. Temporary Variables
20. variables defined
DEF [INE] displays all defined Variables
DEF [INE] variable = value
ACC [EPT] variable user input variable such as: ACCEPT test number format 9999 PROMPT hello, enter a value 'hide
21. COLUMN
22. pagesize set pagesize 20;
23. linesize set linesize 120;
24. Create a simple report
TTI [TLE] sets the header
BTI [TLE] sets the footer
25. BREAK and COMPUTE commands
26. Create a tablespace
For example, create tablespace myspace datafle 'd: \ app \ myspace. dbf' size
10 M autoextend on next 5 M maxsize 100 M;
27. Table space status attributes
1. online alter tablespace tablespace_name online;
2. offline alter tablespace tablespace_name offline parameter (normal | temporary | immediate | for recover );
3. read-only read only alter tablespace tablespace_name read only
4. read/write alter tablespace tablespace_name read write
28. Modify the tablespace size
1. Modify the size of the tablespace data file alter database datafile file_name (full path) RESIZE newsize K | M
2. ADD the TABLESPACE data file alter tablespace tablespace_name add datafile file_name SIZE number K | M
3. Modify the automatic scalability of the data files in the tablespace. alter database datafile file_name AUTOEXTEND ON
29. Table space data file status
1. ONLINE
2. OFFLINE
3. OFFLINE DROP
30. Move the data files in the tablespace
1. alter tablespace tablespace_name OFFLINE
2. Move the data file to another disk
3. rename datafile 'data file path 1' TO 'data file path 2'
4. alter tablespace tablespace_name ONLINE
31. Rename the tablespace
When the TABLESPACE is ONLINE, alter tablespace tablespace_name rename to new_tablespace_name
32. Delete tablespaces
Drop tablespace tablespace_name INCLUDING CONTENTS AND DATAFILES
33. Create a temporary tablespace
Create temporary tablespace mytemp TEMPFILE 'd: \ mytemp. dbf 'size 5 m autoextend on next 5 m maxsize 20 M
34. Modify temporary tablespace
35. Temporary tablespace Group
View temporary tablespace group information
36. Large file tablespace
Create bigfile tablespace tablespace_name DATAFILE 'path 'size 10 M

This article permanently updates the link address: