Today I want to query all the table names in the Oracle database or all the tables in a user. I haven't thought about it for a long time. I still found the answer online.
Select table_name from all_tables; // All
Select table_name from user_all_tables; // all tables of the user
The following is a post on the SQL statement.
Select * From user_objects; // query all tables
Select * From dba_tables; // query all tables
Select * From all_tables; // query all tables
Select * From user_users // find a user
Select * From all_users // query all users
Select * From dba_users // query all users
Select name, dbid from V $ database; // query the database name and its ID
Select * From SYS. user_tab_columns; // query the table name and display the column name.
Describe table name // query the table structure
2: query the database parameter show parameter dB; 3: query the database instance name select instance_name from V $ instance; 4: After the database domain name database is installed, if you want to know whether the database is running with a domain name and the database domain name, you can use select value from V $ parameter where name = 'db _ Domain 'show parameter domain5: database Service name if the database has a domain name, the Database Service name is the global database name. If the database does not have a defined domain name, the Database Service name is the same as the database name show parameter SERVICE_NAME 6: show the current user show user7: log on to sqlplus "/As sysdba" 8: Select sysdate from dual;
9: query the database dictionary v $ nls_parameter. Select * from V $ nls_parameters;
//*************
Basic Oracle statements (suitable for beginners) |
Oracle operation statement: 1. Create a table Create Table Name ( Column name 1 type, Column name 2 type ); 2. Modify class attributes Alter table table name modify (column name type ); 3. Add Columns Alter table table name Add (column name type ); 4. Add primary key constraints and non-empty Constraints Alter table table name Add constraint pK _ table name primary key (column name ); Alter table table name modify (column name not null ); 5. Delete the primary key constraint Alter table Table Name drop primary key; Alter table Table Name drop constraint pK _ table name; 6. Failure Constraint Alter table table name disable primary key; Alter table table name disable constraint pK _ table name; 7. Valid Constraints Alter table table name enable primary key; Alter table table name enable constraint pK _ table name; 8. Delete Columns Alter table Table Name drop column name; 9. Set a column to unavailable and delete it. Alter table table name set unused (column name ); Alter table Table Name drop unused columns; 10. Modify the table name Rename table name 1 to table name 2 Alter table name 1 Rename to table name 2; 11. Table Truncation Truncate table name; 12. truncate the table and retain row space Truncate table name resue storage; 13. view the table structure Desc table name; 14. delete a table Drop table name; 15. Insert record Example: insert into table name values (Content 1, content 2, content 3, content 4 ); 16. Insert rows in dialog mode with Parameters Example: insert into table name values (& Column name 1, & Column name 2 ); Insert into table name values (Content 1, content 2 ); 17. Insert several columns of records Insert into Table Name (column name 1, column name 2) values (Content 1, content 2 ); 18. Insert a null value for the column (its column cannot be not null) Insert into table name values (Content 1, null, null ); 19. Create a table (including primary key and foreign key settings) method 1 Create Table Name ( Column name 1 Type Constraint pK _ table name primary key, Column name 2 type not null, Column name 3 type Constraint FK _ Table Name Reference Table Name (column name ), Column name 3 type Constraint CK _ table name check (column name 3 in (''content 1 '', ''content 2'', ''content 3 '')) ); 20. query all rows Select * from table name; 21. query certain Columns Select column name 1, column name 2 from table name;22. Duplicate row Elimination Select distict column name from table name; 23. Where statement Query Select * from table name where condition order by column name; (Note: If the number type is queried automatically in ascending order, if you want to sort it in descending order, select * from table name where condition order by column name DESC ;) 24. Create a table. method 2 Create Table Name ( Column name 1 type primary key, Column name 2 type not null, Check (column name 3 in ('''','''','''')), Column name 4 type refernce table name (column name) ); 25. Modify the column = '? 'Data Update table name set (column = ?) Where column = '? '; 26. Delete rows Delete from table name where condition; 27. Transaction Processing -- Transaction Processing Update table name Set column name (date) = ''30-May-98'' Where condition; Savepoint mark1; Delete from table name where condition; Savepoint mark2; Rollback to savepoint mark1; Rollback; 28. Create user1 with Password Grant connect and resource permissions to users Connect role for Logon The resource role is used to create tables. Connect system/Manager Create user user1 identified by password; Grant connect, resource to password; 29. Data Control Language Connect Scott/Tiger 30. Grant the permission to query and Modify Table 1 to user1. Grant select, update on table 1 to user1; 31. Grant the permission to modify column 1 and column 2 in table 1 to user1. Grant Update (column 1, column 2) on table 1 to user1; 32. Grant the permission to query Table 1 to user user1 In addition, user1 users can grant this permission to other users (with grant option) Grant select on table 1 to user1 with grant option; 33. revoke the permission to query and Modify Table 1 from user user1 Revoke select, update on table 1 from user1; |