Oracle Database creation and date format usage

Oracle Database creation and date format usage install oracle Instance name orcl, system password system * database creation steps: 1. Install oracle, enter the Instance name orcl, and end after creating the instance database. 2. in the system running, Enter cmd and press enter to open the command window, enter sqlplus, enter the user name and press enter to log on, and enter the command startD: ksxt. SQL (D: ksxt. SQL is the path of this file) & nbsp; press enter to execute

Oracle Database creation and date format usage

Install oracle Instance name orcl, system password system
/* Database creation steps:
1. Install oracle, enter the Instance name orcl, and finish creating the instance database.
2. in the system running, Enter cmd and press enter to open the command window, enter sqlplus, enter the user name and press enter to log on, and enter the command start D: ksxt. SQL (D: ksxt. SQL is the path of this file)
Press enter to execute. Automatic termination
The command window is as follows:
C: UsersAdministrator> sqlplus
Enter the User Name:
SQL> start D: ksxt. SQL
3. Modify ksxt2448 in Web. Config to ksxtpwd.
4. The deployment is successful and the program is tested.
*/
-- Logon: Log On with system before creating a user
Conn;
-- Create temporary tablespace
Create temporary tablespace ksxt_temp tempfile 'd: oracleproduct10.2.0oradatatestserverksxt _ temp01.dbf'
Size 32 m
Autoextend on
Next 32 m maxsize 2048 m
Extent management local;

-- Create a tablespace
Create tablespace KSXT
Logging-there are two options: NOLOGGING and LOGGING. When creating a tablespace, no redo logs are created.
Datafile 'd: oracleproduct10.2.0oradatatestserverKSXT01. dbf 'size 32 m -- DATAFILE is used to specify the location and size of the data file. if multiple files exist, they can be separated by commas (,), but the size of each file must be specified. it must be an absolute address and cannot be a relative address.
Autoextend on
Next 32 m maxsize 2048 m
Extent management local -- extent management local storage area management method, local management: Manages disks in binary mode, with high efficiency, and can use disks to the maximum extent. at the same time, the system can automatically track and record the situation of near-idle space to avoid merging idle areas.
Uniform segment space management -- Disk Extension MANAGEMENT Method: segment space management: use this option to determine the time zone size automatically by the system. Since Oracle can determine the optimal size of each region, the partition size is variable. Uniform segment space management: Specifies the partition size. You can also use the default value (1 MB ).
AUTO-segment space management method: AUTO: can only be used in local tablespace. when you use LOCAL to manage tablespaces, after the free space in the data block is increased or decreased, its new status will be reflected in the in-place diagram. Bitmap makes Oracle's management of free space more automated and provides better performance for management of free space. However, it cannot be automatically managed for tables with LOB fields. MANUAL: It is currently not used, mainly for backward compatibility.
;
-- Create a user and specify a tablespace
Create user ksxt/* username */identified by ksxt2448/* password */
Default tablespace KSXT
Temporary tablespace ksxt_temp;

-- Grant permissions to users
Grant connect, resource, dba to ksxt; -- connect connects only to resource development in three system permission groups. The application resource dba Administrator
-- Log on to the user later. Any database objects created belong to the ksxt_temp and ksxt tablespaces. Therefore, you do not need to specify a tablespace for each object created.
-- Use the created user to log on
Conn;
-- Import data
-- Imp file = c:/ksxt. dmp full = y ignore = y; -- import the dmp file
Start d: ksxt. SQL; -- SQL file statement Import

-- To CREATE a SEQUENCE, you must first have the create sequence or create any sequence permission,
-- Example:
/*
Create sequence emp_sequence -- sequence name
Increment by 1 -- add several
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
Cach 10; -- cache size
*/
Create sequence KSTMB_SQS
Minvalue 1
Max value 999999999999999999999999999
Start with 2
Increment by 1
Cache 20;

Create sequence STORYDATA_SQS
Minvalue 1
Max value 999999999999999999999999999
Start with 2
Increment by 1
Cache 20
Order;
-- Create an index and check whether the exported SQL file contains the index creation statement. Do not create the index again.
/*
Create index INDEX_KSTMB_1 on KSTMB (SJBM, STLX, STBM)
Tablespace KSXT
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64 K
Minextents 1
Maxextents unlimited
);

Create index STORYDATA_INDEX_1 on STORYDATA (STBM, TIMEID)
Tablespace KSXT
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64 K
Minextents 1
Maxextents unlimited
);
*/

Actually

Oracle Date Format insert into Ecp_Chat_Message (CHATID, CONTENT, IMGURL, SENDUSERID, SENDTIME, RECEIVEUSERID, STATE, ISREAD)
Values (1, 'asdfdf ', 'aaa', 'kehu4', to_date ('2017-12-03 12:55:45. 333333 ', 'yyyy-mm-dd hh24: mi: ss. ff '), 'serviceid', 1, 0)

The following error is reported:

The Date Format Image ends before the entire input string is converted.

You can insert data to the second in this way,

Insert into Ecp_Chat_Message (CHATID, CONTENT, IMGURL, SENDUSERID, SENDTIME, RECEIVEUSERID, STATE, ISREAD)
Values (1, 'asdfdf ', 'aaa', 'kehu4', to_date ('2017-12-03 12:55:45', 'yyyy-mm-dd hh24: mi: ss '), 'serviceid', 1, 0)

Use in milliseconds

Insert into Ecp_Chat_Message (CHATID, CONTENT, IMGURL, SENDUSERID, SENDTIME, RECEIVEUSERID, STATE, ISREAD)
Values (1, 'asdfdf ', 'aaa', 'kehu4', to_timestamp ('2017-12-03 12:55:45. 333333 ', 'yyyy-mm-dd hh24: mi: ss. ff '), 'serviceid', 1, 0)

Note that the database field must be changed to the timestamp type.