Common Oracle DML

-- View the Oracle Instance name, host name, version select instance_name, host_name, version from V $ instance; -- view the details and digits of the Oracle version, other component Information Select * from V $ version -- view the database server Character Set, source props $ select * From nls_database_parameters; select userenv ('language') from dual; -- view the client character set, source v $ parameterselect * From nls_instance_parameters -- session Character Set select * From nls_session_parameters -- View database name select name from V $ database -- View tablespace used in the database select tablespace_name from dba_tablespaces; -- View table space user Distribution Information Select tablespace_name, owner, sum (bytes)/1024/1024 from dba_segments group by tablespace_name, owner; -- View table space size not used select tablespace_name, sum (bytes) /1024/1024 from dba_free_space group by tablespace_name; -- View All tablespace sizes select tablespace_name, sum (bytes)/1024/1024 from dba_data_files group by tablespace_name; -- view all usernames, default tablespace and temporary space select username, default_tablespace, temporary_tablespace from dba_users; -- View All enabled usernames, default tablespace and temporary space select username, default_tablespace, temporary_tablespace from dba_users where account_status = 'open'; -- Obtain the DDL statement select dbms_metadata.get_ddl ('table', U. table_name) from user_tables U; -- view the partition column information of all the partition tables of the current user select * From user_part_key_columns; -- query the Partition Table Name, owner (based on the test user) Select table_name, owner from all_part_tables where owner = 'test'; -- view the user of the partition table, partition table name, table partition name, and table space where the table partition is located (based on the partition table name Test2) Select table_owner, table_name, partition_name, tablespace_name from all_tab_partitions where table_name = 'test2'; -- view the data type of each field in the User table (Test2 table under the specified test user) Select column_name, data_type from all_tab_cols where table_name = 'test2' and owner = 'test'; -- query the table index select T. *, I. index_type from user_ind_columns T, user_indexes I where T. index_name = I. index_name and T. table_name = 'test'; -- query the table's primary key select Cu. * From user_cons_columns Cu, user_constraints au where Cu. constraint_name = au. constraint_name and AU. constraint_type = 'p' and Cu. table_name = 'test'; -- view the log mode select log_mode from V $ database; -- view the minimum supplemental logging status of the database select supplemental_log_data_min from V $ database; -- view the status of the current database force logging select force_logging from V $ database;