Common Oracle SQL plus commands and queries

1. Start and Stop TNS listening
LSNRCTL start
LSNRCTL stop2. start Oracle services
Net start oracleserviceorcl
Net stop oracleserviceorclsql> show all -- View All 68 system variable values
SQL> show user -- display the current connected user
SQL> show error -- Display Error
SQL> set heading off -- disable the output column title. The default value is on.
SQL> set feedback off -- disable counting feedback of the last row. The default value is "send back on" for 6 or more records"
SQL> set timing on -- the default value is off. It sets the query time, which can be used to estimate the SQL statement execution time and test the performance.
SQL> set sqlprompt "SQL>" -- sets the default prompt. The default value is "SQL>"
SQL> set linesize 1000 -- set the row width on the screen. The default value is 100.
SQL> set autocommit on -- sets whether to submit automatically. The default value is off.
SQL> set pause on -- the default value is off. When this parameter is set to suspend, the screen will be stopped. Wait for the Enter key to display the next page.
SQL> set arraysize 1 -- default value: 15
SQL> set long 1000 -- the default value is 80, which lists the definitions of all synonyms under the current user and can be used to test the true existence of synonyms.
Select 'desc' | tname from tab where tabtype = 'synonym'; queries the number of records of all tables under the current user
Select 'select' | tname | ''', count (*) from' | tname | ';' from tab where tabtype = 'table '; grant the select permission to all qualified tables to public.
Select 'Grant select on' | table_name | 'to public;' from user_tables where conditions; Delete objects under a user
Select 'drop' | tabtype | ''| tname from tab; Delete qualified users
Select 'drop user' | username | 'cascade; 'from all_users where user_id> 25. The following describes how to use common data dictionaries for oracle users by category.
1. User
View the default tablespace of 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 the system and table permissions of the current user
SQL> select * From user_sys_privs;
SQL> select * From user_tab_privs; 2. Table
View All tables under a user
SQL> select * From user_tables; View tables whose names contain log characters
SQL> select object_name, object_id from user_objects
Where instr (object_name, 'log')> 0; 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 the table in the Oracle memory Partition
SQL> select table_name, cache from user_tables where instr (cache, 'y')> 0; 3. Index
View the number and category of Indexes
SQL> select index_name, index_type, table_name from user_indexes order by table_name; view the indexed Field
SQL> select * From user_ind_columns where index_name = upper ('& index_name'); view the index size
SQL> select sum (bytes)/(1024*1024) as "size (m)" from user_segments
Where segment_name = upper ('& index_name'); 4. Serial number
View the serial number. last_number is the current value.
SQL> select * From user_sequences; 5. View
View view name
SQL> select view_name from user_views; view the SELECT statement for creating a view
SQL> set view_name, text_length from user_views;
SQL> set long 2000; Description: You can set the size of set long based on the text_length value of the view.
SQL> select text from user_views where view_name = upper ('& view_name'); 6. Synonym
View synonym name
SQL> select * From user_synonyms; 7. Constraints
View the constraints of 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 processes
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 the source code of the function and Process
SQL> select text from all_source where owner = user and name = upper ('& plsql_name'); 3. view the database SQL
1. view the table space name and size.
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; 2. view the name and size of the tablespace physical file.
Select tablespace_name, file_id, file_name,
Round (Bytes/(1024*1024), 0) total_space
From dba_data_files
Order by tablespace_name; 3. view the rollback segment name and size
Select segment_name, tablespace_name, R. status,
(Initial_extent/1024) initialextent, (next_extent/1024) nextextent,
Max_extents, V. curext curextent
From dba_rollback_segs R, V $ rollstat v
Where R. segment_id = V. USN (+)
Order by segment_name; 4. view the control file
Select name from V $ controlfile; 5. view log files
Select member from V $ logfile; 6. View table space usage
Select sum (bytes)/(1024*1024) as free_space, tablespace_name
From dba_free_space
Group by tablespace_name; select a. tablespace_name, A. bytes total, B. bytes used, C. bytes free,
(B. bytes * 100)/A. bytes "% used", (C. bytes * 100)/A. bytes "% free"
From SYS. sm $ ts_avail A, SYS. sm $ ts_used B, SYS. sm $ ts_free C
Where a. tablespace_name = B. tablespace_name and A. tablespace_name = C. tablespace_name; 7. view database objects
Select owner, object_type, status, count (*) Count # From all_objects group by owner, object_type, status; 8. view database version
Select version from product_component_version
Where substr (product,) = 'oracle '; 9. view the database creation date and archiving method.
Select created, log_mode, log_mode from V $ database; 1. The current connection is displayed. Select *
From v $ session
Where status = 'inactive'
And username is not null; 2. Force Close user connection a) Select username, Sid, serial #
From v $ session
Where status = 'inactive'
And username is not null;
                                                                                                                                                                         
B) alter system kill session 'sid, serial # '; -- Sid and serial # Are the results obtained in Step. 3. Select the m to N records from the returned results *
From (
Select a. *, rownum Rn
From (
Select *
From yourtable -- use your select statement here
)
Where rownum <= N
)
Where rn> = m;

4. recompile the package and package body)
This command can be used to view the compilation error of the package created in sqlplus. A) Compile package
Alter package your_package
Compile specification;
B) Compile the package body.
Alter package your_package
Compile body;
C) Check for errors
Show err; 5. Unlock the user alert User Username unlock;