Oracle Common Queries

Oracle Query Table space usage

Select A.tablespace_name "Table space name",
Total "Table space size",
Free "Table space remaining size",
(Total-free) "Tablespace usage Size",
Total/(1024 * 1024 * 1024) "Table space Size (G)",
Free/(1024 * 1024 * 1024) "Table space remaining size (G)",
(total-free)/(1024 * 1024 * 1024) "Table space use Size (G)",
Round ((total-free)/Total, 4) * 100 "Utilization%"
From (SELECT tablespace_name, SUM (bytes) free
From Dba_free_space
GROUP by Tablespace_name) A,
(SELECT tablespace_name, SUM (bytes) Total
From Dba_data_files
GROUP by Tablespace_name) b
WHERE A.tablespace_name = B.tablespace_name

View the default tablespace for the current user

Sql>select username,default_tablespace from User_users;

View the role of the current user

Sql>select * from User_role_privs;

View system and table-level permissions for the current user

Sql>select * from User_sys_privs;

Sql>select * from User_tab_privs;

View all the tables under the user

Sql>select * from User_tables;

1. Users

View the default tablespace for the current user

Sql>select username,default_tablespace from User_users;

View the role of the current user

Sql>select * from User_role_privs;

View system and table-level permissions for the current user

Sql>select * from User_sys_privs;

Sql>select * from User_tab_privs;

Displays the permissions that the current session has

Sql>select * from Session_privs;

Displays the system permissions that the specified user has

Sql>select * from Dba_sys_privs where grantee= ' GAME ';

2. Table

View all the tables under the user

Sql>select * from User_tables;

View a table with a name that contains a log character

Sql>select object_name,object_id from User_objects

where InStr (object_name, ' LOG ') >0;

To view the creation time of a table

Sql>select object_name,created from User_objects where Object_name=upper (' &table_name ');

View the size of a table

Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments

where Segment_name=upper (' &table_name ');

View tables placed in Oracle's memory area

Sql>select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0;

3. Index

View index number and category

Sql>select Index_name,index_type,table_name from User_indexes ORDER by TABLE_NAME;

View the fields indexed by the index

Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');

To view the size of an index

Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments

where Segment_name=upper (' &index_name ');

4. Serial number

View serial number, Last_number is the current value

Sql>select * from User_sequences;

5. View

View the name of the view

Sql>select view_name from User_views;

View the SELECT statement that created the view

Sql>set view_name,text_length from User_views;

Sql>set Long 2000; Description: The size of the set long can be set according to the Text_length value of the view

Sql>select text from User_views where View_name=upper (' &view_name ');

6. Synonyms

View the name of a synonym

Sql>select * from user_synonyms;

7. Constraint conditions

View constraints on a table

Sql>select constraint_name, Constraint_type,search_condition, R_constraint_name

From user_constraints WHERE table_name = UPPER (' &table_name ');

Sql>select C.constraint_name,c.constraint_type,cc.column_name

From User_constraints c,user_cons_columns cc

where C.owner = Upper (' &table_owner ') and c.table_name = Upper (' &table_name ')

and C.owner = Cc.owner and c.constraint_name = Cc.constraint_name

Order BY Cc.position;

8. Storage functions and procedures

View the status of functions and procedures

Sql>select object_name,status from user_objects where object_type= ' FUNCTION ';

Sql>select object_name,status from user_objects where object_type= ' PROCEDURE ';

View source code for functions and procedures

Sql>select text from All_source where Owner=user and Name=upper (' &plsql_name ');

----------------------------------------------------------------------------------------------------------- --------------------------

ORACLE:


----Common Data dictionary

User_
Records information about user objects, such as user_tables contains all tables created by the user; user_views,user_constraints, etc.;
All_
To record the information of the user object and the object information that is authorized to access;
Dba_
Records information about all objects of a DB instance, such as dba_users contained in a DB instance
Information for all users, DBA information contains user and all information;
v$ The dynamic view of the current instance, including the system management and optimized use of the view;
Gv_ a dynamic view of all instances in a distributed environment, including a view of system management and optimized use, where the GV represents the meaning of Global v$;
Launch multiple instances in the current distribution environment:
Set line 160 (160 characters per row)
Select Inst_id,sid,lmode from gv$lock where inst_id = 1 or inst_id=2;

Oracle Common Data dictionary, Oracle10 added a lot of data dictionary 10g R1 has 1713, R2 has 1870:
1 Basic Data dictionary:
Dba_tables information for all tables of all users;
Dba_tab_columns column (field) information for all users ' tables;
Dba_views All view information for all users;
dba_synonyms synonym information for all users;
Dba_sequences all user sequence information;
dba_constraints table constraint information for all users;
Dba_indexes summary information for all user indexes;
Dba_ind_columns column information for all user indexes;
Dba_triggers all user trigger information;
Dba_source all user stored procedure source code information;
Dba_procedus all user stored procedures;
Dba_segments all user segments (tables, indexes, Cluster) using spatial information;
Dba_extents Extended segment information for all user segments;
Dba_objects basic information about all user objects (including vegetarian citation, tables, views, sequences, etc.);
All base tables that the CAT current user can access;
TAB all base tables, views, synonyms, etc. created by the current user;
DICT information for all tables that comprise a data dictionary;


2 data dictionaries related to database components:
Database:
V$database synonym V_$database, record the operation of the system;
Table Space:
Dba_tablespaces record the basic information of the system table space;
Dba_data_files record system data files and table space basic information;
Dba_free_space information about the remaining space in the system table space;
Control files:
V$controlfile records the path information of the system control files;
V$parameter The basic information of each parameter of recording system;
V$controlfile_record_section Record System control operation basic information;
Data files:
Dba_data_files record system data files and table space basic information;
V$datafile records data file information from the control file;
V$filestat record the basic information of data file reading and writing;





--View VGA information:

Show SGA;

SELECT * from V$sgastat;

--You can view the information in several dynamic performance views:
V$sysstat System Statistical Information
V$sesstat User Session Statistics
V$pgastat Displaying memory usage statistics
V$sql_workarea information for the workspace used by SQL cursors
v$ sql_workarea_active information about the current system workspace

--In the v$process dynamic performance view, you can query the memory and memory used by the PGA allocated to each Oracle process,
--where pga_used_mem represents a used, Pag_alloc_mem represents an assigned, pga_max_men represents the maximum value of the PGA.

Sql> select Pid,pga_used_mem,pga_alloc_mem,pga_max_mem from v$process;

--View Background process:

SELECT * from v$bgprocess WHERE paddr <> ' 00 ';

--View all the table spaces;

Sql> Select Tablespace_name from Dba_data_files order by Tablespace_name;

--View the name and size of the table space:
Sql> Select T.tablespace_name, round (SUM (bytes/(1024*1024)), 0) ts_size
From Dba_tablespaces T, dba_data_files d where t.tablespace_name = D.tablespace_name
Group BY T.tablespace_name;

--The syntax for creating table spaces is:
CREATE tablespace Tablespacename
datafile ' filename ' [SIZE integer [k| M]]
[Autoextend [off| On]];
--Create a tablespace with multiple data files:
sql> Create Tablespace SALES
DataFile ' d:/sales/sales_data01.dbf ' size 10m autoextend on next 10m maxsize 100m,
' d:/sales/sales_data02.dbf ' size 10m autoextend on next 10m maxsize
Unlimited, ' d:/sales/sales_data03.dbf ' size 10m;

--View the size of the table space;

Sql> SELECT tablespace_name,sum (BYTES)/1024/1024 MB from Dba_free_space GROUP by Tablespace_name;

--View the path of the data file stored in the tablespace:

Sql> SELECT Tablespace_name, bytes/1024/1024 file_size_mb, file_name from Dba_data_files;


To delete a table space:
Sql> drop tablespace worktbs including contents;
Table space is discarded.

Querying the tablespace again will find that there is no worktbs table space.
What happens if we delete the tablespace again?
To delete a table space that does not exist:
Sql> drop tablespace worktbs including contents;
Drop tablespace Worktbs including contents
*
ERROR on line 1th:
ORA-00959: tablespace ' Worktbs ' does not exist
o Add data files for table spaces
Sql> alter tablespace sales add datafile ' d:/oracle/oradata/test/testtablespace/
sales_data04.dbf ' size 10m autoextend on next 10m maxsize 100m,
' c:/oracle/oradata/test/testtablespace/sales_data05.dbf ' size 10m autoextend on next 10m maxsize Unlimited, ' c:/oracle/ oradata/test/testtablespace/sales_data06.dbf ' size 10m;

Table space has changed.
o Modify the size of the data file
o When the free space in the data file is not exhausted, the data file is allowed to compress and an error occurs when the size of the compressed space exceeds the unused space size.
sql> ALTER DATABASE datafile ' C:/ORACLE/ORADATA/TEST/TESTTABLESPACE/SALES_DATA04.DBF ' resize 30m;

The database has changed.
O Close Auto-extended properties of tablespace data files
o ALTER DATABASE
o datafile ' c:/sales_data04.dbf ',
O ' c:/sales_data05.dbf ',
O ' c:/sales_data06.dbf '
o autoextend off;
o Open the Auto-extended properties of a tablespace data file
o ALTER DATABASE
o datafile ' c:/sales_data04.dbf ',
O ' c:/sales_data05.dbf ',
O ' c:/sales_data06.dbf '
o autoextend on;

o Modify Table Space properties (offline)
o alter tablespace sales offline;
o Modify Table Space properties (online)
o alter tablespace sales online;
o Modify Table Space properties (read-only)
o alter tablespace sales read only;
o Modify Table Space properties (read and write)
o alter tablespace sales read write;
o The following system table space must not be set to offline or Read only
o System, Temp,undo, Undotbs

o Move table space data files
o 1. Take the tablespace offline (the system table space must not be taken offline, so it must not be moved)
o 2. Modify the name of the data file
o 3. Execute the ALTER tablespace rename datafile command
o 4. Bring table spaces Online
O First step: Alter TABLESPACE sales offline;
O Step two: Physically move the data file to the destination (which can be a table space
o Partial data file, can modify the name of the data file)
O Step three: Logical move, modify the contents of the control file

o Alter tablespace sales rename
o datafile ' c:/sales01.dbf ' to ' d:/sales02.dbf '
O--can have multiple data files, but the source file
O to the left of to, the destination file to the right, the file name separated by commas.
O Fourth step: Bring the tablespace online
o alter tablespace sales online;
o Fifth step: Query Dba_data_files Confirmation

o Create user, specify default tablespace, disk quota
o Create user rose identified by rose default
o tablespace sales quota 10m on sales;
o Authorization to the user
o Grant connect,resource,dba to Rose;
O User Login
O Connect rose/rose
o Create a table
o CREATE TABLE emp (EID number)
o The table is placed by default in the table space sales
o Query the user's storage limit Dba_ts_quotas
o Delete Table space (if the table space is empty)
o Drop tablespace sales;
o Delete the data file (not empty) while deleting the tablespace
o Drop tablespace sales including contents and datafiles;
o Specify the storage location of the table while creating the table
o CREATE table mytab (tid int) tablespace sales;

o Delete Table space (if the table space is empty)
o Drop tablespace sales;
o Delete the data file (not empty) while deleting the tablespace
o Drop tablespace sales including contents and datafiles;
o Specify the storage location of the table while creating the table
o CREATE table mytab (tid int) tablespace sales;


o View the size of each table occupied by the current user:
o Select Segment_name,sum (bytes)/1024/1024 from User_extents GROUP by segment_name
o See the size of each tablespace footprint:
o Select Tablespace_name,sum (bytes)/1024/1024 from Dba_segments GROUP by Tablespace_name


Access to Oracle database only with a legitimate user account
Oracle has several default database users
Scott/tiger

Create a user named Martin with a password of martinpwd
CREATE USER MARTIN identified by martinpwd
DEFAULT tablespace USERS
Temporary tablespace TEMP;

The GRANT command can be used to assign permissions or roles to the user;
The Connect role allows users to connect to the database and create database objects.
GRANT CONNECT to MARTIN;

The resource role allows users to use storage space in the database.
GRANT RESOURCE to MARTIN;

This system permission allows the user to create a sequence in the current mode, which is included in the Connect role.
GRANT CREATE SEQUENCE to MARTIN;

GRANT CREATE SESSION to MARTIN;

GRANT CREATE TABLE to MARTIN;

GRANT CREATE VIEW to MARTIN;

GRANT CREATE SEQUENCE to MARTIN;



Grant the user MARTIN the right to manipulate the EMP Table object:
Allow users to query the records of TEST tables
GRANT SELECT on EMP to MARTIN;

Allow users to update records in the TEST table
GRANT UPDATE on EMP to MARTIN;

Allow users to insert, delete, update, and query records in the TEST table
GRANT all on EMP to MARTIN;


The Alter USER command can be used to change the password:
Change the password for the MARTIN user:
ALTER USER MARTIN identified by Martinpass;
The drop user command is used to delete users:
Delete MARTIN user mode:
DROP USER MARTIN CASCADE;


Alter session set nls_date_format= ' YYYY-MM-DD ';



1 Start Database creation:

----------------created with tools (step omitted)

2 Landing a new database
Run/sqlplus SYS/SYS@ACCP
(At this point if you log in to the old database using Sqlplus sys/[email protected])
2 Creating a tablespace: Sales (used to hold data from the ACCP database)
Create tablespace testtbs datafile ' d:/sales/sales001.dbf ' size 10m autoextend on next 10m maxsize 30m, ' d:/sales/sales002. DBF ' size 10m autoextend on next 10m maxsize 30m
3 Create user ACCP and grant permissions
Grant connect to ACCP;
Grant resource to ACCP;
4 Login to ACCP database as ACCP user
CREATE TABLE person (PID number (5), PName varchar2 ()) tablespace Testtbs;
INSERT into person values (1001, ' TOM ');
Commit
Select Table_name,tablespace_name from User_tables;
select * from person;

Oracle Common Queries