Oracle-related knowledge points

Oracle database, where the instance name and database are one by one, the Oracle server can launch multiple instances, corresponding to multiple databases.

The database can enter an instance of the default Sid via Sqlplus/as SYSDBA.

View current instance Name: SELECT * from V$instance;

Switch to other instances, only export oracle_sid= sidname (instance name), and then Sqlplus/as SYSDBA enter.

In the case where the database already exists, there are table spaces that users and users can access, that is, the table structure file for the data store.

One, Oracle TNS Listener Related:
To view the listener status:
Lsnrctl status
Start:
Lsnrctl start
Shut down:
Lsnrctl stop

Modify the port of the listening file for the network client link:
Vi/u01/app/oracle/product/11.2.0/network/admin/listener.ora
Modify content (HOST = 10.116.4.63) (PORT = 1521)

LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.116.4.63) (PORT = 1521))
# (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
)
)

Adr_base_listener =/u01/app/oracle


To modify the SID mapping file for listener:
Vi/u01/app/oracle/product/11.2.0/network/admin/tnsnames.ora

Add SID is the instance name of JJCCBDB:
JJCCBDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.116.4.63) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = jjccbdb)
)
)




Ii. steps for Oracle to create/delete an instance library
1. Use the DBCA tool to build the library silently



Dbca-silent-createdatabase-templatename testdb-sid $dbname-gdbname $dbname-datafiledestination $dest-syspassword or Acle-systempassword Oracle \
-characterset $langu-redologfilesize 512-storagetype fs-emconfiguration none-memorypercentage 30-totalmemory $mem-A Utomaticmemorymanagement true





2. Using the DBCA tool to delete the library
Existing Instance name: Srpsdb
Dbca-silent-deletedatabase-sourcedb srpsdb-sid srpsdb-sysdbausername Sys-sysdbapassword Oracle

3. After creating an instance, set the tablespace and user access rights

Create temporary tablespace srpsdb_tmp tempfile '/u01/app/oracle/oradata/srpsdb/srpsdb_tmp.dbf ' size 10240m autoextend On next 1024m maxsize 10240m extent management local;
Create tablespace srpsdb logging datafile '/u01/app/oracle/oradata/srpsdb/srpsdb.dbf ' size 10240m autoextend on next 1024 M maxsize 10240m extent management local;




Create temporary tablespace srpsdb_tmp tempfile '/u01/app/oracle/oradata/jjccbdb/srpsdb_tmp.dbf ' size 10240m Autoextend on next 1024m maxsize 10240m extent management local;
Create tablespace srpsdb logging datafile '/u01/app/oracle/oradata/jjccbdb/srpsdb.dbf ' size 10240m autoextend on next 102 4m maxsize 10240m extent management local;

4. Access table space for empowered users
Create user C identified by 123456 default tablespace srpsdb temporary tablespace srpsdb_tmp;
Create user P identified by 123456 default tablespace srpsdb temporary tablespace srpsdb_tmp;
Create user M identified by 123456 default tablespace srpsdb temporary tablespace srpsdb_tmp;

5. Giving the User a role
Grant DBA to C;
Grant DBA to P;
Grant DBA to M;

--------------

Third, common Oracle View user tables and permissions related

User rights in Oracle are divided into system permissions and user table permissions:
To view the current user table-level permissions permissions:
SELECT * from User_tab_privs;
To view the system permissions that the current user has:
SELECT * from User_sys_privs;

To view the table under the current User:
SELECT * from User_tables;


To view a user's permissions:
SELECT * from Dba_sys_privs where grantee= ' M ';

View the role of the user, limiting the current query user to be DBA:
SELECT * from Dba_role_privs where grantee= ' P ';


To view the table space for the current user:
Select Username,default_tablespace from User_users;



Into the DBA role view:
1, Sqlplus/as SYSDBA
2, select Owner,table_name from Dba_tables where table_name= ' abm_account ';

Four, Oracle's JDBC link address: Jdbc:oracle:thin:@10.116.4.125:1521:srpsdb (instance name)

Oracle-related knowledge points