Common ORACLE commands and oracle commands

Common ORACLE commands and oracle commands

1. How can I view all tables in the database?

Select * from sys. user_tables;

2. How to connect to a database

Sqlplus/nolog; conn sys/oracle as sysdba;

3. How to start a database

Startup;

4. view the ORACLE instance Structure

Desc V $ instance;

5. view User's default and temporary tablespace Information

Select distinct username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username in ('system', 'productdb', 'process _ user', 'dictionary ', 'observ', 'forecast ')

6. Create a user

Create user OBSERVE identified by 123456; (default tablespace USERS, temporary tablespace TEMP)

7. View permissions of the current user role

Desc role_sys_privs;

8. View All ORACLE roles

Select ROLE from dba_roles order by ROLE;

9. View roles of all users

Select distinct GRANTEE, GRANTED_ROLE from dba_role_privs order by GRANTEE;

10. Bind a role to a user

Grant DBA to KFSYS;

Common commands for oracle databases

1. su-oracle is not required. It is suitable for use without a DBA password. You do not need a password to enter the sqlplus interface.
2. sqlplus/nolog, sqlplus system/manager, or./sqlplus system/manager @ ora9i;
3. SQL> connect/as sysdba; (as sysoper) or
Connect internal/oracle as sysdba; (scott/tiger)
Conn sys/change_on_install as sysdba;
4. SQL> startup; start the database instance
5. view all current databases: select * from v $ database;
Select name from v $ database;
Desc v $ databases; view database structure Fields
7. How to check which users have SYSDBA and SYSOPER permissions:
SQL> select * from V _ $ PWFILE_USERS;
Show user; view the current database connection user
8. Enter the test database: database test;
9. View All database instances: select * from v $ instance;
For example, ora9i
10. view all data tables of the current database:
SQL> select TABLE_NAME from all_tables;
Select * from all_tables;
SQL> select table_name from all_tables where table_name like 'U ';
TABLE_NAME --------------- default_auditing_options
11. view the table structure: desc all_tables;
12. display the structure of all fields of CQI. T_BBS_XUSER:
Desc CQI. T_BBS_XUSER;
13. Obtain records in the CQI. T_BBS_XUSER table:
Select * from CQI. T_BBS_XUSER;
14. Add database users: (test11/test)
Create user test11 identified by test default tablespace users Temporary TABLESPACE Temp;
15. user authorization:
Grant connect, resource, dba to test11;
Grant sysdba to test11;
Commit;
16. change the password of the Database User: (change the password of sys and system to test .)
Alter user sys indentified by test;
Alter user system indentified by test;
 
Common oracle commands

Baike.baidu.com/view/1239908.htm

SQL Plus commands are almost all on the above page.

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Sqldoc |
| Test |
+ -------------------- +
4 rows in set (0.00 sec)

An Oracle instance is a database, so there is no corresponding show databases statement

Mysql> show tables
->;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| Sale_report |
| Test_dysql |
| Union_tab_1 |
| Union_tab_2 |
| V_sale_report |
+ ---------------- +
5 rows in set (0.00 sec)

Oracle uses the following statement to implement

SQL> SELECT
2 table_name
3 FROM
4 all_tables
5 WHERE
6 ROWNUM <10;

TABLE_NAME
------------------------------------------------------------
ICOL $
CON $
UNDO $
PROXY_ROLE_DATA $
FILE $
UET $
IND $
SEG $
COL $

9 rows selected.

Mysql> desc sale_report;
+ ------------ + --------------- + ------ + ----- + --------- + ------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------ + --------------- + ------ + ----- + --------- + ------- +
| SALE_DATE | datetime | NO | NULL |
| SALE_ITEM | varchar (2) | NO | NULL |
| SALE_MONEY | decimal (10, 2) | NO | NULL |
+ ------------ + --------------- + ------ + ----- + --------- + ------- +
3 rows in set (0.00 sec)

Oracle

SQL> desc all_tab_columns
Name ...... remaining full text>