SID： instance_name, unique name of the INSTANCE
SID SERVICE_NAME
bob1 bob
bob2 bob
bob3 bob
bob4 bob
SERVICE_NAMES：Oracle recommends that the value of the service_name be the same as the ORACLE_SID. However, a service_name can point to more than one instance
http://www.dba-oracle.com/t_tns_service_name.htm
set linesize 1000to have a friendly appearance to look:
https://dolphinpg.net/program/oracle-sqlplus-sql-linesize-column/
sqlplus: sqlplus system@orcl
login orcl instance as system administrator
DROP TABLESPACE TABSP1 INCLUDING CONTENTS AND DATAFILES;
adding AND DATAFILES makse sure the datafiles also be deleted.
DROP TABLESPACE TABSP1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
CASCADE CONSTRAINTS makes sure the constraints also be deleted.
ALTER TABLESPACE TABSP1 ADD DATAFILE 'D:\ORCL\DBFILE2.DBF' SIZE 100M;
eg: alter tablespace tablespacename add datafile 'D:\xxx.DBF' size 10G autoextend on next 100M maxsize unlimited;
ALTER TABLESPACE…ADD DATAFILE to add more corresponding physical files to save it.
DROP USER SPOTFIRE_DB CASCADE;
delete the user named spotfire_db within all the tablespace under it.
Sqlplus /nolog: without log, which is safe to protect the password
conn Username/Password@Servicename
SQL> conn sys/ as sysdba;
conn sys/PW@instance_name as sysdba
login in using a user
SELECT * from sn_version; contains the version details for the product.
http://informatics.perkinelmer.com/Support/KnowledgeBase/details/Default?TechNote=3079
create user name identified by password;
create user named name with password
GRANT ALL PRIVILEGES to username;
grant all authority to a user
How to Grant All Privileges to a User in Oracle
CREATE TABLE CardInfo (
CardID nchar(6) NOT NULL,
CustomerID nchar(5),CONSTRAINT pk1 PRIMARY KEY(CardID)
);
let the CardID becomes the pk1 primary key
copy the table from another table
CREATE TABLE emp
AS SELECT * FROM test;
https://sql-oracle.com/?p=1694
INSERT INTO CardInfo (
CardID, CustomerID)
VALUES (
'AX10012', 'MASUU12'
);
CREATE TABLE TBL1 (NOTE VARCHAR2(10)) TABLESPACE TABSP;
means create a table named tbl1 with the column information and belonged to the tablespace named tabsp, and, under the schema, the username who having logined in the oracle.
COMMIT;
quit: is to quit database system
Two connection styles:
ODBC(Open Database Connectivity): through database connector
JDBC(Java Data Base Connectivity):through information link
https://blog.csdn.net/u010926964/article/details/46389867
Tnsnames.ora: it is the driver configuration file
select userenv('language') from dual;
check the language code used in database.
If you want to Unlock Accounts and Reset Passwords:
https://docs.oracle.com/cd/B19306_01/install.102/b25300/rev_precon_db.htm
sqlplus /nolog
CONNECT SYS/SYS_password AS SYSDBA
make sure to set SEC_CASE_SENSITIVE_LOGON = TRUE;
try UTF-8 when import data in oracle sql developer, then it won’t be 文字化け.
中文乱码解决：https://www.geek-share.com/detail/2765805380.html
DROP TABLE table1 CASCADE CONSTRAINTS;
delete the table named table1 in the database
delete the table1 totally:
DROP TABLE table1 PURGE;
SELECT * FROM RECYCLEBIN;
choose the recyclebin
PURGE RECYCLEBIN;
clean the recyclebin forever
SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘spfile’;
to check whether you use SPFILE to configure the initial parameters.
tnsnames.ora is in dbhome_1\network\admin
listener.ora is in dbhome_1\network\admin
SID is saved in memory, and database file(.dbf) is saved in ORCL folder
SID’s name can be checked from windows service, eg. OracleServiceORCL
database file name is written in parameter of DB_NAME in the initial spfile
The process of data refreshment:
1.INSERT/UPDATE/DELETE->Redo log Buffer, Database buffer cache,
2.COMMIT->Redo log file
3.while checkpoint happen(when redo log file refresh, create, or instance shutdown), database buffer cache->dbf file
ROWID resembles the key of the row in each datatable, and it cannot be changed mannualy but automatically allocated by system. It is a must to ask the rowid clearly, eg.
SELECT rowid,empno FROM emp;
To look for the version of Oracle:
SELECT * FROM V$VERSION;
sqlplus / as sysdba(short style for the next one)
sqlplus user1/pass1 as sysdba(used the login information)
shutdown the database: sqlplus->shutdown immediate
startup the database: sqlplus->startup
NOMOUNT・・・read the initial parameter->STARTUP NOMOUNT
MOUNT・・・read the redo log file, path, etc.->STARTUP MOUNT
OPEN・・・read the .dbf file, open state->STARTUP
sqlplus username/password@orcl
run by quote from file:
@d:\test\emp.sql;
@d:\test\emp_para.sql 'A009', 'XXX', 'TO1';
start with the command:
sqlplus username/password@orcl @d:\test\emp.sql;
CREATE OR REPLACE DIRECTORY dp_dir AS 'd:\dump';
means let the dp_dir to ‘d:\dump’
drop directory dp_dir;
means delete directory dp_dir
GRANT READ ON DIRECTORY dp_dir TO test;
means give the test the authority to read the dp_dir
GRANT WRITE ON DIRECTORY dp_dir TO test;
means give the test the authority to write the dp_dir
The command below is used in command prompt, which is to copy all the database file content to somewhere.
expdp username/password@orcl directory=dp_dir dumpfile=dmp_db.dmp log=exp.log FULL=Y
to select a certain time when export, use flashback_time:
FLASHBACK_TIME=”TO_TIMESTAMP(’12-02-2005 14:35:00′, ‘DD-MM-YYYY HH24:MI:SS’)”
systimestamp gets the current time: https://www.techonthenet.com/oracle/functions/systimestamp.php
remap_schema maps the old schema to new shcema:
https://blog.csdn.net/e_wsq/article/details/78374020
eg:
expdp system/password@servicename directory=data_pump_dir dumpfile=xxx.dmp schemas=xxx flashback_time=systimestamp logfile=xxx.logimpdp system/password@servicename directory=data_pump_dir dumpfile=xxx.dmp remap_schema=(oldschema1:newschema1,oldschema2:newschema2) logfile=xxx.logEXPDP system/password@servicename DIRECTORY=xxx DUMPFILE=xxx.dmp LOGFILE=xxx.log SCHEMAS=xxxIMPDP system/password@servicename DIRECTORY=xxx DUMPFILE=xxx.dmp LOGFILE=xxx.log SCHEMAS=xxx
use TABLESPACES=tabsp1 to instead of FULL=Y, action on only the tablespaces
use SCHEMAS=test to instead of FULL=Y, action on only the schemas
use TABLES=test.emp to instead of FULL=Y, action on only the tables
option: https://sql-oracle.com/?p=183
CONTENT=data_only(other wise METADATA_ONLY, ALL)
TABLE_EXISTS_ACTION = REPLACE(other wise SKIP, APPEND, TRUNCATE)
Phisically Backup file:
init.ora
.ctl(control file)
.dbf
.log（redo log)
SCN(system change number):
check the SCN in .dbf and .ctl is the same to judge whether the content is the same
To recover the SCN and keep the SCN the same from the redo log file:
recovery automatic database;
then open it as usual state:
alter database open;
to show all the directory objects:
SELECT * FROM DBA_DIRECTORIES;
alert_orcl.log:(app\oracle\diag\rdbms\orcl\orcl\trace)
used to record the alert, start on or off log, log switch
listener.log:(app\oracle\diag\tnslsnr\LAPTOP-DPQM9JLI\listener\trace)
access to listener’s information
To check if archive log exists:
SELECT LOG_MODE FROM V$DATABASE;
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE 'log_archive%';
Procedure to use archivelog:
1.SHUTDOWN IMMEDIATE;
2.STARTUP MOUNT;
3.ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=D:\ORACLE\ORADATA\ARCHIVE’;
4.ALTER DATABASE ARCHIVELOG;
5.ALTER DATABASE OPEN;
RMAN‘s usage to delete the archivelog automatically: https://sql-oracle.com/?p=1555
TEMPORARY TABLE, which is used by group, sort, or calculation in caches
CREATE TABLESPACE TABSP DATAFILE 'D:\oradata\orcl\TABSP1.dbf' SIZE 300M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE TEMPORARY TABLESPACE TMPSP TEMPFILE ‘TempFile1.dbf‘ SIZE 100M;
The above is to create tablespace named tabsp, showing the file path, size, which is able to extend（For tempfile, add TEMPORARY and use TEMPFILE instead of DATAFILE in the command）
CREATE TABLESPACE TABSP DATAFILE
'D:\oradata\orcl\TABSP1.dbf' SIZE 300M AUTOEXTEND OFF;
This makes the size maximum be 300M
CREATE TABLESPACE TABSP DATAFILE
'D:\oradata\orcl\TABSP1.dbf' SIZE 300M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
'D:\oradata\orcl\TABSP2.dbf' SIZE 300M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
It can be even put into 2 more files.
ALTER DATABASE DATAFILE 'D:\oradata\TABSP1.dbf' RESIZE 500M;
change the size of the datafile for a tablespace, only available to shrink the size when there is no data inside.
Change it to automatically extend the datafile:
ALTER DATABASE DATAFILE 'D:\APP\TEST\ORADATA\ORCL\TABSP1.DBF'
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED;
to check the autoextensible state:
SELECT TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE
FROM DBA_DATA_FILES
ORDER BY 1
to check the table datafile volume, etc:https://sql-oracle.com/?p=375
but better to use sqldeveloper as the video below(make sure to use the account having the privilege)
ALTER TABLE TBL1 MOVE TABLESPACE TABSP1;
that means to move table tbl1 to another tablespace named tabsp1
ALTER TABLE TEST1 MOVE;
make the table be quick.https://sql-oracle.com/?p=360
ALTER INDEX index1 REBUILD TABLESPACE idxsp2;
change the index’s tablespace(need rebuild the index)https://sql-oracle.com/?p=279
ALTER INDEX index1 REBUILD;
as the corresponding index of that column needs to rebuild
to check the index is valid or not:
SELECT INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS FROM USER_INDEXES;
to check whether the dbf file is broken or not in command prompt(not used for temp file):
DBV FILE=D:\ORACL\ORADATA\TBSP1.DBF
schema resembles the container which is paired with a special user in Oracle, schema contains the table, view, etc.
CREATE USER SATOU IDENTIFIED BY PASS;
The above is to create user, and the schema is meanwhile created by Oracle.(Living together, dying together)
SELECT * FROM schema_name.table_name;
cannot omit, if login in another username, which is different from schema_name’s username
SELECT SYSDATE FROM DUAL;
SELECT ROUND(10.5) FROM DUAL;
dual is under sys schema, having some convenient use
change the table name:
ALTER TABLE table1 RENAME TO table2;
change the empname’s number of digits:
ALTER TABLE table1 MODIFY (empname VARCHAR2(20));
SELECT * FROM ALL_TAB_COLUMNS
WHERE OWNER = ‘XX’;https://sql-oracle.com/?p=358
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE
FROM ALL_TAB_COLUMNS
WHERE OWNER = ‘username’ AND TABLE_NAME = ‘tablename’;
username and tablename is Case Sensitive!!!
CREATE USER username
IDENTIFIED BY password
DEFAULT TABLESPACE TABSP1
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON TABSP1
QUOTA UNLIMITED ON TABSP2;
If use 2 tabspace, using quota command above to show ithttps://sql-oracle.com/?p=193
ALTER TABLE TEST1 ADD(ADDITION NUMBER(5,0), ADDITION2 VARCHAR2(100));
add some columns into the old table.
ALTER TABLE TEST1 RENAME COLUMN addition TO addition1;
change the column name.
ALTER TABLE TEST1 MODIFY (ADDITION1 VARCHAR2(10));
change the column’s data type
ALTER TABLE TEST1 DROP (ADDITION1) CASCADE CONSTRAINTS;
delete a column in the table
CREATE INDEX INDEX1 On TEST1(ADDITION2) TABLESPACE tablespacename;
create an index on a column
DROP INDEX index1;
delete a index(if you want to change it, you can only delete and rebuild it)
ALTER TABLE test1 MODIFY col1 NULL;???
change the column to be nullable
CREATE OR REPLACE VIEW view’sname AS
SELECT…
create a view…
DROP VIEW viewname CASCADE CONSTRAINTS;
delete a view
ALTER TABLE table1 ADD CONSTRAINT pk1 PRIMARY KEY (empno,depno);
add empno and depno as the primary key of table1
ALTER TABLE table1 DROP CONSTRAINT pk1;
delete the primary key(if you want to change it, you can only delete and rebuild it)
change user’s password:
ALTER USER satou IDENTIFIED BY pass1;
check the locked user:
SELECT USERNAME,ACCOUNT_STATUS,LOCK_DATE
FROM DBA_USERS
WHERE ACCOUNT_STATUS <> ‘OPEN’;
unlock the locked user:
ALTER USER satou ACCOUNT UNLOCK;
about making profile: https://sql-oracle.com/?p=387
check the profile condition on users:
SELECT USERNAME,PROFILE FROM DBA_USERS;
apply profile on a user.
ALTER USER satou PROFILE profile1;
creat a role:
CREATE ROLE ROLE1;
delete a role:
DROP ROLE role1;
define the role:
GRANT INSERT,UPDATE,DELETE ON TABLE_A TO ROLE1;
give the user a role:
GRANT role1 TO user1;
withdraw a role form the user:
REVOKE role1 FROM user1;
To delete the role content:
1.REVOKE create table FROM role1;
2.REVOKE create table,drop table FROM role1,role2;
3.REVOKE insert ON table1 FROM role1;
4.REVOKE all ON table1 FROM role1;
to check all the defined roles(needs the privilege):
SELECT * FROM DBA_ROLE_PRIVS;
SELECT USERNAME,STATUS,OSUSER,MACHINE,LOGON_TIME
FROM V$SESSION
WHERE USERNAME IS NOT NULL;
The above is to get the information of current session.https://sql-oracle.com/?p=266
to check the session and the process maximum:
SELECT * FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME = ‘processes’
OR RESOURCE_NAME = ‘sessions’;
to check all the username can be seen by current logined user:
SELECT * FROM ALL_USERS
ORDER BY USERNAME;
more user information in detail:
SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE
FROM DBA_USERS
ORDER BY USERNAME;
give the privilege to create table:
GRANT CREATE TABLE TO SATOU;
give the privilege to insert on table:
GRANT INSERT ON TABLE_A TO SATOU;
check the sysdba role: SELECT * FROM V$PWFILE_USERS;
check the dba role: sysdba>dba role
SELECT * FROM DBA_ROLE_PRIVS
WHERE GRANTED_ROLE ='DBA';
If using spfile mode(only spfile needs restart) to change the processes and sessions maximum:https://sql-oracle.com/?p=395
ALTER SYSTEM SET PROCESSES = 1000 SCOPE=SPFILE;
ALTER SYSTEM SET SESSIONS = 1000 SCOPE=SPFILE;
size change of SGA（System Global Area, needs restart）:
ALTER SYSTEM SET SGA_MAX_SIZE = 1G SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET = 1G SCOPE=SPFILE;
to confirm SGA:
show parameter sga_;
size change of PGA（Program Global Area, needs restart）:
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 500M SCOPE=SPFILE;
to confirm PGA:
SHOW PARAMETER TARGET;
After Oracle 11gR2, the memory distribution is automated, without setting sga_target and pga_aggregate_target seperately using the command as follows, then reboot the oracle service:eg:
create spfile=’C:\app\Administrator\virtual\product\12.2.0\dbhome_1\database\SPFILEORCL.ORA’ from pfile=’C:\app\Administrator\virtual\admin\orcl\pfile\init.ora’;
alter system set sga_target=0 scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;
alter system set memory_target=16G scope=spfile;
alter system set memory_max_target=24G scope=spfile;
make synonym for table:
CREATE SYNONYM othername FOR tablename;
make public synonym for table(available for all users):
CREATE PUBLIC SYNONYM othername FOR tablename;
to check the synonym in the database:https://sql-oracle.com/?p=211
SELECT OWNER, SYNONYM_NAME ,TABLE_NAME FROM ALL_SYNONYMS;
database link(use to connect the database on another server):
CREATE DATABASE LINK databaselinkname
CONNECT TO username
IDENTIFIED BY password
USING 'database_name';
‘database_name’ is written in tnsnames.ora
to use the data by database link:
SELECT * FROM username.tablename@databaselinkname;
check all the databaselink:https://sql-oracle.com/?p=213
SELECT * FROM DBA_DB_LINKS;(more in detail)
SELECT * FROM ALL_DB_LINKS;
to delete the database link:
DROP DATABASE LINK databaselink_name;
DROP PUBLIC DATABASE LINK databaselink_name;
trigger: do something before/after/instead of, seems like to build a link mechanismhttps://sql-oracle.com/?p=147
CREATE OR REPLACE TRIGGER TRI1
BEFORE/AFTER/INSTEAD OF
INSERT OR UPDATE
ON EMP
FOR EACH ROW
BEGIN
PRO_A
END;
for each row means do the trigger in every row action
to check the trigger information:https://sql-oracle.com/?p=1543
SELECT TRIGGER_NAME, TABLE_NAME, STATUS FROM USER_TRIGGERS;
SELECT TRIGGER_BODY FROM ALL_TRIGGERS;
change the trigger state:
ALTER TRIGGER triggername DISABLE;
ALTER TRIGGER triggername ENABLE;
change the trigger states which involve in the table:
ALTER TABLE tablename DISABLE ALL TRIGGERS;
ALTER TABLE tablename ENABLE ALL TRIGGERS;
to give the privilege involves job:
GRANT CREATE JOB TO user1;
GRANT MANAGE SCHEDULER TO user1;
create automated job execution:https://sql-oracle.com/?p=164
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name => ‘JOB_PROC_A’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘USER1.PROC_A’,
start_date => TO_DATE(‘2018/08/01 00:00:00′,’yyyy/mm/dd hh24:mi:ss’),
repeat_interval => ‘FREQ=HOURLY’,
enabled => TRUE
);
END;
oracle’s procedure:https://sql-oracle.com/?p=1083
CREATE OR REPLACE PROCEDURE proA
IS
nCnt NUMBER;
rEmp emp%ROWTYPE;
tEmpNo emp.empno%TYPE;
BEGIN
nCnt := 1;
rEmp.empno := ‘A0101’;
tEmpNo := ‘A0101’;
END;
if there is prameter:
CREATE OR REPLACE PROCEDURE proA(nEno IN NUMBER)
IS
nCnt NUMBER;
BEGIN
UPDATE emp
SET salary = salary * 2
WHERE empno = nEno;
END;
oracle’s function:
CREATE OR REPLACE FUNCTION funA RETURN NUMBER
IS
nCnt NUMBER;
BEGIN
nCnt := 1;
RETURN nCnt;
END;
or if there is parameter:
CREATE OR REPLACE FUNCTION funA(nEno IN NUMBER) RETURN NUMBER
IS
nSal NUMBER;
BEGIN
SELECT salary INTO nSal FROM emp
WHERE empno = nEno;
RETURN nSal;
END;
DROP PROCEDURE pro1;
DROP FUNCTION fun1;
DROP PACKAGE pac1;
the usage of loop:
(1.loop exit when):
CREATE OR REPLACE PROCEDURE proA
IS
nCnt NUMBER;
BEGIN
LOOP
nCnt := nCnt + 1;
EXIT WHEN nCnt > 10;
END LOOP;
END;
(2.while loop):
CREATE OR REPLACE PROCEDURE proA
IS
nCnt NUMBER;
BEGIN
WHILE (nCnt <= 10);
LOOP
nCnt := nCnt + 1;
END LOOP;
END;
(3.for loop):
CREATE OR REPLACE PROCEDURE proA
IS
nCnt NUMBER;
BEGIN
FOR nCnt IN 1 .. 10
LOOP
nCnt := nCnt + 1;
END LOOP;
END;
the usage of if:
1.
IF nEmpNo > 100 THEN
nCnt = 1;
END IF;
2.
IF nEmpNo > 100 THEN
nCnt = 1;
ELSIF nEmpNo < 10 THEN
nCnt = 0;
END IF;
3.
IF nEmpNo > 100 THEN
nCnt = 1;
ELSIF nEmpNo < 10 THEN
nCnt = -1;
ELSE
nCnt = 0;
END IF;
the usage of case:
CASE
WHEN nEmpNo > 100 THEN
nCnt = 1;
WHEN nEmpNo < 10 THEN
nCnt = -1;
ELSE
nCnt = 0;
END CASE;
EXECUTE IMMEDIATE（PL/SQL）OracleのEXECUTE IMMEDIATE 動的SQL【PL/SQL】
number type:【Oracle】NUMBER 数値型の特徴
NUMBER(6,2):6 digits in total, 2 after decimal point
NUMBER(3,-2):two zero before decimal point, maximum three digits before decimal point
change from character to number:
SELECT TO_NUMBER(‘10.5’) FROM DUAL;
round:
SELECT ROUND(10.5) FROM DUAL;
SELECT ROUND(10.437,2) FROM DUAL;
SELECT ROUND(151.437,-2) FROM DUAL;
The above is to round the decile number.OracleのROUND 数値を四捨五入する【SQL】
truncate the unrequired:OracleのTRUNC 数値を切り捨てる【SQL】
SELECT TRUNC(10.5) FROM DUAL;
SELECT TRUNC(10.437,2) FROM DUAL;
SELECT TRUNC(151.437,-2) FROM DUAL;
ABS:
SELECT ABS(-10) FROM DUAL;
MOD:
SELECT MOD(9,2) FROM DUAL;
POWER:
SELECT POWER(5,3) FROM DUAL;
STDDEV:
SELECT STDDEV(COLUMNNAME) FROM TABLENAME;
data type:OracleのVARCHAR・CHAR 文字データ型の特徴
VARCHAR2(10)
10 can be saved less than 10, if put into a smaller strings, the most common one
NVARCHAR2 is for unicode
CHAR(10) is saved as 10, even if it is less than 10
NCHAR is for unicode
RAW is for Binary data, length is extendable OracleのBLOB・BFILE バイナリデータ型の特徴
LONG RAW is for pictures, audios, etc, better to use BLOB instead
BFILE saves the information of the path to a file
CLOB is for text data, NCLOB is for Unicode especially OracleのCLOB・NCLOB テキストデータ型の特徴
string to date:
SELECT TO_DATE(‘20171201‘,’YYYY/MM/DD’) FROM DUAL;
SELECT TO_DATE(‘20171201230010‘,’YYYY/MM/DD HH24:MI:SS’) FROM DUAL;
to concat:
1.SELECT CONCAT(‘A‘,’B‘) FROM DUAL;
2.SELECT ‘A‘ || ‘B‘ FROM DUAL;
to replace:SATOU->KATOU
SELECT REPLACE(‘SATOU‘,’S‘,’K‘) FROM DUAL;
SATOU->SATO
SELECT REPLACE('SATOU','U') FROM DUAL;
SELECT REPLACE('ABC','BC','XY') FROM DUAL; ABC->AXY
to translate: ABC->XBY Oracle TRANSLATE 文字列を1文字ずつ置換する
SELECT TRANSLATE('ABC','AC','XY') FROM DUAL;
to see the dedault profile limit state:
select resource_name, limit from dba_profiles where profile='DEFAULT';
change the default profile and let the password have no lock time:https://cloud.tencent.com/developer/article/1529149
alter profile default limit
password_life_time unlimited
password_lock_time unlimited
password_grace_time unlimited
failed_login_attempts unlimited#session should be(=>300)
show parameter session;
#process should be(=>300)
show parameter processes;
#cursor should be(=>500)
show parameter open_cursor;
#change cursor, will be effective after restart
alter system set open_cursors=500 scope=spfile;
list all the schemas in database:
select username from dba_users;
check the DATA_PUMP_DIR:
desc dba_directories
select directory_name, directory_path from dba_directories;
show the current logined user:
show user
to check the NLS_CHARACTERSET code while installing oracl database:
select * from nls_database_parameters;
to check the installment configuration:
app\oracle\product\19.3.0\dbhome_1\cfgtoollogs\oui
pfile locates in: app\oracle\admin\orcl\pfile
connect by jdbc:
jdbc:oracle:thin:@hostname:1521:servicename
or
oracle:thin:@hostname:1521/servicename
check the service of oracle available, and description for alias in command prompt:
tnsping servicename
check the listener’s state in command prompt:
lsnrctl status
start or stop the listener:
lsnrctl start
lsnrctl stop
show all the table under a certain schema:
select table_name from USER_TABLES;
to open PDB(pluggable database, default pdb’s instance name is orclpdb):
sqlplus / as sysdba
check the sate:
select con_id,name,open_mode from v$pdbs;
and then:
alter pluggable database ORCLPDB open;
alter session set container=ORCLPDB;
commit;
restart the database
check the connected container:
SQL> show con_name;
check the pdb name:
SQL> select pdb_name from cdb_pdbs;
connect to the pdb:
SQL> alter session set container = ORCLPDB;
to keep it starts with the windows server startup, you need to save the PDB’s state:
alter pluggable database orclpdb save state;
Check tablespace in db:
select tablespace_name from dba_tablespaces;
Check username in db:
select username from dba_users;
remap tablespace, eg:
impdp system/PW@instance_name directory=data_pump_dir dumpfile=xxx.dmp remap_tablespace=(old_tablespace_name1:new_tablespace_name1, old_tablespace_name2:new_tablespace_name2) remap_schema=old_schema:new_schema logfile=xxx.log
to output csv by sql script and in a good format, under sqlplus:
set echo off
set termout off
set heading on
set linesize 32767
set trimspool on
set colsep ‘,’
set underline off
set feedback off
set pagesize 0 embedded on
set pause off
set mark csv ON
spool “result.txt”
@ “xxx.sql”
spool off
check the tablespace’s datafile size in physical hard disk:
select tablespace_name,file_name,bytes/1024/1024/1024"GB" from dba_data_files;
create directory for importing and exporting dumpfile:
create directory xxx as 'C:\foldername';
show which instance it is using when the oracle is in cluster:
select * from v$instance;
show error of a certain view:
show errors view schemaname.viewname;
compile a view to check error:
alter view schemaname.viewname compile;
check which segment the schema has:
select segment_name, tablespace_name from dba_segments where owner=‘schemaname‘;
check which object the schema has, including view:
select object_name,object_type from dba_objects where owner=’schemaname‘;
check which table the schema has: oracleの表領域が少なくなった場合の調査用SQL
select table_name,tablespace_name from all_tables where owner=’ schemaname ‘;
find data file folder in hard disk:
select * from dba_data_files ;
check the os where oracle installed on:
select platform_name from v$database;
get the hostname where oracle installed on:
select * from v$instance;
To clear the SQL window:
cl scr;
to solve error ORA-01033, if accidentally delete the tablespace file on the hard disk:
check the last command in the buffer: ed
check data file volume usage:
SELECT
A.TABLESPACE_NAME "Table Space"
, ROUND(SUM(BYTES) / 1024 / 1024, 1) "File Volume(MB)"
, ROUND(SUM(BYTES - SUM_BYTES) / 1024 / 1024, 1) "Used Volume(MB)"
, ROUND(SUM(SUM_BYTES) / 1024 / 1024, 1) "Free Volume(MB)"
, ROUND((SUM(BYTES - SUM_BYTES) / 1024) / (SUM(BYTES) / 1024) * 100, 1)
"Used(%)"
FROM
DBA_DATA_FILES A
LEFT JOIN (
SELECT
TABLESPACE_NAME
, FILE_ID
, NVL(SUM(BYTES), 0) SUM_BYTES
FROM
DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
, FILE_ID
) B
ON A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.FILE_ID = B.FILE_ID
GROUP BY
A.TABLESPACE_NAME
ORDER BY
1;
check all the tablespace:
SELECT TABLESPACE_NAME, STATUS, CONTENTS From user_tablespaces;
create tablespace:
CREATE TABLESPACE [yourTablespaceName] DATAFILE '[pathTo\xxx.dbf]' SIZE 300M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
create temporary tablespace:
CREATE TEMPORARY TABLESPACE [yourTemporaryTableSpaceName] TEMPFILE '[pathTo\xxx.dbf]' SIZE 300M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
create schema and assign it to certain tablespaces:
CREATE USER [yourUserName] IDENTIFIED BY [yourPassword] DEFAULT TABLESPACE [yourTableSpaceName] TEMPORARY TABLESPACE [yourTemporaryTableSpaceName];
delete the tablespace within the contents inside. (If there is data inside, including contents is a must):
DROP TABLESPACE [yourTableSpaceName] INCLUDING CONTENTS;
grant quota on tablespace to a user: (A quota only really makes sense for permanent extents)
ALTER USER [yourSchemaName] QUOTA UNLIMITED ON [yourTableSpaceName];
create session system privilege:
GRANT CREATE SESSION TO [yourSchemaName];
KAGAYA's Blog 