Oracle

1
2
3
4
5

> drop tablespace TABLESPACE including contents and datafiles;
> create tablespace TABLESPACE datafile 'TABLESPACE.dbf' size 500M autoextend on next 5M maxsize unlimited;
> drop user USER cascade;
> create user USER identified by PASSWORD default tablespace TABLESPACE;
> grant connect,resource to USER;

1
2
3
4

> conn system/123456 as sysdba
> alter session set "_ORACLE_SCRIPT"=true;
> create user guest identified by 123456;
> grant connect,resource,dba,select any table to guest;

1
2
3
4
5
6
7

> SELECT table_name FROM user_tables;
> SHOW TABLES;
> SHOW VIEWS;
> describe TABLE-NAME;
> select * from nls_instance_parameters;
> select * from nls_instance_parameters;
> select * from nls_session_parameters;

1

> select userenv(‘language’) from dual;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

> connect sys as sysdba
> shutdown immediate
> startup mount
> alter system enable restricted session;
> alter system set JOB_QUEUE_PROCESSES=0;
> alter system set AQ_TM_PROCESSES=0;
> alter database open;
> alter database character set internal_use AL32UTF8;
> shutdown immediate
> startup

1
2

NLS_CHARACTERSET=AL32UTF8
NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8

1
2
3
4
5

exp USER/PASSWORD@HOST/ORACLE file=backup.dmp full=y
imp USER/PASSWORD@HOST/ORACLE file=backup.dmp full=y

expdp full=Y dumpfile=$VERSION.dmp logfile=$VERSION.log
impdp dumpfile=NAME.dmp logfile=VERSION.log schemas=SCHEMA REMAP_SCHEMA=OLD_USER:NEW_USER

1

select 'drop table ', table_name, 'cascade constraints;' from user_tables;

1

show con_name;

1
2

docker pull oracleinanutshell/oracle-xe-11g
docker run -d -p 49161:1521 -e ORACLE_ALLOW_REMOTE=true -e ORACLE_DISABLE_ASYNCH_IO=true oracleinanutshell/oracle-xe-11g

1
2
3
4
5

hostname: localhost
port: 49161
sid: xe
username: system
password: oracle

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

yum install compat-libstdc++-33
yum localinstall oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
yum localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm

/etc/init.d/oracledb_ORCLCDB-19c configure
sed -i 's/:N/:Y/g' /etc/oratab

cat > ~oracle/.profile <<- EOF
ORACLE_SID=ORCLCDB
ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_HOME PATH

export LC_ALL="en_US.utf8"
export LANG="en_US.utf8"
export NLS_LANG="american_america.zhs16gbk"
EOF
chown oracle:oinstall ~oracle/.profile
chmod 600 ~oracle/.profile

1
2
3
4
5
6
7
8

> alter pluggable database all close;
> create tablespace birman datafile '/home/oracle/birman.dbf' size 500M autoextend on next 5M maxsize unlimited;
> create user c##birman identified by 123456 default tablespace birman;
#根据需要进行授权
> grant connect, resource to c##birman container=all;
> grant create session to c##birman container=all;
> grant dba to c##birman container=all;
> alter pluggable database all open;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

$ sqlplus / as sysdba
# 查看用户的proifle， 一般是default
> SELECT username,PROFILE FROM dba_users;
# 查看指定概要文件（如default）的密码有效期设置
> SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
# 密码有效期
> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
# 尝试登录失败次数
> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
# 修改密码
> ALTER USER 用户名 identified BY 原来的密码