Shulou(Shulou.com)06/02 Report--

Basic operation of oracle

Desc all_tables;-View the table structure

Select from all_tables;-- views all the tables in the current database

Select table_name from user_tables; looks at the table of currently logged-in users:

Select from dba_users; to see which users there are.

Modify password expiration policy

1. View the proifle where the user stores the password policy:

SELECT USERNAME,PROFILE FROM DBA_USERS

two。 View the number of days the current password policy expires

SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME'

3. Modify password expiration policy

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

4. Log in to dba or the authorized user changes the password

DBA changes:

Alter user test IDENTIFIED BY 123456

An ordinary user changes the user password:

ALTER USER test IDENTIFIED BY new password replace old password

Dba user changes password:

ALTER USER test IDENTIFIED BY 123456

An ordinary user changes the user password:

ALTER USER test IDENTIFIED BY new password replace old password

Log in to the DBA user

Sqlplus system/oracle as sysdba

Modify the maximum number of connections and sessions

Col sid for 9999

Col serial# for 99999

Col username for a10

Col program for a20

Col machine for a20

1. Query the number of connections to the current process in the database:

Select count () from von processer;

two。 View the number of connections in the current session of the database:

Select count () from v$session

3. View the number of concurrent connections to the database:

Select sid,serial#,username,program,machine,status from v$session where status='ACTIVE'

4. View the session status of the current database establishment:

Select sid,serial#,username,program,machine,status from v$session

5. Modify the maximum number of connections allowed in the database:

Alter system set processes = 600scope = spfile

6. Modify the maximum number of sessions allowed in the database:

When you modify the maximum number of connections, the maximum number of sessions is automatically modified

View the number of connections

Show parameter processes

View number of session

Show parameter session

Check the default number of connections processes is 150

SQL > show parameter process

NAME TYPE VALUE

Aq_tm_processes integer 0

Cell_offload_processing boolean TRUE

Db_writer_processes integer 1

Gcs_server_processes integer 0

Global_txn_processes integer 1

Job_queue_processes integer 0

Log_archive_max_processes integer 4

Processes integer 150

Check the default number of sessions sessions is 247

SQL > show parametere session

SP2-0158: unknown SHOW option "parametere"

SP2-0158: unknown SHOW option "session"

SQL > show parameter session

NAME TYPE VALUE

Java_max_sessionspace_size integer 0

Java_soft_sessionspace_limit integer 0

License_max_sessions integer 0

License_sessions_warning integer 0

Session_cached_cursors integer 50

Session_max_open_files integer 10

Sessions integer 247

Shared_server_sessions integer

Modify the number of connections to 600, and the maximum number of sessions will automatically be changed to 922

Public notice of change:

Sessions=processes*1.5+22

SQL > alter system set processes = 600scope = spfile

System altered.

No change in the number of database sessions and connections without restarting

SQL > show parameter session

NAME TYPE VALUE

Java_max_sessionspace_size integer 0

Java_soft_sessionspace_limit integer 0

License_max_sessions integer 0

License_sessions_warning integer 0

Session_cached_cursors integer 50

Session_max_open_files integer 10

Sessions integer 247

Shared_server_sessions integer

SQL > show parameter process

NAME TYPE VALUE

Aq_tm_processes integer 0

Cell_offload_processing boolean TRUE

Db_writer_processes integer 1

Gcs_server_processes integer 0

Global_txn_processes integer 1

Job_queue_processes integer 0

Log_archive_max_processes integer 4

Processes integer 150

Restart the database, nginx-like reload

SQL > startup force

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2217464 bytes

Variable Size 562039304 bytes

Database Buffers 218103808 bytes

Redo Buffers 2637824 bytes

Database mounted.

Database opened.

Number of sessions and connections changed

SQL > show parameter process

NAME TYPE VALUE

Aq_tm_processes integer 0

Cell_offload_processing boolean TRUE

Db_writer_processes integer 1

Gcs_server_processes integer 0

Global_txn_processes integer 1

Job_queue_processes integer 0

Log_archive_max_processes integer 4

Processes integer 600

SQL > show parameter session

NAME TYPE VALUE

Java_max_sessionspace_size integer 0

Java_soft_sessionspace_limit integer 0

License_max_sessions integer 0

License_sessions_warning integer 0

Session_cached_cursors integer 50

Session_max_open_files integer 10

Sessions integer 922

Shared_server_sessions integer

SQL >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.