SqlPlus quick reference
- SqlPlus Profile: glogin.sql
- SqlPlus Connect
- List objects
- Truncate all tables
- SqlPlus pause on each page
- Sqlplus .sql Import
- Language Settings, Encoding
- Database operations
glogin.sql or login.sql details:
-- pagesize repeats table headers after N rows
set pagesize 500
-- Increase output length
set linesize 150
-- Show output from stored procedures
set serveroutput ON
-- Do not wrap rows
set wrap OFF
sqlplus user/pass@host/SID_or_ServiceName
sqlplus user/pass@SID (localhost is default)
Local admin access, user must be part of ORA_DBA group on Windows; dba on Unix
sqlplus / as sysdba
select * from tab;
select * from user_tables;
SELECT distinct name FROM User_Source WHERE TYPE = 'FUNCTION';
SELECT * FROM User_Procedures;
SELECT DISTINCT name FROM User_Source WHERE TYPE = 'PROCEDURE';
Describe <table_name>
SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE
TABLE_NAME='table_name';
BEGIN
FOR c IN (SELECT table_name, constraint_name FROM user_constraints
WHERE constraint_type = 'R')
LOOP
EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' disable
constraint ' || c.constraint_name);
END LOOP;
FOR c IN (SELECT table_name FROM user_tables)
LOOP
EXECUTE IMMEDIATE ('truncate table ' || c.table_name);
END LOOP;
FOR c IN (SELECT table_name, constraint_name FROM user_constraints
WHERE constraint_type = 'R')
LOOP
EXECUTE IMMEDIATE ('alter table ' || c.table_name || ' enable
constraint ' || c.constraint_name);
END LOOP;
END;
/
set pause on
- UTF8 sql files must be without BOM
- Stored procedures and blocks must end with
/ - SET ECHO ON to display executed sql for logs
- Script parameters
- This way it will exit asap after script finishes:
"@<path_to_sql_file> arg1 arg2" | sqlplus <conn_string>
$ENV:NLS_LANG='.UTF8'
NLS lang FAQ: http://goo.gl/58oknU
Provera:
select Value from nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER DATABASE OPEN;
alter system set nls_length_semantics='char' scope=both;
SHUTDOWN IMMEDIATE;
STARTUP;Provera:
select value from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS'
select Value from nls_database_parameters WHERE parameter = 'NLS_LENGTH_SEMANTICS';
SQL> show parameter NLS_LENGTH_SEMANTICS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string BYTE
SQL> ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=BOTH;
System altered.
SQL> show parameter NLS_LENGTH_SEMANTICS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_length_semantics string CHAR
By default, the character data types CHAR and VARCHAR2 are specified in bytes, not characters. Hence, the specification CHAR(20) in a table definition allows 20 bytes for storing character data. This works well if the database character set uses a single-byte character encoding scheme because the number of characters is the same as the number of bytes. (Naša baza je multi-byte)
Note that if the NLS_LENGTH_SEMANTICS environment variable is not set on the client, then the client session defaults to the value for NLS_LENGTH_SEMANTICS on the database server. This enables all client sessions on the network to have the same NLS_LENGTH_SEMANTICS behavior. Setting the environment variable on an individual client enables the server initialization parameter to be overridden for that client.
The session-level value of NLS_LENGTH_SEMANTICS specifies the default length semantics to use for VARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. This default may be overridden by the explicit length semantics qualifiers BYTE and CHAR in column, attribute, and variable definitions.
The instance-level value of NLS_LENGTH_SEMANTICS provides a default for the session-level value if NLS_LENGTH_SEMANTICS it is not set explicitly by the database client through the NLS_LENGTH_SEMANTICS client environment variable (does not apply to JDBC Thin clients), or the ALTER SESSION SET NLS_LENGTH_SEMANTICS statement.
Why do you want to change it? NLS_LENGTH_SEMANTICS is only valid if you define a column without specifying the semantics (e.g. create table xxx (values varchar2(50)). It doesn't have an impact on any pre created tables, so it doesn't help to change from BYTE to CHAR! On the other hand: you can set it session specific (ALTER SESSION) or database wide (ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR).
BUT AGAIN (due to the Oracle documentation): "Caution: Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file."
The ; ends a SQL statement, whereas the / executes whatever is in the current "buffer". So when you use a ; and a / the statement is actually executed twice.
The / is mainly required in order to run statements that have embedded ; like a CREATE PROCEDURE statement
From my understanding, all the SQL statement don't need forward slash as they will run automatically at the end of semicolons, including DDL, DML, DCL and TCL statements.
For other PL/SQL blocks, including Procedures, Functions, Packages and Triggers, because they are multiple line programs, Oracle need a way to know when to run the block, so we have to write a forward slash at the end of each block to let Oracle run it.
lsnrctl start | status | stop
-- Activate
alter session set container=pdbtest1;
-- Open
alter pluggable database PDBTEST1 open read write;
show con_name;
show con_id;
show pdbs;
select * from v$pdbs;
alter session set container=PDB1;
-- Users
select * from all_users;
select * from dba_users;
alter user <name> identified by <pass>
-- Password expiration
select * from dba_profiles;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
-- Enterprise Manager port
select dbms_xdb_config.gethttpsport() from dual;
### Recover
RECOVER DATABASE UNTIL CANCEL;